Are you having slow disk performance with your Dynamics Product? Do you even know if you are?
SQL Server data management views can help you determine if you are having disk latency issues without needing to run Windows Performance Monitor. The DMV that can help us determine this is called sys.dm_io_virtual_file_stats. This DMV keeps track of all I/O per database file. Unlike Windows Performance Monitor which gives us data per disk, SQL Server can help us determine which database file on that disk is the actual cause of the performance issue.
These are the columns in this DMV:
ID of database.
ID of file.
Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.
Number of reads issued on the file.
Total number of bytes read on this file.
Total time, in milliseconds, that the users waited for reads issued on the file.
Number of writes made on this file.
Total number of bytes written to the file.
Total time, in milliseconds, that users waited for writes to be completed on the file.
Total time, in milliseconds, that users waited for I/O to be completed on the file.
Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.
Windows file handle for this file.
Read latency = io_stall_read_ms / num_of_reads
Write latency = io_stall_write_ms / num_of_writes
Bytes per Read = num_of_bytes_read / num_of_reads
Bytes per Write = num_of_bytes_written / num_of_writes
Performance Analyzer for Microsoft Dynamics collects the data from this DMV into a table called DISKSTATS. With this data, it is possible to calculate the above numbers for a given timeframe within Dynamics AX. For example, if you collect data in the morning and then collect data again at the end of the day, you can determine disk latency for that day. The default schedule for data collection in Performance Analyzer for Microsoft Dynamics is once per day. This would allow you to determine latency from day to day.
The following query will give you the latency for all data collections:
WHEN ( E.NUM_OF_READS - START.NUM_OF_READS ) = 0 THEN 0
ELSE ( E.IO_STALL_READ_MS - START.IO_STALL_READ_MS ) / ( E.NUM_OF_READS - START.NUM_OF_READS )
END AS Read_Latency,
WHEN ( E.NUM_OF_WRITES - START.NUM_OF_WRITES ) = 0 THEN 0
ELSE ( E.IO_STALL_WRITE_MS - START.IO_STALL_WRITE_MS ) / ( E.NUM_OF_WRITES - START.NUM_OF_WRITES )
END AS Write_Latency,
ELSE ( E.NUM_OF_BYTES_READ - START.NUM_OF_BYTES_READ ) / ( E.NUM_OF_READS - START.NUM_OF_READS )
END AS Avg_Bytes_Per_Read,
ELSE ( E.NUM_OF_BYTES_WRITTEN - START.NUM_OF_BYTES_WRITTEN ) / ( E.NUM_OF_WRITES - START.NUM_OF_WRITES )
END AS Avg_Bytes_Per_Written,
E.NUM_OF_READS - START.NUM_OF_READS AS Num_of_Reads,
E.NUM_OF_WRITES - START.NUM_OF_WRITES AS Num_of_Writes,
E.IO_STALL_READ_MS - START.IO_STALL_READ_MS AS Read_IO_Stalls_MS_Last,
E.IO_STALL_WRITE_MS - START.IO_STALL_WRITE_MS AS Write_IO_Stalls_MS_Last,
Rank() OVER (partition BY E.STATS_TIME ORDER BY E.STATS_TIME DESC, ( (E.IO_STALL_READ_MS+E.IO_STALL_WRITE_MS)-(START.IO_STALL_READ_MS+START.IO_STALL_WRITE_MS)) DESC) AS Rank
FROM DISKSTATS E
INNER JOIN DISKSTATS START
ON START.DATABASENAME = E.DATABASENAME
AND START.FILE_ID = E.FILE_ID
AND START.STATS_TIME = (SELECT Max(STATS_TIME)
FROM DISKSTATS D
WHERE D.STATS_TIME < E.STATS_TIME)
The results will look like:
Depending on the disk subsystem, number of disks, and speed of those disks, the latency will very. Best practice would be for the latency numbers to be under 20-25ms. Numbers larger then this indicate a performance problem that needs to be investigated.
NOTE: You can use the above query in Performance Analyzer 1.0. This query has been added to a view named PERF_IOSTATS_VW, in Performance Analyzer 1.10.
http://dynamicsperf.codeplex.com. Downloadable link for article
In my previous blog about Determining Disk Latency with Performance Analyzer for Microsoft Dynamics