Wait--使用sys.dm_io_virtual_file_stats来查看IO延迟

 

/*============================================================================
  File:     VirtualFileStats.sql

  Summary:  sys.dm_io_virtual_file_stats

  Date:     March 2011

------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2011, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Use this script, based on code from Jimmy May
-- This is what I use on client systems
SELECT 
    --virtual file latency
    ReadLatency =
        CASE WHEN num_of_reads = 0
            THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
    WriteLatency =
        CASE WHEN num_of_writes = 0 
            THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
    Latency =
        CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
            THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
  --avg bytes per IOP
    AvgBPerRead =
        CASE WHEN num_of_reads = 0 
            THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
    AvgBPerWrite =
        CASE WHEN io_stall_write_ms = 0 
            THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
    AvgBPerTransfer =
        CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
            THEN 0 ELSE
                ((num_of_bytes_read + num_of_bytes_written) / 
                (num_of_reads + num_of_writes)) END,
             
    LEFT (mf.physical_name, 2) AS Drive,
    DB_NAME (vfs.database_id) AS DB,
    vfs.*,
    mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC