axinthefield: Update on determining Disk Latencies with Performance Analzyer for Microsoft Dynamics

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx
==============

In my previous blog about Determining Disk Latency with Performance Analyzer for Microsoft Dynamics, I spoke of how to determine the disk latency. In testing, we have determined that the computed latency from sys.dm_virtual_io_file_stats does not match Windows Performance Monitor Avg. Disk Sec/Read and Avg. Disk Sec/Write.

There are many technical details around why the 2 values are not the same but the bottom line is that SQL doesn’t measure it at the same layer as Windows Performance Monitor. This doesn’t make this DMV any less valuable only that its harder to compare this value between two different computer systems because of some of the technical issues of how it’s measured by SQL.

The data from sys.dm_virtual_io_file_stats is very valuable when it is collected consistently on your database server. Although the computed latencies don’t match Performance monitor, the fact that the latency goes up or down is still very important to your analysis of the performance on your database server. The fact that the value in this DMV goes up still indicates that you are having more disk activity generated by the queries in your system. Performance Analyzer for Microsoft Dynamics collects this DMV on an hourly basis. This allows you to determine what the baseline numbers are for your system. This in turn allows you to be able to determine when you’re having more disk activity compared to that baseline number and determine that disk performance is slowing your system down.

The most important value of sys.dm_virtual_io_file_stats is for determining which database is causing the disk activity when the files from multiple databases are on a single disk drive. Windows Performance Monitor cannot determine this because it’s limited to measuring at the disk level and not at the file level.

For the process of analyzing disk problems, the steps are:

1. Use Windows Performance Monitor to determine there are disk latency issues
2. Review PERF_IOSTATS_VW in the DynamicsPerf database to determine which database file is generating all the activity
3. Analyze any query data that has been collected from that database to determine if inefficient queries are the cause of the disk activity.

You can download the Performance Analyzer for Microsoft Dynamics here.






Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx