PAGEIOLATCH or WRITELOG waits suggest an I/O path deserves attention, but they do not prove the disk is slow at this moment. Pending I/O requests show operations SQL Server has issued and not fully processed. Join them to file statistics and sample repeatedly before escalating to storage.
Distinguish Waits From Pending I/O Requests
PAGEIOLATCH waits can reflect reads that need pages from storage. WRITELOG can reflect waiting for log flushes. Both can have causes beyond a permanently slow disk, including workload bursts, insufficient memory, or a saturated path. Server wait totals are cumulative; a live pending-I/O view shows work still outstanding when you query it.
I start by noting when users saw the slowdown and which database or application was affected. Is the same file repeatedly present in several samples? One snapshot of one long request is a lead, not a storage verdict.
Map Handles to Database Files
sys.dm_io_pending_io_requests has the request's io_handle, type, pending state, and path. sys.dm_io_virtual_file_stats has file_handle plus database and file IDs. Joining those handles identifies a known SQL Server file; sys.master_files adds its physical path and type. Use a LEFT JOIN because some pending I/O does not map neatly to a database file.
SELECT DB_NAME(v.database_id) AS database_name,
mf.name AS logical_file, mf.type_desc,
COALESCE(mf.physical_name, p.io_handle_path) AS path,
p.io_type, p.io_pending,
p.io_pending_ms_ticks,
p.io_completion_request_address
FROM sys.dm_io_pending_io_requests AS p
LEFT JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS v
ON v.file_handle = p.io_handle
LEFT JOIN sys.master_files AS mf
ON mf.database_id = v.database_id
AND mf.file_id = v.file_id
ORDER BY p.io_pending_ms_ticks DESC;Microsoft labels io_pending_ms_ticks internal-use-only. It can help rank a snapshot, but do not present it as a guaranteed contractual elapsed-time field. io_pending = 1 means pending in the operating system; zero can mean the OS completed it but SQL Server has not yet processed the completion. Keep that distinction in the incident note.
Sample Pending I/O Requests Every Few Seconds
A single request can finish before the next query starts. Create a small temporary sample table, collect several snapshots, and timestamp each one. This loop takes six samples five seconds apart. It stores request address, file identity, type, pending state, and the internal tick field for context. Run it only during a bounded investigation.
CREATE TABLE #PendingSample
(
sampled_at datetime2(3),
request_address varbinary(8),
database_id int NULL,
file_id int NULL,
io_type nvarchar(60),
io_pending int,
pending_ticks bigint
);
DECLARE @sample int = 0;
WHILE @sample < 6
BEGIN
INSERT #PendingSample
SELECT SYSUTCDATETIME(),
p.io_completion_request_address,
v.database_id, v.file_id,
p.io_type, p.io_pending, p.io_pending_ms_ticks
FROM sys.dm_io_pending_io_requests AS p
LEFT JOIN sys.dm_io_virtual_file_stats(NULL,NULL) AS v
ON v.file_handle = p.io_handle;
SET @sample += 1;
IF @sample < 6 WAITFOR DELAY '00:00:05';
END;
SELECT DB_NAME(database_id) AS database_name,
file_id, io_type, COUNT(*) AS observed_samples,
MIN(sampled_at) AS first_seen,
MAX(sampled_at) AS last_seen,
DATEDIFF_BIG(millisecond, MIN(sampled_at),
MAX(sampled_at)) AS observed_span_ms
FROM #PendingSample
GROUP BY request_address, database_id, file_id, io_type
ORDER BY observed_span_ms DESC;A request seen in two samples was present across at least part of that interval, but observation span is not the exact device service time. Request addresses can be reused, so keep the sampling window short and correlate with file and type. A missing row can mean the request completed between samples.
Compare With File-Level Delays
sys.dm_io_virtual_file_stats exposes cumulative reads, writes, and stall milliseconds per file. Take two samples over the same incident window to calculate average read or write stall per operation. Do not divide a lifetime stall total by today's request count. Compare with pending-I/O samples and the storage team's latency metrics.
SELECT DB_NAME(v.database_id) AS database_name,
mf.name, mf.type_desc,
v.num_of_reads, v.io_stall_read_ms,
v.num_of_writes, v.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
JOIN sys.master_files AS mf
ON mf.database_id = v.database_id
AND mf.file_id = v.file_id;A log file with rising write stalls and recurring WRITELOG waits is a stronger lead than one wait chart alone. Data files with long pending reads during PAGEIOLATCH complaints deserve an OS and storage view. SQL Server cannot by itself tell whether the delay came from a controller, virtual host, network storage, or competing workload.
Give the Storage Team an Actionable Packet
Include UTC timestamps, server and instance, database, logical and physical file, I/O type, number of pending observations, observed spans, file-stat deltas, and user-facing latency. Add volume mapping and any growth or backup activity in the same interval. Explain that the pending tick value is internal and the span is sampled lower-bound evidence.
I ask the storage team to compare those timestamps with device queue depth, latency percentiles, throttling, and neighboring workloads. If their data shows low device latency, return to SQL Server's query pattern and memory pressure rather than insisting the disk must be the cause. The goal is a shared timeline, not a blame assignment.
Separate File Latency From Query Volume
An increased number of pending requests can mean the storage service time rose, or simply that SQL Server issued far more I/O at the same service time. Pair request counts with bytes read and written, file-stat stall deltas, and business volume. If an index was dropped and a query now scans ten times as many pages, the storage layer can look busy while responding normally to each request. Conversely, unchanged I/O volume with sharply higher latency supports a storage-path investigation.
Do not average read and write stalls into one file score. Log files are write-heavy and data files can have very different read patterns. Compare like operations and the same file across similar periods. A few very long requests can also hide inside an acceptable average; use percentiles from OS or storage telemetry when available.
Check the State of Pending I/O Requests
The io_pending value distinguishes work still pending in the operating system from a completion that SQL Server has not yet processed. If many requests are in the latter state, examine scheduler pressure and CPU as well as the device. A path name from io_handle_path can be missing or truncated, so use the file-handle join when possible and keep unknown mappings in the output rather than discarding them.
The internal tick field is tempting because it looks like a duration. I label it as internal and use repeated observations to make a supported statement. A typical one says the request was visible in three samples over ten seconds. That is less precise but more honest. The storage team can supply actual device service time for the same interval.
Bound the Collection
A five-minute continuous collector is more useful during an incident than a one-shot screenshot, but it should not run indefinitely in every session. Limit rows, interval, and retention. Save UTC timestamps and engine start time so counters reset cleanly after a restart. Protect query text and file paths in shared reports. Once the slowdown ends, stop the collector and summarize the evidence instead of leaving a polling job consuming attention and msdb space.
Related reading on this blog: Script to List Database File Latency and CPU Scheduler Waiting On Disk.
A lifetime I/O wait total is not a current incident, it is a clue to test with repeated samples.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
