Category Archives: SQL

Configuring the Microsoft Dynamics AX business database for better performance

Microsoft recommend the following settings for the Microsoft Dynamics AX business database. You can use SQL Server Management Studio or the appropriate ALTER DATABASE statement to configure these settings.

  • Set COMPATIBILITY_LEVEL to 110 for SQL Server 2012, or to 100 for SQL Server 2008 or SQL Server 2008 R2.

  • Set READ_COMMITTED_SNAPSHOT to on. Performance testing has shown that Microsoft Dynamics AX performs better when the READ_COMMITTED_SNAPSHOT isolation option is set to on. You must use an ALTER DATABASE statement to set this option. This option cannot be set by using SQL Server Management Studio.

    Run the following query, where <database name> is the name of the Microsoft Dynamics AX database. There can be no other active connections in the database when you run this query.

    ALTER DATABASE <database name>

    Query the sys.databases catalog view, and verify that the Microsoft Dynamics AX database contains a value of 1 in the is_read_committed_snapshot_on column. For more information, see the following Web pages:

  • Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to on. Set AUTO_UPDATE_STATISTICS_ASYNC to off. Performance testing has shown that Microsoft Dynamics AX performs better when the options have these settings.

  • Make sure that the AUTO_SHRINK option is set to off. When database files are automatically shrunk, performance of the database degrades. We recommend that the database administrator manually shrink the database files on a predefined schedule. For more information, see Turn AUTO_SHRINK OFF! on the SQL Server Storage Engine Team’s blog.


All Microsoft Dynamics AX databases must use the same SQL collation. These databases include the business database, model store database, Microsoft SQL Server Reporting Services database, and Microsoft SQL Server Analysis Services database.

Configuring the instance of SQL Server for Dynamics AX

Improve SQL performance lead us to optimize AX performance, this post is one of posts to achieve that

1- Configuring max degree of parallelism

The max degree of parallelism option is a setting that affects the entire instance of SQL Server. Microsoft Dynamics AX workloads generally perform better when intra-query parallelism is disabled. However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance. Use the following settings when the system performs maintenance activities or an upgrade:

· Before an upgrade to a new release of Microsoft Dynamics AX, or before a large number of maintenance or batch activities, set max degree of parallelism to the smallest of the following values:

· 8

· The number of physical processor cores

· The number of physical processor cores per non-uniform memory access (NUMA) node

· When the Microsoft Dynamics AX database is used in a production environment, set max degree of parallelism to 1.

Use the following statements to set the value of max degree of parallelism.

Examine the output from the second sp_configure ‘max degree of parallelism’ statement, and confirm that the value has been changed. In the following query, the first sp_configure ‘max degree of parallelism’ statement sets the value of max degree of parallelism to 1. The second sp_configure ‘max degree of parallelism’ statement returns a value of 1.

EXEC sp_configure ‘show advanced options’, 1;



EXEC sp_configure ‘max degree of parallelism’, 1;



EXEC sp_configure;

For more information, see max degree of parallelism Option. For general guidelines, see Knowledge base article 329204, General guidelines to use to configure the MAXDOP option. For tips from the SQL Server team, visit the SQL Server Relational Engine team’s blog, SQL Server Engine Tips.

2- Configuring max server memory

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory is allocated to SQL Server. However, the max server memory option can be useful in some environments. Make sure that sufficient memory is available for the operation of Windows Server. For more information, see Configure SQL Server and storage settings, later in this topic.

If you find that the dynamic allocation of memory adversely affects the operation of Windows Server, adjust the value of max server memory based on the available random access memory (RAM). For more information, see Effects of min and max server memory.

3- Monitoring available memory

Make sure that sufficient memory is available for the operation of Windows Server. For example, make sure that you run a dedicated instance of SQL Server on a server that has at least 4 gigabytes (GB) of memory. If the available memory for the server drops below 500 megabytes (MB) for extended periods, the performance of the server may degrade.

Use the Memory: Available Mbytes performance counter for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods. If the available memory drops below 500 MB frequently or for extended periods, we recommend that you reduce the max server memory setting for SQL Server or increase the physical memory of the server.

Detailed guidance about memory management is beyond the scope of this topic. For more information about how to monitor memory and troubleshoot performance issues, see the Windows Server and SQL Server documentation.

4- Allocating storage for tempdb

