This article describes how to use the Client Monitor tool to optimize the C/AL code in Microsoft Dynamics NAV and in Microsoft Business Solutions - Navision. This article also describes how to make sure that you use the correct keys when you filter data. If you use the correct keys, the performance of Microsoft Dynamics NAV is improved, and blocking is minimized. You can use the Client Monitor tool to monitor a process. And, you can find bottlenecks in the key usage that could eventually cause performance issues in Microsoft Dynamics NAV.
To use the Client Monitor tool, follow these steps:
Open the database on which you will be working.
Import the "Client Monitor.fob" file from the Objects folder of Microsoft Dynamics NAV Database Resource Kit. This step creates 34 objects in the database. The following objects are the objects that are used in this procedure:
Client Monitor form (150020)
Client Monitor (Key Usage) form (150022)
The Client Monitor form that is mentioned here is separate from Client Monitor on the Tools menu.
To download Microsoft Dynamics NAV Database Resource Kit, visit the following Microsoft Web site:
Prepare the process about which you want to capture data. For example, you can capture data about the "sales order posting" process.
Before you start the process, click Client Monitor on the Tools menu.
In the Client Monitor dialog box, click the Options tab. If you are running on the Microsoft SQL Server Option for Microsoft Dynamics NAV, make sure that you click to select the Show SQL statement check box in the Advanced section, and then click Start.
Start the process about which you want to capture data. For example, post the sales order.
When the process is complete, click Stop in the Client Monitor dialog box.
The client monitor that is embedded in Microsoft Dynamics NAV has now captured the data that will be processed by the Client Monitor tool that you imported earlier into the database. After you capture the data by using the embedded client monitor, follow these steps:
Open Object Designer, locate the Client Monitor form (150020), and then click Run. This process collects the data that was captured by the embedded client monitor. The data is processed into a more readable format.
Open the Client Monitor (Key Usage) form (150022). Notice the following five columns:
Good Filtered Start of Key This column displays the first fields of the key that match the filter that is applied.
Key Remainder This column displays the remaining fields of the key that are not used in the filter.
Key Candidate Fields This column displays the fields that could be used to match the filter.
Filter This column displays the filter that is applied.
Elapsed Time (ms) This column displays the time that is used to implement the filter.
For each FIND/NEXT function in the Client Monitor (Key Usage) form, review the contents of the columns. Focus primarily on the records that have a long elapsed time. Ideally, the filters that are listed in the Filter column should be matched together with the correct key. Be aware of the following things:
If the Good Filtered Start of Key column lists most of the fields in the filter, you are using the correct key.
If the Good Filtered Start of Key column lists only some fields in the filter, or if no fields are listed, check the elapsed time, and then verify how long the process took. You can locate the C/AL code to see whether any adjustments or any corrections can be made. To do this, use the information in the following columns:
Note The C/AL code should have a SETCURRENTKEY function before each SETRANGE statement or before each SETFILTER statement.
The order of the fields in the filter must match the order of the fields in the key that is used. When you filter data, never skip a field in the key.
By default, Microsoft Dynamics NAV 4.0 Service Pack 3 Platform Update Rollup 6 enables index hinting. If you use both index hinting and the SETCURRENTKEY function, as described earlier, SQL Server is directed to use a specific index that matches the query. Therefore, the response times are more efficient. For more information about Platform Update Rollup 6, see the "References" section.
For more information about index hinting in the platform update rollup, refer to the following blog that is posted by the Microsoft Dynamics NAV Sustained Engineering team: