AX 2012 Performance - Configure SQL Server and Storage

This Article provides information about how to configure database and windows server for Microsoft Dynamics AX. To achieve optimal Microsoft Dynamics AX performance, we must correctly configure the database infrastructure. 

You must have knowledge in following areas:

·         Windows Server administration.

·         SQL Server administration. Specific areas of knowledge include advanced configuration options, memory management, performance management, and troubleshooting.

·         Microsoft Dynamics AX system administration.

Recommendation on Tune DB operations:

#	Recommendation	Reference
1	Enabling trace flags 4136 ,1117 and 4199 on SQL server.	T-4136 : Disable parameter sniffing T-4199 : Activate query optimizer fixes T-1117 : DB files growth
2	Disabling index hints from AX configuration and thereby allowing SQL to decide the best way to process the query.	Best Practice
3	Making sure that entire table cache is not enabled for tables which get frequently updated.	Best Practice
4	SQL Server service account is not granted SE_MANAGE_VOLUME_NAME right	Best Practice - Space created by Delete to used
5	Reducing the MAXDOP setting to 1 and test it.	Best Practice
6	Change Minimum SQL to 0 to 60 GB	Best Practice
7	Making sure no of tempDB files = No of logical processors. It must be 24 data files	MS Best practice : ensuring Temp files used by DB for temp table operations

Recommendation on I/O Storage and Physical Structure tuning:

#	Recommendation	Reference
1	DB File Growth is having auto-growth setting to trend of increase in size and It recommended to set to 10%	Best Practice
2	Making sure we have primary key/clustered index on each table in AOT for enabling record caching.	Best practice : Ensuring table scans are avoided

IOPS for AX 2012:

Benchmark reports of Microsoft, which the performance study done in AX 2012 with SQL 2008 R2. Based on the report I have attached the snapshot of IOPS and CPU utilization.
The below study is done for 5000 users. I also attached the detailed report which has  transaction details, hardware details and system configuration.

You can also see the same report from the below link,

http://technet.microsoft.com/en-us/library/hh536205.aspx

Recommendation on Platform related Tuning:

#	Recommendation	Reference
1	Disable 'Power Saving' setting on DB server and change to 'High Performance’. Taking reference from MS on performance improvement , power saving will reduce sql performance by 50%	Best Practice MS Support http://support.microsoft.com/kb/2207548
2	Exemption of AOS processes from real time antivirus scanning  1) AX32.exe 2) AX32serv.exe All the application folders	Best Practice
3	Disable Scalable Networking Pack on Application Servers
4	Install MS latest hot fixes on Applications servers  to update Processor Driver.
5	Disable Debug Settings	Best Practice
6	Disabling Error Reporting in AOS servers	Best Practice

Minimal SQL Server infrastructure

The configuration of Windows Server and SQL Server greatly affects the performance of the Microsoft Dynamics AX database. This below recommendations for the configuration of Windows Server and SQL Server.

The configuration recommendations are based on the following assumptions:

·         You are using a dedicated server that runs SQL Server.

·         You are using a single instance of SQL Server that is dedicated to running the Microsoft Dynamics AX production database.

·         MS recommend that you store your test and development databases on a separate server from the production database.

Configuring the Microsoft Dynamics AX database

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

·         Set COMPATIBILITY_LEVEL to 100.

·         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.

·         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. 

Configuring the server that runs SQL Server

For SQL Server, we recommend the following configuration settings for the SQL Server service.

·         Run the SQL Server service under an Active Directory domain account that has the minimum necessary privileges.

·         Confirm that the account for the SQL Server service has been granted the Lock pages in memory privilege.

·         Configure the account for the SQL Server service for instant file initialization. Instant file initialization is only available if the account for the SQL Server service, MSSQLSERVER, has been granted the SE_MANAGE_VOLUME_NAME right. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.

·         Enable the TCP/IP network protocol. Depending on the edition of SQL Server that you use, this protocol may be automatically installed during installation. For instructions,

Disable hyperthreading. This step must be performed in the BIOS settings of the server. For instructions, see the hardware documentation for your server.

Configuring the instance of SQL Server

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;

RECONFIGURE; 

GO

EXEC sp_configure 'max degree of parallelism', 1;

RECONFIGURE;

GO

EXEC sp_configure;

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. 



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.



Allocating storage for tempdb
MS 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. 

·        Based on performance testing of the OLTP workload for Microsoft Dynamics AX, we recommend that you maintain one tempdb data file per processor. 

·      Isolate tempdb on dedicated storage, if you can. MS 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.

·        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. 

·      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 theyare 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.

Plan database storage

1.     Characterize the input/output (I/O) load of the application. The I/O characteristics depend on your business requirements, and on the Microsoft Dynamics AX modules and components that we deploy. To determine your I/O characteristics, answer the following questions:

·         What is the read ratio versus write ratio of the application?

·         What is the typical I/O volume, or I/O per second (IOPs)?

·         How much of the I/O is sequential, and how much is random?

2.     Determine the availability and performance requirements for the database system.

3.     Determine the hardware that is required to support

4.     Configure SQL Server properly.

5.   Track the performance as the workload changes

Configuring physical storage

·         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. Microsoft 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.

Reference from Technet  articles and personal resources