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.

SQL SERVER - Best Practices for Dynamics NAV AX CRM dynamics-800x290

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:

Solarwinds

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:

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:

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:

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:

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)

Solarwinds
, , , , , , ,
Previous Post
SQL SERVER – Number of Tables Memory Optimized in TempDB in SQL Server 2019
Next Post
SQL SERVER – Row Goal and Performance

Related Posts

1 Comment. Leave new

Leave a Reply

Menu