Measure disk IO with TSQL

There is a WAITFOR DELAY after the first SELECT, default is 2 seconds, change to whatever you want.

 SELECT 1 AS x
     , a.database_id
     , b.[name]
     , b.[type_desc]
     , a.size_on_disk_bytes
     , a.io_stall_read_ms
     , a.num_of_reads
     , a.num_of_bytes_read
     , b.[type]
     , a.io_stall_write_ms
     , a.num_of_writes
     , a.num_of_bytes_written
     , b.physical_name
     , GETDATE() AS ts
 INTO #data
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
 INNER JOIN sys.master_files AS b ON a.file_id = b.file_id AND a.database_id = b.database_id;
  
 WAITFOR DELAY '00:00:02';
  
 INSERT #data
     SELECT 2
         , a.database_id
         , b.[name]
         , b.[type_desc]
         , a.size_on_disk_bytes
         , a.io_stall_read_ms
         , a.num_of_reads
         , a.num_of_bytes_read
         , b.[type]
         , a.io_stall_write_ms
         , a.num_of_writes
         , a.num_of_bytes_written
         , b.physical_name
         , GETDATE()
     FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
     INNER JOIN sys.master_files AS b ON a.file_id = b.file_id AND a.database_id = b.database_id;
  
 SELECT DB_NAME(d1.database_id) AS [database_name]
     , d1.[name] AS logical_file_name
     , d1.[type_desc] AS logical_file_type
     --, d1.[type]
     , d1.physical_name AS physical_file_name
     , d2.size_on_disk_bytes - d1.size_on_disk_bytes AS size_on_disk_bytes
  
     , d2.io_stall_read_ms - d1.io_stall_read_ms AS io_stall_read_ms
     , d2.num_of_reads - d1.num_of_reads AS num_of_reads
     , d2.num_of_bytes_read - d1.num_of_bytes_read AS num_of_bytes_read
     , CASE WHEN (d2.num_of_reads - d1.num_of_reads) > 0 THEN (d2.io_stall_read_ms - d1.io_stall_read_ms) / (d2.num_of_reads - d1.num_of_reads) ELSE NULL END AS read_stall_ms_average
     , CASE 
         WHEN d1.[type] = 0 THEN 30 /* data files */
         WHEN d1.[type] = 1 THEN 5 /* log files */
         ELSE 0
       END AS read_stall_ms_avg_max
     , CASE
         WHEN d1.[name] = 'tempdb' THEN 'N/A'
         WHEN d1.[type] = 1 THEN 'N/A' /* log files */
         ELSE 'PAGEIOLATCH*'
       END AS read_related_wait_stat
     , d2.io_stall_write_ms - d1.io_stall_write_ms AS io_stall_write_ms
     , d2.num_of_writes - d1.num_of_writes AS num_of_writes
     , d2.num_of_bytes_written - d1.num_of_bytes_written AS num_of_bytes_written
     , CASE WHEN (d2.num_of_writes - d1.num_of_writes) <> 0 THEN (d2.io_stall_write_ms - d1.io_stall_write_ms) / (d2.num_of_writes - d1.num_of_writes) ELSE NULL END AS write_stall_ms_avg
     , CASE 
         WHEN d1.[type] = 0 THEN 30 /* data files */
         WHEN d1.[type] = 1 THEN 2 /* log files */
         ELSE 0
       END AS write_stall_ms_avg_max
     , CASE
         WHEN d1.[type] = 1 THEN 'WRITELOG' /* log files */
         WHEN d1.[name] = 'tempdb' THEN 'xxx' /* tempdb data files */
         WHEN d1.[type] = 0 THEN 'ASYNC_IO_COMPLETION' /* data files */
         ELSE 'xxx'
       END AS write_related_wait_stat
     , DATEDIFF(MILLISECOND, d1.ts, d2.ts) / 1000.0 AS sample_time_second
 FROM #data d1
 INNER JOIN #data d2 ON d1.database_id = d2.database_id
     AND d1.[name] = d2.[name]
     AND d1.[type_desc] = d2.[type_desc]
     AND d1.[type] = d2.[type]
     AND d1.physical_name = d2.physical_name
     AND d1.x = 1
     AND d2.x = 2
 WHERE (d2.io_stall_read_ms - d1.io_stall_read_ms) + 
     (d2.num_of_reads - d1.num_of_reads) + 
     (d2.num_of_bytes_read - d1.num_of_bytes_read) + 
     (d2.io_stall_write_ms - d1.io_stall_write_ms) + 
     (d2.num_of_writes - d1.num_of_writes) + 
     (d2.num_of_bytes_written - d1.num_of_bytes_written) <> 0;
  
 
 DROP TABLE #data; 

Leave a Reply

Your email address will not be published. Required fields are marked *