We recommend that you determine the total size of the data files and transaction log files that are required for the tempdb database, and that you set a specific value. Do not use automatic growth, or autogrow, setting for space management. Instead, use autogrow as a safety mechanism, so that tempdb can grow if tempdb files use the space that was originally allocated to them. Follow this process to determine the number and placement of data files.

· Determine the number of processors that are available to SQL Server. Unless you are using an affinity mask, this number is same as the total number of processors that you see on the Performance tab of Windows Task Manager. When hyperthreading is not enabled, each processor corresponds to a processor core. Affinity masks and processor cores are beyond the scope of this topic. For more information, see the Windows Server and SQL Server documentation.

· Based on performance testing of the OLTP workload for Microsoft Dynamics AX, we recommend that you maintain one tempdb data file per processor. For more information, see the performance benchmark reports on PartnerSource or CustomerSource.

· Isolate tempdb on dedicated storage, if you can. We recommend that you move the primary data file and log file for tempdb to high-speed storage, if high-speed storage is available. The Microsoft Dynamics AX database runs in read committed snapshot isolation (RCSI) mode. In RCSI mode, row versions are stored in tempdb. By creating multiple files for tempdb data, even if these files reside on the same storage device, you can improve the performance of tempdb operations.

· Determine the size of the tempdb data files and log files. You must create one primary data file and one log file. Determine how many additional, secondary data files you require for the tempdb data. For best results, create data files of equal size. The total number of data files must equal the total number of processor cores. The aggregate size of the primary data file and all other data files must equal the total data size that you determined for the tempdb database.

For more information, see Optimizing tempdb performance.

· Resize the primary data file and log file for tempdb. Move the primary data file and log file to dedicated storage, if dedicated storage is available. The primary tempdb data file cannot be moved while the instance of SQL Server is running. To complete the move, you must use an ALTER DATABASE statement and restart the instance of SQL Server. For more information, see ALTER DATABASE.

Note: The data files and transaction log files for tempdb can reside on the same storage device.

· If space is available on the drive where tempdb files are allocated, do not configure the autogrow property for data files and log files as a percentage. Instead, configure the autogrow property as a specific number of megabytes. If you can, configure the data files and log files to grow by 100 to 500 MB, depending on the available space. Monitor the data files, and when they grow, adjust the original allocation to prevent automatic growth later. If the autogrow property is configured in megabytes instead of as a percentage, the allocation of space is more predictable, and the chance of extremely small or large growth increments is reduced.

· Monitor the tempdb data files and log files to make sure that they are all sized correctly, and that all data files are of equal size. Use SQL Server Management Studio or a transact-SQL query to view the database properties. Verify that all the data files are of equal size, and that they have the same size as the value that you originally provided. If one or more files have grown, adjust the initial size of all files.

Configuring physical storage for SQL server of Dynamics AX

This post provides general recommendations for physical storage. Determine the applicability of these recommendations to your environment. Some storage area network (SAN) vendors may have alternative recommendations that take precedence. Recommendations are listed in order of priority.

  • Many factors contribute to optimal I/O performance for a disk. By default, Windows Server 2008 aligns partitions. When you upgrade to Windows Server 2008, preexisting partitions are not automatically aligned and must be manually rebuilt to guarantee optimal performance. Therefore, until you rebuild the migrated partitions, alignment of disk partitions remains a relevant technology.

Check existing disks on the server, and be aware of the differences in the analysis of basic partitions and dynamic volumes. Rebuild the partitions, if you can, and appropriate and create all new partitions based on guidance from the SAN vendor. If the vendor does not provide recommendations, follow the best practices for SQL Server. See Disk Partition Alignment Best Practices for SQL Server.

The partition offset value must be a multiple of the stripe size. In other words, the expression, partition offset / stripe size, must resolve to an integer value.

  • Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10. We recommend RAID 10 for these files. Do not use RAID 5.
  • Store the data files for the Microsoft Dynamics AX database on separate physical stores from the transaction log files.
  • Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.
  • Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.

Re-index Dynamics AX database

Our database is incremented by 1 GB daily, and we apply periodic clean up and re-index from dynamics ax client but it is not affect size so much, so we tried using SQL re-index through SQL visual studio but we noticed that the re-index is increased not decreased plus data size of table is increased also although number of records are the same before and after the re-index, any body faced this problem before?

Hint: we are using SQL Cluster (2 Nodes)

Troubleshooting blocked SPIDS in AOS


