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

Comments