AX 2012 Performance – Configure SQL Server and Storage

Configuring max degree of parallelism

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.