In this blog post I will provide some details on how the AOS server manages it SQL connections and some tips on troubleshooting blocked connections in the database. All of my description is based on the SQL Server backend but similar techniques are applicable for Oracle also, only the DB tools are different. Let’s first start with quick primer on SQL connection management inside Ax.

When connecting to SQL Server, Dynamics uses ODBC APIs to connect to the database. There is a cost in establishing the connection and logging in the user , hence the AOS uses a connection caching mechanism that allows recycling of connections when not in use. Internally, the AOS keeps track of 3 types of connections which are:

· Regular application connection – All application code use this connection.

· RECID connection – There is a dedicated connection to the SystemSequences table. This is used by the RECID allocator inside the AOS.

· Read Only/SysLastValue connection – This is a shared connection that is used for SysLastValue and read only queries.

Note, that the AOS internally distinguishes between these connection types but externally when looking in the database or in the application it is not possible to differentiate between them easily. In fact if a particular connection is cached and then reused again it might end up being reused as a different type of connection than its original type although there are some restrictions on the types when reusing the connections

The connection cache is implemented as a FIFO queue where the connection is stored while it is not being used. By not being in use I mean that there is no active SQL statement from the AOS to the database backend using that connection. The AOS also keeps track of the state of the connections before putting them in the cache for reuse. If the connection is bad due to connectivity errors to the database or any other errors that would cause errors in the future, then that connection will not be reused and it will deleted which will close the connection to the database.

Now let’s see how to look at some of this information from outside the AOS. There are a variety of ways to look at the active connections in a database server. For our discussion we will consider two alternatives, one using the tools provided by SQL Server and the other from within Ax itself. Let’s first look at the option or using SQL Server. My favorite tool for a quick overview of connections is the “Activity Monitor” tool that is available in the SQL Server Management Studio. This tool can be found under the Management folder in the object explorer. There are lots of columns for each active connection but the Process ID (SPID) is the one of interest to us. When you first start an AOS server you will see 2 SPIDS which have “Microsoft Dynamics Ax” under the Application column. These are the connections which are currently opened by the AOS and they could be either active or an inactive connection in the cache.

SQL Server 2005

Dynamics Ax sessions in SSMS

SQL Server 2008 R2


From inside Ax, you can see some of the above information using the “Online Users Form”. The Client Sessions tab has a column called SPIDs that shows the connections that are currently active on a particular session. I should make you aware that you don’t get to see the whole picture when using this form. To start with you only see the active connections that are assigned to a session. This explains why sometimes you will not see any value in the SPIDs columns for some of the users. Basically it means that the user does not have any active connection to the database at that instant of time. Another limitation is that the SPID will be populated only if the client where you are viewing the form is connected to the same AOS as the other session. So if you have multiple AOS’ in a cluster you will not see all the active SPIDs in the system by opening a single instance of the online users form. You will have to open the form in separate clients each connected to a different AOS.

Now let’s look at how we can use some of the above information to troubleshoot scenarios where sessions are blocked in the database and how to get rectify the situation. You can again start with the Activity Monitor to look lookup the SPID of the blocked session. The activity monitor also gives you the SPID that it blocking any other SPID and also the DB resource that is causing the contention. You can then use the online users form to determine the owner of the session in AX for the blocked and blocking SPIDs. The form provides an ability to terminate an existing session in Ax. But you have to be careful in terminating the correct session. If you try to terminate the blocked session you will see that the online users form changes the status to “Ending – Blocked”. This state indicates that the AOS tried to terminate the session but it was not successful since this session has some open resources and it cannot be safely terminated. One option to solve this situation is to terminate the session that is blocking the other session(s). If the termination is successful, the database connection is closed and this will free up the DB resources for the blocked session(s).

In the online users form when you try to terminate a session sometimes you might see the status change to “Ending – Waiting for AOS”. This happens when you terminate a session that is not in the same AOS as the client that sent the terminate request. In this case the request is placed to the other AOS and it monitors for terminated session in the background and will it terminate it eventually when it processes the request.

In addition to the above techniques there are alternate techniques to troubleshoot blocking in the SQL Server database. You can query the database provided Dynamics Management Views (DMVs) to get more details on the resources that are being consumed or blocked.

I hope you found the above information useful and interesting. Let us know if you would like more information in related areas on how the AOS works and options to manage it. We would also like to hear any suggestions for improvements in any of the areas covered in this topic.


Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!