SQL SERVER – Best Practices for Dynamics NAV AX CRM

In the last 3 months, I have done nearly 11 consulting engagement for Dynamics NAV AX CRM while working on Comprehensive Database Performance Health Check. While working on the SQL Server application, we have options to do quite lots of things but when it is about Dynamics, there are limited options.

Best Practices for Dynamics NAV AX CRM

If your Dynamics system is running slow, you can try out following various optimization suggestions.

TempDB Configuration

TempDB is one of the most important databases in your system and it is very critical to configure it optimally. Make sure that your TempDB is on the fastest possible drive (possibly SSD). It is a general practice to create as many as tempDB files as many as core you have. It is a good idea that all the files of the TempDB of the same size. If you are using SQL Server 2014 or earlier version of SQL Server to enable equal file growth you can consider using trace flags 1117 or 1118 but for the SQL Server 2016 or later version of SQL Server, you do not have to set this trace flags as they are enabled automatically.

You can read the following blog posts to accomplish the task:

• Moving TempDB to New Drive
• Is tempDB behaving like a Normal DB?
• Regular Table or Temp Table – TempDB Logging Explained
• Identifying Query Growing TempDB
• Removing Extra TempDB Files
• How to Shrink TempDB Without SQL Server Restart?

Auto Growth

Do change your data file and log file’s auto growth value to a higher value. It is not a good idea to keep them in the percentages as that will give you unexpected growth. In most cases, you should keep it a fixed value which is equal to your weekly file growth.

You can read the following blog posts to accomplish the task:

• Enable Autogrowth Database Property
• How to Track Autogrowth of Any Database?
• Introducing AutoGrow and Mixed_Page_Allocations Options
• When/Who did Auto Grow for the Database?
• Find the Growth Size for All files in All Databases

Max Degree of Parallelism (MAXDOP)

It goes without saying for Dynamics is a kind of database that contains most of the small transactions. The recommended value for the MAXDOP for the dynamics is 1. If you are on SQL Server 2014 or earlier version of SQL Server, you can keep the value of this setting at the server level at 1 but if you using SQL Server 2016 or later version of SQL Server, it is a good idea to set this value at the database level.

You can read the following blog posts to accomplish the task:

• Microsoft Dynamics CRM – Max Degree of Parallelism Settings and Slow Performance
• Override Server’s Configuration of Max Degree of Parallelism
• Do Queries Always Respect Cost Threshold of Parallelism?
• What is the Priority of Database Scoped Configurations? 

Auto Update Statistics / Auto Create Statistics

SQL Server builds an execution plan based on the statistics. It is very important that we keep the database statistics updated. I often see many DBA keeping the Auto Update Statistics / Auto Create Statistics to false/off. However, I have seen significant improvement in performance when they are kept on. They are database-specific settings so when they are turned on, which is a default value, SQL Server builds better execution plans.

You can read the following blog posts to accomplish the task:

• How to Enable Auto Update Statistics and Auto Create Statistics with T-SQL
• Enable Automatic Statistic Update on Database
• When Are Statistics Updated – What Triggers Statistics to Update
• Update Table Statistics in Parallel with FULLSCAN

Index Maintenance

Just like every other database, Dynamics NAV CRM has also indexes and they need to be maintained. You should also add missing indexes to the database and also if you find unused indexes, you should also drop them from the database. Regular index maintenance is always a good idea and you should update statistics more frequently and rebuild indexes once a month if possible.

You can read the following blog posts to accomplish the task:

• Missing Index Script – Download
• Missing Index Script – Download
• An Index Reduces Performance of SELECT Queries

Call To Action

If your dynamics system is running, slow, I strongly suggest that you follow the above five points. Once you complete them and if you still face further problems with your system, I think it is time to look deeper inside together and find the culprit. Just drop a note to me and we can quickly start with Comprehensive Database Performance Health Check and make your system run faster.

Reference: Pinal Dave (https://blog.sqlauthority.com)