In the first part, I presented SQL server configuration settings best practices that can improve AX performance. The link to the first part is below. In this part, I will cover database configuration settings and in the end, there is a small guide for disk performance counters that can be helpful in analyzing disk IO. These settings are much important to consider because they can have greater influence on the overall performance of AX.

First Part:

https://www.axpulse.com/dynamics-ax-performance-improvement-best-practices-series-part-13/

Database configuration:

SQL Server DB Settings Best Practices
Recovery Model Full
Compatibility Level Same as version of SQL Server
Collation Same as TempDB Case Insensitive, Accent Sensitive for Dynamics AX
Auto Close Disabled
Auto Shrink Disabled
Auto Create Stats Enabled
Auto Update Stats Enabled
Auto Update Stats Async Disabled
Page Verify Checksum
Allow Snapshot Isolation Allow snapshot isolation should be set OFF in the AX database.

Database file configuration:

SQL Database File Best Practices
Data and Log File Placement Data and Log files on separate drives
Data File Auto Growth Increment Avoid auto growth by sizing the files large enough to start with. 200MB to 500MB
Log File Auto Growth Increment Avoid auto growth by sizing the files large enough to start with. 200MB to 500MB
Data file Free space Space should be periodically reviewed and adjusted when necessary based or Auto Growth should be set. It is a better strategy to pre-allocate the Database data file to a size which accounts for the expected growth some time ahead. You can still set Auto Growth to avoid any issue before the next pre-allocation.

Database file configuration for TempDB:

SQL Database File Best Practices
Data and Log File Placement Data and Log files on separate drives
Data File Auto Growth Increment Avoid auto growth by sizing the files large enough to start with. 200MB to 500MB
Log File Auto Growth Increment Avoid auto growth by sizing the files large enough to start with. 200MB to 500MB
TempDB Data File Count 1 data file per logical processor
TempDB Data Files Size TempDB data files should have the same initial size and the same auto grow
TempDB Data File Size 20-25% of AX database or just large enough to avoid file auto growth.
TempDB Log File Size Large enough to avoid file auto growth.

Disk performance counters:

Measurements are average disk sec/read & average disk sec/write.

Status Scale
OK 0 to 10 ms
Warning 11 to 24 ms
I/O

Bottleneck

25+ ms

Leave a Reply

Recent Comments

    Archives

    Categories