Источник:
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