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 

Tools for monitoring performance in AX 2012

 “Good performance requires good and continuous monitoring. If you find problematic processes early, you can optimize them without significantly affecting your end users. Remember that performance tuning is an iterative process. Often, you must complete more than one round of tuning to achieve the maximum improvement in performance.” - Microsoft Tech Net,  May 29, 2012

Microsoft recommend the following tools for monitoring the performance of Microsoft Dynamics AX.

1. Performance Monitor
Performance Monitor is the basic tool for obtaining an overview of performance. You can review the CPU, disk, and memory counters to find performance issues. After you have identified a time frame during which your system may have experienced performance problems, you can add more specific counters to understand the problem in more detail.

Performance Monitor is a simple yet powerful visualization tool for viewing performance data, both in real time and from log files. With it, you can examine performance data in a graph, histogram, or report.

Membership in the local Performance Log Users group, or equivalent, is the minimum required to complete this procedure.

To start Performance Monitor

1.     Click Start, click in the Start Search box, type perfmon, and press ENTER.
2.     In the navigation tree, expand Monitoring Tools, and then click Performance Monitor. You can also use Performance Monitor to view real-time performance data on a remote computer. Membership in the target computer's Performance Log Users group, or equivalent, is the minimum required to complete this procedure.

To connect to a remote computer with Performance Monitor

1.     Start Performance Monitor.
2.     In the navigation tree, right-click Reliability and Performance, and then click Connect to another computer.
3.     In the Select Computer dialog box, type the name of the computer you want to monitor, or click Browse to select it from a list.
4.     Click OK.

For more details, follow the below links:

o   Set up Performance Monitor counters

o   Collect AX 2012 event traces with Windows Performance Monitor

2. Microsoft Dynamics AX 2012 Trace Parser

Trace Parser ships with Microsoft Dynamics AX. Trace Parser consolidates information from multiple sources, such as remote procedure calls (RPCs) and Microsoft SQL Server, to provide an integrated view of application performance at run time.

o    Install the Trace Parser

o    Tracing Cockpit Form

o    Walk through major features of Microsoft Dynamics AX 2012 Trace Parser (Part 1)

o    Walk through major features of Microsoft Dynamics AX 2012 Trace Parser (Part 2)

3. Performance Analyzer for Microsoft Dynamics AX (DynamicsPerf)

DynamicsPerf can be used to collect information from Microsoft Dynamics AX and SQL Server. DynamicsPerf consists of a database and a collection of scripts that collect information from SQL Server and Microsoft Dynamics AX. Based on this information, you can find issues such as expensive queries and locking/blocking. When you have gained experience, you can also use this tool to find inefficient code or business processes.

4. System Center Operations Manager Monitoring Pack for Microsoft Dynamics AX 2012

The Monitoring Pack can be used to monitor your system. The Monitoring Pack can automatically discover the servers in your environment, monitor server availability, and find violations of setup best practices.

5. Microsoft Visual Studio Profiling Tools 

The Visual Studio Profiling Tools help you identify performance issues in source code and compare the performance of possible solutions. For more information, see the following resources:

o    Find Application Bottlenecks with Visual Studio Profiler

o  Beginners Guide to Performance Profiling

6. Intelligent Data Management Framework for Microsoft Dynamics AX

Intelligent Data Management Framework (IDMF) provides functionality that resembles the functionality of DynamicsPerf, and also includes a user interface. IDMF also provides tools for activities that are related to data management, such as archiving and purging.

IDMF Screenshots: 

7. Performance Analyzer for Dynamics

Performance Analyzer 1.0 for Microsoft Dynamics is the tool used by Microsoft Dynamics support, Premier Field Engineers, and product team members to diagnose performance issues with Dynamics products.  The following is a step-by-step guide to installing this tool set on your SQL Server environment.

This tool is delivered as a SQL Server solution file and can be downloaded from http://code.msdn.com/dynamicsperf.  Once the file is unzipped the solution can be opened from within SQL Server Management Studio.

On the Menu, click File | Open | Project Solution

Choose the Performance Analyzer 1.0 for Microsoft Dynamics Solution file

To view the Solution Explorer in SQL Server Management Studio, click View | Solution Explorer or Ctrl - ALT-L.

The next step in deploying the tool is creating the database and the objects.  In Solution Explorer, on the right hand side, select CreateDynamicsPerfDB.sql query.

This script will create a database called DynamicsPerf in the default location.  It is initially 500Mb for the data file and 100Mb for the Transaction Log file for this database.  Each data collection is approximately 500Mb to 1 GB in size.  This varies widely depending upon total system memory and how much activity is occurring.  To manage database size there is a job called DYNPERF_Capture_Stats_Purge.  This job by default will run once per day at 6PM and delete data older then 7 days old.  Adjusting the number of days to retain data in this job  is the method for controlling the database size of the DynamicsPerf database.

The next step is to create all of the database objects and SQL Jobs used by Performance Analyzer.  Select CreateDynamicsPerfObjects.sql query on the right hand side in Solution Explorer.

This script creates all of the Tables, Procedures, and SQL Jobs used by the tool in the DynamicsPerf database.  In order to do blocking analysis, the script will set a configuration option called "Blocked Processes Report" to five seconds.  (Setting up data gathering for blocking will be explained in another blog post.)  After running the create object script the results returned from this script should look as follows:

Performance Analyzer is designed to capture all Query and Index data for one database.  To make this easier to configure, all basic functionality to collect data has been provided in a set of SQL Jobs.  In order to do basic data capture, the SQL Job called DYNPERF_Capture_Stats must be edited to define which database to collect performance data on.

Either double click this job, or right click and select properties.  This should bring up the following dialog to edit the SQL job.

Click on the Steps page on the left hand side of this dialog

Click the Edit button to edit the single step in this job.

In the command of this job step, change the '<dbname>' to the name of the database to collect performance data from such as 'AX_Live'.  Click the 'Ok' button to save the changes.

At this point, basic data collection has been setup for Performance Analyzer .  By default data will be collected once per day at 5PM.  This is the default schedule for the DYNPERF_Capture_Stats  job, but you can edit it to find a time that better fits the data you're trying to collect.


Reference from : http://technet.microsoft.com/ and other MS materials...