Investigating the performance of the database

There are a few key performance troubleshooting techniques that are effective in identifying the bottlenecks in the performance of the database.

Updating statistics

There are several tools available from various Microsoft Dynamics ISV(s) that provide effective tools for monitoring the performance of a Dynamics NAV database. We will limit ourselves to the SQL Resource Kit provided by Microsoft in this section of the chapter.

The Index Defrag tool

The Index Defrag tool is a Dynamics NAV tool that comes along with the Microsoft Dynamics NAV SQL Resource Kit, and helps in identification of the keys that need to be defragmented:

1. 1. Look for the index defrag tool.fob file in the Dynamics NAV SQL resource toolkit. Import the object file into the database and run the 50090 form, Index Defrag Card.

2. 2. Start with filling the setup form from the Defrag menu in the bottom as shown in the following screenshot:

1. 3. Once we have set up and tested the connection...