Blitz Result: Slow Storage Reads or Writes

SQL Server feels the need – the need for speed. Storage speed, to be more specific. One of the most common bottlenecks is underperforming storage subsystems.

SQL Server tracks read and write speeds for each database file – both data and log files. This part of our SQL Server sp_Blitz script checks sys.dm_io_virtual_file_stats looking for average read stalls (latency) over 200 milliseconds and average write stalls over 100 milliseconds. Yes, those thresholds are horrifically high – but that’s the point. We only want to alert you when the numbers are awful.

This script breaks it down by file:

	SELECT  DB_NAME(a.database_id) AS [Database Name] ,
        b.name + N' [' + b.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS [Logical File Name] ,
        UPPER(SUBSTRING(b.physical_name, 1, 2)) AS [Drive] ,
        CAST(( ( a.size_on_disk_bytes / 1024.0 ) / (1024.0*1024.0) ) AS DECIMAL(9,2)) AS [Size (GB)] ,
        a.io_stall_read_ms AS [Total IO Read Stall] ,
        a.num_of_reads AS [Total Reads] ,
        CASE WHEN a.num_of_bytes_read > 0 
            THEN CAST(a.num_of_bytes_read/1024.0/1024.0/1024.0 AS NUMERIC(23,1))
            ELSE 0 
        END AS [GB Read],
        CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) AS [Avg Read Stall (ms)] ,
        CASE 
            WHEN b.type = 0 THEN 30 /* data files */
            WHEN b.type = 1 THEN 5 /* log files */
            ELSE 0
        END AS [Max Rec Read Stall Avg],
        a.io_stall_write_ms AS [Total IO Write Stall] ,
        a.num_of_writes [Total Writes] ,
        CASE WHEN a.num_of_bytes_written > 0 
            THEN CAST(a.num_of_bytes_written/1024.0/1024.0/1024.0 AS NUMERIC(23,1))
            ELSE 0 
        END AS [GB Written],
        CAST(a.io_stall_write_ms / ( 1.0 * a.num_of_writes ) AS INT) AS [Avg Write Stall (ms)] ,
        CASE 
            WHEN b.type = 0 THEN 30 /* data files */
            WHEN b.type = 1 THEN 2 /* log files */
            ELSE 0
        END AS [Max Rec Write Stall Avg] ,
        b.physical_name AS [Physical File Name],
        CASE
            WHEN b.name = 'tempdb' THEN 'N/A'
            WHEN b.type = 1 THEN 'N/A' /* log files */
            ELSE 'PAGEIOLATCH*'
        END AS [Read-Related Wait Stat],
        CASE
            WHEN b.type = 1 THEN 'WRITELOG' /* log files */
            WHEN b.name = 'tempdb' THEN 'xxx' /* tempdb data files */
            WHEN b.type = 0 THEN 'ASYNC_IO_COMPLETION' /* data files */
            ELSE 'xxx'
        END AS [Write-Related Wait Stat],
        GETDATE() AS [Sample Time],
        b.type_desc
	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
	WHERE   a.num_of_reads > 0
			AND a.num_of_writes > 0
	ORDER BY  CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) DESC;

SELECT DB_NAME(a.database_id) AS [Database Name] ,

b.name + N' [' + b.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS [Logical File Name] ,

UPPER(SUBSTRING(b.physical_name, 1, 2)) AS [Drive] ,

CAST(( ( a.size_on_disk_bytes / 1024.0 ) / (1024.0*1024.0) ) AS DECIMAL(9,2)) AS [Size (GB)] ,

a.io_stall_read_ms AS [Total IO Read Stall] ,

a.num_of_reads AS [Total Reads] ,

CASE WHEN a.num_of_bytes_read > 0

THEN CAST(a.num_of_bytes_read/1024.0/1024.0/1024.0 AS NUMERIC(23,1))

ELSE 0

END AS [GB Read],

CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) AS [Avg Read Stall (ms)] ,

CASE

WHEN b.type = 0 THEN 30 /* data files */

WHEN b.type = 1 THEN 5 /* log files */

ELSE 0

END AS [Max Rec Read Stall Avg],

a.io_stall_write_ms AS [Total IO Write Stall] ,

a.num_of_writes [Total Writes] ,

CASE WHEN a.num_of_bytes_written > 0

THEN CAST(a.num_of_bytes_written/1024.0/1024.0/1024.0 AS NUMERIC(23,1))

ELSE 0

END AS [GB Written],

CAST(a.io_stall_write_ms / ( 1.0 * a.num_of_writes ) AS INT) AS [Avg Write Stall (ms)] ,

CASE

WHEN b.type = 0 THEN 30 /* data files */

WHEN b.type = 1 THEN 2 /* log files */

ELSE 0

END AS [Max Rec Write Stall Avg] ,

b.physical_name AS [Physical File Name],

CASE

WHEN b.name = 'tempdb' THEN 'N/A'

WHEN b.type = 1 THEN 'N/A' /* log files */

ELSE 'PAGEIOLATCH*'

END AS [Read-Related Wait Stat],

CASE

WHEN b.type = 1 THEN 'WRITELOG' /* log files */

WHEN b.name = 'tempdb' THEN 'xxx' /* tempdb data files */

WHEN b.type = 0 THEN 'ASYNC_IO_COMPLETION' /* data files */

ELSE 'xxx'

END AS [Write-Related Wait Stat],

GETDATE() AS [Sample Time],

b.type_desc

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

WHERE a.num_of_reads > 0

AND a.num_of_writes > 0

ORDER BY CAST(a.io_stall_read_ms / ( 1.0 * a.num_of_reads ) AS INT) DESC;

What exactly is a bad number here? That’s a really good question, and it’s open for debate. At Microsoft SQL Customer Advisory Team – in point #4 in their Top 10 OLTP Issues, they suggest that reads over 15ms are a bottleneck and that log file writes should be 1ms or less. Keep in mind that this team usually deals with high-performance environments, though – not every SQL Server can afford to be quite this fast.

No matter whose guidelines you pick, I’ve got bad news: you’re already doing worse.

To Fix the Problem

There’s two ways to fix slow storage: make the storage go faster, or ask the storage do to less work.

The second option – asking storage to do less work – is usually the easiest way to start. By dropping unused indexes, we can insert/update/delete data faster, run backups faster, do DBCCs faster, and even do index rebuild jobs faster. By using the right indexes, we can avoid storage-intensive table scans and do less data juggling in TempDB.

Return to sp_Blitz or Ask Us Questions