Quick Scan Report – Slow Disk Reads

What this check looks for

Average read stall per drive, from sys.dm_io_virtual_file_stats(NULL, NULL) joined to sys.master_files, grouped by the first character of the physical file path. The message reports the average in milliseconds and the drive letter.

The figure is an average since the instance started. On a long running instance a recent problem is diluted; on a recently restarted one there may not be enough operations to mean much yet.

Why it matters

Slow reads mean every query that touches disk is slower, and the buffer pool being too small makes it worse in a way that looks like a storage problem.

Two things produce this finding and they need separating:

• The storage is genuinely slow. The path from SQL Server to the platter has a problem, or the storage is simply not fast enough for the workload.
• SQL Server is reading more than it should. A missing index turning a seek into a scan, or a buffer pool too small to hold the working set, means far more physical reads. The disk is performing normally and is being asked to do too much.

The second is more common than people expect, and the fix is in the database rather than in the storage.

Rules of thumb for read latency, per operation:

What SQL Server measures is not what the array measures. dm_io_virtual_file_stats records the time from the engine issuing the I/O to it completing, which includes the HBA, the switch fabric, the multipath driver, the file system filter stack and any antivirus product sitting in it. A storage team reporting sub-millisecond latency at the array and SQL Server reporting 30 ms are frequently both correct, and the difference is the finding.

How to confirm it yourself

Per drive, which matches what the check reports:

SELECT LEFT(mf.[physical_name], 1)                                     AS [drive],
       SUM(vfs.[num_of_reads])                                          AS [reads],
       CAST(SUM(vfs.[io_stall_read_ms])  * 1.0 / NULLIF(SUM(vfs.[num_of_reads]), 0)  AS DECIMAL(10,1)) AS [avg_read_ms],
       SUM(vfs.[num_of_writes])                                         AS [writes],
       CAST(SUM(vfs.[io_stall_write_ms]) * 1.0 / NULLIF(SUM(vfs.[num_of_writes]), 0) AS DECIMAL(10,1)) AS [avg_write_ms],
       CAST(SUM(vfs.[num_of_bytes_read]) / 1073741824.0 AS DECIMAL(12,1)) AS [gb_read]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 GROUP BY LEFT(mf.[physical_name], 1)
 ORDER BY [avg_read_ms] DESC;

Per file, to find which database is driving it:

SELECT DB_NAME(vfs.[database_id])                                       AS [database_name],
       mf.[name], mf.[type_desc],
       CAST(vfs.[io_stall_read_ms] * 1.0 / NULLIF(vfs.[num_of_reads], 0) AS DECIMAL(10,1)) AS [avg_read_ms],
       vfs.[num_of_reads],
       mf.[physical_name]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 WHERE vfs.[num_of_reads] > 1000
 ORDER BY [avg_read_ms] DESC;

Whether it is actually hurting, from the waits:

SELECT [wait_type], [waiting_tasks_count],
       [wait_time_ms] / 1000 AS [wait_seconds],
       [wait_time_ms] / NULLIF([waiting_tasks_count], 0) AS [avg_ms]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] LIKE 'PAGEIOLATCH%'
 ORDER BY [wait_time_ms] DESC;

And whether memory is the real cause:

SELECT [object_name], [counter_name], [cntr_value]
  FROM sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE [counter_name] IN ('Page life expectancy', 'Buffer cache hit ratio')
   AND [object_name] LIKE '%Buffer Manager%';

A low page life expectancy alongside high PAGEIOLATCH_SH means pages are being evicted and read back, so the disk is busy because memory is short. Adding memory or fixing a query that scans a large table will do more than faster storage.

How to fix it

Work out which of the two you have before spending anything.

If SQL Server is reading too much:

1. Look at the top queries by reads. The CPU by Query and Page Reads by Query reports rank them. One scan of a large table repeated frequently is a common single cause.
2. Check for missing indexes on the tables being scanned.
3. Check max server memory. An instance with a small buffer pool re-reads the same pages endlessly. That has its own check.
4. Check for spills, which generate tempdb I/O from bad estimates.

If the storage is genuinely slow:

1. Give the storage team SQL Server’s numbers, per file and per drive, with the timestamps. The difference between their measurement and this one is the diagnostic.
2. Check antivirus exclusions. .mdf, .ndf, .ldf and .bak should be excluded from real time scanning. This is free and it is regularly the answer.
3. Check the multipath configuration if this is a SAN, and the queue depth settings on the HBA.
4. Separate the workloads. Tempdb, data and log competing on one volume is a common cause, and each has its own placement check on this report.
5. Look at instant file initialization. Without it, every data file growth zeroes the new space, which shows up as a stall while it happens.

Measure before and after. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) resets the wait statistics so a comparison starts from a known point.

How long it takes

About four hours to separate a workload cause from a storage cause and gather the evidence. Acting on either is a separate piece of work.

Related reports

Related checks

Frequently asked questions

Our storage is all flash. Why is this firing? Because the measurement includes everything between the engine and the flash. Antivirus, a filter driver or a saturated path all add latency the array never sees.

The average is skewed by one bad period. Likely, since it is cumulative since startup. Snapshot the DMV twice a few minutes apart and compare deltas for a current reading.

Should I just add memory? If page life expectancy is low, that may genuinely be the cheapest fix, because it removes reads rather than making them faster. Check before buying either.

What about writes? Read latency is what this check reports. The queries above show both, and slow writes on a log file have their own check because they show up as commit time.