Microsoft Dynamics AX 2009/2012 – SQL Server 2008 R2/2012/2014 Configuration Check List for “Go-Live"
Server Level Settings
- Max Degree of Parallelism
- Best Practice is that the Max Degree of Parallelism on the production Dynamics AX SQL Server be set 1
- Parallelism – When SQL is sent a SQL statement it runs it through the optimizer which is a bunch of algorithms to determine the most effect way to execute the statement. Once of the things it determines is how many CPUs to use, this is what is called Parallelism. Because of the unique data make up and workload of ERP systems and AX’s cursor architecture having SQL use multiple CPU’s to execute a statement can actually decrease performance by forcing SQL server to only CPU to execute a statement. This can be seen in the SQL wait statistics and thread scheduling on the CPU is one of the highest wait times on the entire system because SQL in unnecessarily breaking up simple statements into multiple threads that then have to be scheduled on a CPU.
- Trace Flags 1117, 1224, 2371, 4199
- More about these SQL Trace Flags included in the below blog post
- Max Server Memory
- Max Server memory should be calculated as followsExample:Max Server Memory should be set at 120.3 GB
- 128 – (6 + (850 * 2MB)
- Total Memory installed – (4,6 +(856 * 2MB)
- Service Pack and Cumulative Update
- Best Practice dictates that you at least be on the latest Service Pack for the SQL Server version you are on. It is recommended that you also stay current with Cumulative updates but it is not a best practice just a recommendation.
- Performance Analyzer for Dynamics
- Make sure DynamicsPerf is installed
The link to download DynamicsPerf
- TempDB Configuration
- Best Practice dictates that you have as many TempDB Data Files as your do cores on your SQL Server. I find this a little over kill and say start with 8 files that when added together equal 20% the size of the Dynamics AX production database.
- Default Fill Factor
- Do not set this as this is server wide and not a database setting.
- Power Settings
- Power settings on the SQL Server should always be set at “Best Performance”
- Priority – Back Ground Services
- In System Properties under the “advanced” tab select “performance” then select “advanced” again and set the “processor scheduling” Adjust Best for “Background Services
- Read Committed Snapshot Isolation should be enabled on Dynamics AX Production database this will increase concurrency as will stop readers and writers of the same data from blocking each other. This should be on by default. To check to see if it is enabled run the blow SQL Script against the Master database on the Production SQL Server
SELECT NAME, is_read_committed_snapshot_on FROM SYS.databases
- “Auto Update” and “Auto Create” Statistics
- These also should be on by default to check this run the following SQL Statement against the Master database on the Production SQL Server
SELECT NAME, is_auto_create_stats_on, is_auto_update_stats_on FROM SYS.databases
- Database Compatibility Level
- The Database Compatibility Level should be set to the version of SQL Server that is installed and running.
- Database Recovery Mode
- This should be set to FULL
- Back-up Schedule
- Setup a back-up schedule that at least does a FULL back-up once a day and transaction log back-ups every hour at a minimum. This will put your theoretical max data loss at one hour
- A plan I recommend to a lot of customers is a FULL back-up at night and differential at noon and transaction log back-ups every 15 minutes. Which puts a max theoretical data loss at 15 minutes.
- Database and Log Auto Grow
- The Auto Grow on both the Production database and TempDB data files should be set at a static number not a percentage. My advice it to set the data file on the production AX database at 500 MB autogrow and TempDB data files at 250 MB autogrow.
- Page Verify
- The Page Verify option should be set to CHECKSOME
- Maintenance Plan
- It is important to have a regular Maintenance Plan for the Production Database. I would recommend re-organizing indexes with 50% or less fragmentation and rebuild for all indexes over 50% fragmentation and run this once a week. Second create a SQL Job that runs sp_updatestats every day. For more on maintenance plans and scripts please see the below blog post.https://blogs.msdn.microsoft.com/axinthefield/dynamics-axsql-maintenance-scripts/