axinthefield: Determining Disk Latency with Performance Analyzer for Microsoft Dynamics

[Blog bot]

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

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. So, 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:

database_id

ID of database.

file_id

ID of file.

sample_ms

Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.

num_of_reads

Number of reads issued on the file.

num_of_bytes_read

Total number of bytes read on this file.

io_stall_read_ms

Total time, in milliseconds, that the users waited for reads issued on the file.

num_of_writes

Number of writes made on this file.

num_of_bytes_written

Total number of bytes written to the file.

io_stall_write_ms

Total time, in milliseconds, that users waited for writes to be completed on the file.

io_stall

Total time, in milliseconds, that users waited for I/O to be completed on the file.

size_on_disk_bytes

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.

file_handle

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:

SELECT E.STATS_TIME,

E.DATABASENAME,

E.FILE_ID,

CASE

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,

CASE

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,

CASE

WHEN ( E.NUM_OF_READS - START.NUM_OF_READS ) = 0 THEN 0

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,

CASE

WHEN ( E.NUM_OF_WRITES - START.NUM_OF_WRITES ) = 0 THEN 0

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:



STATS_TIME

DATABASENAME

FILE_ID

Read_Latency

Write_Latency

Avg_Bytes_Per_Read

Avg_Bytes_Per_Written

Num_of_Reads

Num_of_Writes

Read_IO_Stalls_MS_Last

Write_IO_Stalls_MS_Last

Rank

2011-05-18 09:34:00.000

AX2009

1

9

9

8192

12723

72

6680

707

60556

1

2011-05-18 09:34:00.000

AX2009

2

6

4

920549

8855

38

3973

255

17319

2





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://blogs.msdn.com/b/axinthefield...-dynamics.aspx