13.06.2011, 06:13 | #1 |
Участник
|
axinthefield: Determining Disk Latency with Performance Analyzer for Microsoft Dynamics
Источник: 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
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
|