Authors: Peter Scharlock, Mark Prazak
Reviewers:  Kevin Cox, Mike Ruthruff, Norberto Garcia, Sri Srinivasan

Introduction:
This article summarizes the top tips for maximizing the performance and scalability of the Microsoft Dynamics AX 2009 application when utilizing SQL Server 2008. These tips are the result of collaboration between the Dynamics AX Performance Team & the SQL Server Partner Advisory Team based on optimizing real customer workloads and extensive benchmarking efforts. The tips encompass recommended best practices, as well as exposing newly released or modified, Dynamics and SQL Server code.

1.     Maximize Cursor Performance 

      The Dynamics AX application uses the well-known SQL Native Client ODBC API for its interaction with the SQL Server 2008 database. The AX programming model calls API Server Cursors extensively for paging through data, and in most cases this provides great performance, scalability, and reliability. However, during performance testing we have uncovered numerous areas for optimizing cursor performance. In most cases, the AOS servers utilize the fastest and most optimal SQL Server cursor type: Fast Forward-Only with the Autofetch (FFO) option. See documentation here: http://msdn.microsoft.com/en-us/library/ms187502.aspx

As stated in the SQL 2008 Books Online topic listed above, Fast Forward-only cursors are never converted, however it is possible that under certain conditions a ‘static-type’ execution plan may be created nonetheless, which essentially disables the optimal behavior.  These conditions include; the resultset includes one or more ‘blobs’, and/or not having the appropriate indexes to optimally satisfy the cursor.

Maximize Auto-Fetch capability by paying attention to:

Table design considerations

Limit the use of AX 2009 specific ‘container’ and ‘memo’ data-types (which utilize the SQL Server ‘image’ and ‘text’ data-types). SQL Server will degrade to a less optimal cursor type/behavior if the query contains either of these data-types. Retrieving these data-types will create a much more expensive static query plan. If you do need to add / retrieve these data-types try to make sure that these queries retrieve a single row or small number of rows, this will limit the time that it takes to create the static dataset in tempdb.

Keep the row length small to maximize the number of rows that get returned by the Auto-Fetch cursor

Evaluate whether this CU is a requirement for your implementation. As per SQL Server sustained engineering policy, this change will be automatically rolled into the next available SQL Server Service Pack.

Currently, X++ SELECT statements issued under AX’s pessimistic locking will request a dynamic cursor and not a FFO cursor, so we recommend: optimistic locking for this reason in addition to the concurrency benefits it provides.

There is a pending design change in AX4, AX2009 and the future AX6 that will request FFO cursors even when pessimistic locking is used.  An announcement of this change will be made on the AX Performance blog: http://blogs.msdn.com/axperf when completed.

2.   Index Management

    Like most other SQL Server applications, Dynamics AX takes full advantage of indexes. However, there are a number of very important considerations when adding and/or modifying indexes on an AX database;

Indexes that maximize the benefit of clustering are those which are frequently used to return a range of rows.

The clustered index key should not include columns which are updated.

Consider the key length when choosing a clustered index.  A long clustered key can inflate the size of non-clustered indexes on the same table. This is because every non-clustered index ‘embeds’ the clustering key as the mechanism to find the path back to the actual data row.

Examples of such indexes are:

          RecId index on any table

          TransIdIdx on SalesLine table

We can use the following criteria for identifying such indexes:

          Are unique, due to AllowDuplicates::No or are designated as the primary key.

          Are generally composed of two key columns, one of them being DataAreaId.

The following query can be used to locate indexes meeting these criteria:

select OBJECT_NAME(id), name from

Fragmentation is not the only reason for rebuilding indexes; The DBA should also consider rebuilding indexes whenever large amounts of data have been removed from the AX database.   Doing so will reclaim space and make it available for reuse. 

Consider rebuilding indexes for tables affected by the following processes:

          The AX default cleanup processes are run.  These processes are module specific and can generally be located under Periodic->Cleanup.

          Intelligent Data Management Framework (IDMF) is used to purge or archive data.

Missing Indexes
SQL Server has the ability to identify missing indexes by way of a number Dynamic Management Objects. These objects can be monitored to detect cases where the Query optimizer believes that adding an index would help performance. These DMO’s and their use are documented here: http://msdn.microsoft.com/en-us/library/ms345524(v=SQL.100).aspx   

NOTE: whenever an index is added to the AX schema, it should be carefully tested by the DBA before being put into the production environment. 

3.   SQL Server statistics

      The SQL Server query optimizer is tasked with creating optimal query plans based on a defined set of heuristics. A major piece of these heuristics is the SQL Server statistics; they help the optimizer by supplying details about the data distribution, last time stats were updated, and so on.More about SQL Statistics found in the following Books-Online topic here: http://msdn.microsoft.com/en-us/library/ms190397(v=SQL.100).aspx For the SQL query optimizer to do its job effectively the statistics must be kept up-to-date; the following tips are recommended guidelines for AX.

