Dynamics NAV Performance Counters
Dynamics NAV performance counters and SQL Server integrated performance counters are powerful tools to track down system configuration errors and bottlenecks.
We would like to introduce the most significant of these performance counters in the NAV system environment (Dynamics NAV Performance Counters / SQL Server Performance Counters).
Keeping a NAV system performant (preferably without locks) is the most important and at the same time the most difficult task of a system manager. Particularly time-consuming are the problems whose cause lies deep in the NAV / SQL Server architecture, often not obvious at first glance. In order to get to the root of the problems, it is very helpful to know and locate the bottlenecks and bottlenecks of the system.
This is exactly where the Dynamics NAV and SQL Server Performance Counter comes in. At first glance, the sheer mass of different counters makes you lose track of them. The Dynamics NAV performance counters (Data Collector Set) provide information about how well the Microsoft Dynamics NAV Server is working. By using the V8 NAV SQL Studio monitoring tools, you can monitor the data of the performance counters of the components such as service tier, storage, physical disk and SQL Server, etc., in order to make any performance optimizations.
1. Dynamics NAV Performance Counters
There are several reasons why a SQL Server performance metrics values can be high in conjunction with the Dynamics NAV server instance. Perhaps there is non-optimal code in your NAV solution that is causing too many SQL calls. It is also possible that your data or metadata cache settings are set too low.
. There are 2 Dynamics NAV performance counters that show this problem:
# | Category | Counter | Description | Status |
---|---|---|---|---|
1 | Data and caching | % Primary key cache hit rate | Percentage of hits in the primary key cache, compared to the total requests in the primary key cache. | >90% |
2 | Data and caching | % Result set cache hit rate | Percentage of result set hits in the cache, compared to total result set cache requests. | >90% |
The primary key cache hit rate should be above 90%; otherwise, the Dynamics NAV server instance cache might be too small.
Data Cache Size (CustomSettings.config)
If you find that the "Data Cache" of the Dynamics NAV server instance is too small, you can adjust the value in this setting:
<add key=“DataCacheSize“ value=“9″ />
When running a "single tenant" NAV system the default value of 9 is probably good.
Here are the values to change the "Data Cache Size":
Value | Memory |
---|---|
9 (default) | 512Mb |
10 | 1Gb |
11 | 2Gb |
12 | 4Gb |
13 | 8Gb |
14 | 16Gb |
15 | 32Gb |
… | … |
Metadata Provider Cache Size
<add key=“MetadataProviderCacheSize“ value=“150″ />
You should only increase the "Metadata Provider Cache Size" of the Microsoft Dynamics NAV service tier if you notice a heavy load on the working memory via the performance counters.
2. Windows and SQL server performance counters
Here is our suggestion of the key performance indicators you should review:
# | Category | Counter | Description | Status |
---|---|---|---|---|
1 | Memory/Buffer Manager | Buffer Cache Hit Ratio | Indication of the percentage of data pages that exists in SQL Server memory without accessing the hard disk. A value lower than 90% is a clear sign of memory problem (increase SQL Server RAM). | >= 90% |
2 | CPU/ Processor | % User Time | The performance indicator "% User Time" corresponds to the percentage of time the processor spends executing user processes such as SQL Server. | =< 70% |
3 | Disk | Avg. Disk Sec/Read | The average time in milliseconds for each read on disk: > 20 is considered very poor, < 20 good, < 12 very good, and < 8 optimal. | =< 20MS |
4 | Disk | Avg. Disk Sec/Write | The average time in milliseconds for each write to disk: > 20 is considered very poor, < 20 good, < 12 very good, and 4 is poor, < 4 is good, < 2 is very good, < 1 is optimal. | =< 20MS |
5 | Locks | Avg. Wait Time (in ms) | The average length of the waiting time (in milliseconds) for each blocking request that could not be fulfilled immediately. An average wait time longer than 500ms indicates excessive blocking. | =< 500MS |
6 | Memory | Available Bytes | Low values for the "Available Bytes" performance indicator can be a sign that there is too little memory overall on the computer or that an application such as Dynamics NAV is not freeing up memory. | >20MB |
7 | Memory | Pages/sec | A high value for the "Pages/sec" performance indicator may indicate excessive outsourcing. | < 50 |
8 | Network | Output Queue Length | The Output Queue Length performance indicator specifies the length of the output packet queue in packets. | < 2 |
9 | SQL Server: Buffer Manager | Output Queue Length | A value above 90 percent indicates that more than 90 percent of all data requests have been met by the data cache. Keep adding memory until the value is consistently above 90 percent. | >90 |
10 | SQL Server Access Methods | Page Splits/sec | Number of page splits per second that are the result of index page overflow.1. Defragment SQL Server indexes 2. Checking the Dynamics NAV C/AL keys | 0 |
Conclusion
Performance is one of the most important non-functional requirements that Dynamics NAV faces, especially with SQL Server. By now you will have noticed that some of these issues require greater concepts and techniques to get into. However, none of these topics are unsolvable for DBAs.
If this or similar topics have piqued your interest, I would be happy to engage in an open dialogue with you.
Your team from DynamicsProject.com