Error 833: I/O Requests Taking Longer Than 15 Seconds on Your Storage

Fix it now

SQL Server has issued reads or writes that took longer than fifteen seconds to come back. It is informational and the instance keeps running, but fifteen seconds is a stall, not a delay, and the fault is below the database.

SELECT DB_NAME(vfs.database_id) AS db, mf.physical_name,
       vfs.num_of_reads, vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads,0)  AS avg_read_ms,
       vfs.num_of_writes, vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes,0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_write_ms DESC;

1. Read the error log entries and note which file and which database each names. Data files, the log and tempdb sit on different parts of the storage path.
2. Compare the figures above with the PhysicalDisk counters for average seconds per read and per write, so you know whether Windows sees the same delay.
3. Check the Windows system event log for disk and storage driver events at the same timestamps. Reset and retry entries put the fault below SQL Server.
4. Exclude SQL Server data files from real-time antivirus scanning. Microsoft names filter drivers, including antivirus, as a documented cause and the exclusion as a documented action.
5. Check whether a backup, snapshot or replication job runs at those times. Correlating timestamps usually names the culprit in one step.

Microsoft’s own guidance for 833 starts with the system event log and hardware logs, then driver and firmware levels, then the antivirus exclusion. Work in that order before touching the database.

If the stalls stop when the overlapping job or the scanner is dealt with, you are done. If they do not, the next section covers what 833 measures and how 845, 17883 and 17884 fit around it.

Why it happens

SQL Server tracks how long each outstanding I/O has been pending. When a request against a file has been waiting fifteen seconds or longer, it writes a message naming the file, the database, how many such occurrences it has seen, the OS file handle and the offset of the most recent long I/O. That threshold is deliberately generous. Fifteen seconds is not a marginal delay; by the time you see 833, latency has been poor for a while and only the very worst requests are being reported.

Microsoft’s own cause list for this message is wider than most people expect: faulty hardware, hardware configured incorrectly, firmware settings, filter drivers such as antivirus, compression, software bugs, a workload that exceeds what the I/O path can carry, and operating system performance problems. Note what is not on it – a shortage of database memory. Adding RAM reduces physical reads, which reduces the number of stalls you see, but it does nothing for log writes, checkpoints or backups, all of which still have to reach the disk.

845 sits one layer up. It is Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d, and it usually means a task waited for a page that was still coming off disk. 17883 is the scheduler monitor reporting a worker that appears to be non-yielding, with kernel and user CPU times and the system idle percentage in the message. 17884 is its sibling, SRV_SCHEDULER_DEADLOCK: new queries assigned to a node have not been picked up by any worker thread for some seconds. Slow storage produces all four, because workers blocked on I/O stop behaving like workers.

Full reference

Where the delay usually comes from

The four messages and what each one is telling you

Microsoft’s guidance on 845 includes a line worth repeating: if the errors are infrequent, they can generally be ignored. It is repetition and clustering that make these worth an incident, not a single entry after a busy night.

Configuration options Microsoft names for 845

• Priority boost – leave it off.
• Lightweight pooling, the fibre mode option – leave it off.
• Set working set size – leave it off.
• Autoshrink – off, so the database is not repeatedly resizing itself.
• Autogrow – configured with large increments so growth happens rarely.
• Compressed volumes – unsupported for data and log files; move them.

Instant file initialization, and its one trade-off

Granting the Database Engine service account the Perform volume maintenance tasks right lets data file creation, growth, resize and restore skip the zeroing pass. The documented trade-off is that deleted disk content is overwritten only as new data is written, so until it is, that content is potentially readable by anyone who can get at the file – which in practice means detached files, backups without restrictive permissions, and storage handed back to a shared array. Microsoft’s own position is that the benefits usually outweigh the risk; the mitigation is restrictive permissions on detached files and backups. Note also that transparent data encryption prevents instant file initialization for data files.

What to do once the storage is stable again

Persistent I/O stalls can leave torn or damaged pages behind. When the storage is healthy again, run DBCC CHECKDB on the affected databases and confirm your most recent backups restore cleanly on another server before you treat the incident as closed.

Reading the scheduler messages

17883 carries kernel time, user time, process utilisation and system idle. Microsoft’s own reading of those numbers is worth borrowing: user mode climbing fast points at a loop inside the engine, kernel mode climbing fast points at the operating system, and both flat with process utilisation high points at preemptive work such as garbage collection. Low process utilisation and low system idle together mean SQL Server is not getting enough CPU at all, which is a host-level problem rather than a storage one.

Every code this article covers

Confirm the fix worked

1. Average stall per file from sys.dm_io_virtual_file_stats, re-measured after a full business day, has fallen.
2. No new 833, 845, 17883 or 17884 entries appear in the error log across a peak period.
3. The Windows system log shows no storage retry or reset events during the same window.
4. DBCC CHECKDB on the affected databases reports no errors.
5. A recent backup has been restored on another server and comes up clean.

Questions people ask about this