SQL Server Configuration

In the first part, I will cover SQL server configuration settings that will be helpful in improving the performance of whole AX environment or of a particular module. I am presenting these points in more concise way and in tabular form.

Server Hardware/Windows settings Best practice to follow
Processor 4 to 8 cores
Virtualization Certified virtualization platform if VM
Power Management Settings Should be set to High Performance
Hyper Threading Must be supported
Processor Scheduling Should be set as Background Services


SQL server settings Best practice to follow
Max Degree of Parallelism 1
Max worker threads 0
Priority Boost Disabled
Lightweight pooling Should be disabled unless needed
Server Authentication Windows Authentication mode
Default Index fill factor 70 to 90 for instance is fine – this can give proper default value for index rebuild
Compress backup Enabled (Recommended)
Network Configuration Only necessary protocols should be enabled
Lock Pages in Memory Right granted to SQL Server Service Account (to allow Instant File Initialization)
Performance volume maintenance Granted (Trace flag 845 for Standard Edition)
Trace Flags Enabled 4199 is recommended in most cases. Other trace flags are recommended on a case by case basis.

I will be presenting database configuration best practices in my next blog.

Leave a Reply

Recent Comments