There is a startup cost associated with the creation of new statistics due to the Auto-Create statistics setting.  This cost can result in high CPU on a new AX deployment due to the large number of composite indexes that exist in the AX database.  The additional CPU consumption will subside after a short time (a few hours) of a steady AX user workload.

You can preempt much of this statistics startup cost through the use of the system stored procedure sp_createstats.  You can use sp_createstats to initially create statistics on all columns participating in an index:

Auto-update async should be left at its default OFF setting.  It was previously advised to enable Auto-update async but this recommendation has now been retracted.   We have identified several processes, such as Update Inventory on Hand and General Ledger Post, which have the potential to degrade if Auto-update async is enabled.  Also, ongoing performance testing did not show a material benefit to any AX process if Auto-update async is enabled.

4.   Parameterized Query considerations

 
     Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement begins execution on an instance of Microsoft SQL Server, it is compiled into a query plan by the SQL Server Database Engine query optimizer. Then the plan is executed to produce a result-set. The compiled query plans are stored into a part of SQL Server memory that is called the plan cache. Reusing complied query plans offer significant performance benefits because they save resources by not having to compile every time the query is executed. One way to further enhance this benefit is by using Parameterized queries, that is; Queries are compiled with parameters rather than literal values. By doing this the query can be compiled for a single parameter value, and yet be reused for many other parameter values.

By default, Dynamics AX parameterizes all queries to take advantage of these performance enhancements but there are a number of conditions where parameterized queries may not be the best choice. For example, to be effective, parameterized queries work best when;

          The distribution of the data being retrieved is evenly distributed

          The query result-sets contain approximately the same number of rows

          The set of used parameter values would typically generate the same query plan

As you can see, there are numerous conditions where parameterized queries may not be the best choice for a specific AX implementation. These conditions are generally related to a phenomenon known as parameter sniffing. Details about parameter sniffing can be found in the following blog: http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx

Basically, when creating a query plan, the SQL Server optimizer uses the parameter values passed the ‘first time’ to create the cached execution plan for this query and this plan will be utilized for all subsequent calls to this query including parameter values that might have prompted a different query execution plan.  For example; let’s suppose we have a simple parameterized query that does the following: select * from table1 where country =  @Param1. Now imagine that the query is compiled and cached where the first invocation passes the value ‘USA’ for the parameter @Param1.  Now let’s suppose the second invocation uses the value ‘Norway’ as the parameter value.  Using the points above as a guide, what could be the result?

The distribution of the data being retrieved is evenly distributed.

          In this case, everything would work fine, however if either value was highly skewed, that is; many more of one value than the other, then we have typically seen that an optimal plan will be created for the first passed value, but this plan may not be the best choice for the ‘next value’. 

The query result-sets contain approximately the same number of rows.

          This is very similar to the description above

The set of used parameter values would typically generate the same query plan.

          Again, this is very similar to the description above

Because these are data conditions generally out of the control of the AX application, there are a number of ways to successful address these conditions:

          Use literals in join queries from forms and reports

          Use literals in complex joins from X++

Checking either of these settings will suppress parameterization of queries described in the respective setting label.  The default for both settings is OFF and it is not recommended to change either one.

In both cases where we specify literals to be used, we must exercise caution that doing so does not introduce the security threat of SQL injection; the literal values should not be derived from a user accessible field on a form.  We must also consider the cost of repeated compilation when forcing literals in place of parameterization.

NOTE: the XML representation of a query plan (captured using DMO’s or by saving an execution plan in XML): http://msdn.microsoft.com/en-us/library/ms190646.aspx

identifies the parameter values that were used to compile the query, as well as the current runtime parameter value. Comparing these values can be highly valuable in diagnosing parameter sniffing issues.

Use of plan guides to pass the following:

OPTION(RECOMPILE)

OPTION(OPTIMIZE FOR…)

OPTION(OPTIMIZE FOR UNKNOWN)

NOTE: using this method against the AX 2009 derived queries is complex because of the extensive use of API cursors. This method should be used in moderation.

 

Evaluate whether your AX 2009 implementation will benefit from this trace flag.

NOTE: this traceflag is to be used where parameter sniffing is problematic because of skewed data or other reasons pointed out in the following blog: http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx it should be used only under fully tested conditions that have proven that the traceflag is beneficial for your specific workload.

5.   Concurrency considerations

   The AX application supports many (hundreds or thousands of ) users accessing the database at the same time. When some of these users attempt to modify the same data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control. There are a number of important considerations to achieve optimal concurrency control when using the AX application:

NOTE: Lock escalation can be monitored by a trace event as described in the following article: http://msdn.microsoft.com/en-us/library/ms190723.aspx

Conclussions:
Utilizing standard SQL Server 2008 and AX 2009 best practices generally provide good performance. This article goes into much more depth; pointing out very specific functionality that will allow the AX 2009 DBA to maximize the performance, scalability, and reliability on their implementation on SQL Server 2008. Although this article is meant for current/future AX customers, much of the information presented below is applicable to other ISV applications as well.