Quick Scan Report – TempDB Slow IO

What this check looks for

Average I/O stall per operation for tempdb’s data files, computed from sys.dm_io_virtual_file_stats(2, NULL), which is database id 2. Read stall is io_stall_read_ms / num_of_reads and write stall is io_stall_write_ms / num_of_writes, both in milliseconds per operation.

These are averages since the instance started. That matters for reading the result: a long running instance dilutes a recent problem, and a recently restarted one may not have enough operations to be meaningful yet.

Why it matters

Tempdb is the one database every session on the instance shares, so its latency is added to everything.

Nothing else on the server has that property. A slow user database slows the queries that touch it. A slow tempdb slows:

• Every sort and hash that does not fit in its memory grant, which is every large query with a bad estimate.
• Every temporary table and table variable.
• Every cursor.
• Every online index rebuild.
• Every version store operation, which on a database with read committed snapshot isolation is every modification.
• Every GROUP BY, ORDER BY and DISTINCT that spills.

So a tempdb latency problem presents as the whole instance being slow with no single query to blame, which is the hardest kind of performance problem to chase. People look at the queries, at the indexes and at the plans, and everything looks reasonable.

Rules of thumb for the numbers, per operation:

Tempdb deserves the fastest storage on the machine, and it is frequently given the slowest, because it is “just temporary”.

How to confirm it yourself

SELECT mf.[name]                                                       AS [logical_name],
       mf.[type_desc],
       vfs.[num_of_reads],
       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_writes],
       CAST(vfs.[io_stall_write_ms] * 1.0 / NULLIF(vfs.[num_of_writes], 0) AS DECIMAL(10,1)) AS [avg_write_ms],
       CAST(vfs.[size_on_disk_bytes] / 1048576.0 AS DECIMAL(12,1))      AS [size_mb],
       mf.[physical_name]
  FROM sys.dm_io_virtual_file_stats(2, 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]
 ORDER BY [avg_write_ms] DESC;

Compare tempdb against the user databases on the same volume, because that separates “this storage is slow” from “tempdb specifically is being hammered”:

SELECT DB_NAME(vfs.[database_id])                                      AS [database_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],
       CAST(vfs.[io_stall_write_ms] * 1.0 / NULLIF(vfs.[num_of_writes], 0) AS DECIMAL(10,1)) AS [avg_write_ms],
       LEFT(mf.[physical_name], 3)                                     AS [drive]
  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 [drive], [avg_write_ms] DESC;

And find out whether it is latency or contention, which are different problems with different fixes:

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms] / 1000 AS [wait_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('PAGEIOLATCH_SH','PAGEIOLATCH_EX','PAGEIOLATCH_UP',
                       'PAGELATCH_SH','PAGELATCH_EX','PAGELATCH_UP','IO_COMPLETION','WRITELOG')
 ORDER BY [wait_time_ms] DESC;

PAGEIOLATCH is disk. PAGELATCH without the IO is allocation contention, which is the tempdb metadata problem and needs more files rather than faster disks.

How to fix it

Establish which of the three it is first, because they look identical in the finding and have nothing in common as fixes.

1. The storage is genuinely slow. If every database on that volume shows similar stalls, the volume is the problem.

• Move tempdb to the fastest storage available. Local NVMe is ideal and is legitimate even in a failover cluster, because tempdb does not need to survive a failover. This is usually the single biggest win available on an instance.
• Check the volume’s queue depth and alignment with whoever owns the storage.

2. Tempdb specifically is being hammered. If tempdb is slow and the user databases on the same volume are not, the volume is fine and tempdb is doing too much work.

• Find the queries generating tempdb load. The TempDB High Usage and TempDB Consumers reports answer this directly.
• The usual culprits are spills from bad memory grants, which come from bad cardinality estimates, which come from missing or stale statistics. Several checks on this report cover that chain.
• A long open transaction holding the version store open is the other common one.

3. It is allocation contention rather than latency. PAGELATCH waits on tempdb pages 1:1, 1:2 and 1:3 mean sessions are queuing on the allocation bitmaps, not on the disk.

• Add data files, up to one per logical processor to a maximum of eight.
• Size them all the same, with the same growth increment. Both have their own checks.

Then size tempdb properly, so it is not autogrowing under load, which adds latency of its own at exactly the wrong moment.

How long it takes

About four hours to separate the three causes and measure a change. Moving tempdb to different storage needs a restart and therefore a window.

Related reports

Related checks

Frequently asked questions

The numbers are averages since startup. Is that useful? As a screen, yes. To see whether it is happening now, snapshot the DMV, wait a few minutes, snapshot again and compare the deltas. The Disk Latency report does this for you over time.

Our SAN team says latency is under 1 ms. They are measuring at the array. SQL Server measures the whole path: HBA, switch, multipath driver, file system and array. A large difference between the two is itself the finding.

Tempdb is on the same drive as everything else. Then compare its stalls with the other databases there. If they match, the drive is the issue; if tempdb is worse, the workload is.

Is 20 ms really bad? For tempdb, yes. It is shared by every session, so its latency is multiplied by everything happening on the instance.