To reduce IO waits in SQL Server, first name the wait. This post explains the main IO wait types and measures two of them for a single session on a demo database. It then shows how to check file latency and which fix matches each wait.
Diagnosing Blocking Chains With sys.dm_exec_requests
Trace blocking chains from active requests to a candidate head session, inspect sleeping transactions, and verify the owner before action.
Backup to URL Hangs on PREEMPTIVE_HTTP_EVENT_WAIT in an Azure VM
Backup to URL hangs on PREEMPTIVE_HTTP_EVENT_WAIT when an outbound rule denies internet access on an Azure virtual machine. I saw it after blocking the internet on my own VM. An outbound rule that allows the Storage destination fixed it.
AlwaysOn Queries Stuck on HADR_AR_CRITICAL_SECTION_ENTRY Wait
AlwaysOn queries stuck on HADR_AR_CRITICAL_SECTION_ENTRY followed a short loss of cluster quorum at a client site. Every change to the availability group waited. Setting failover to manual and restarting both replicas cleared the wait.
PREEMPTIVE_OS_DEVICEIOCONTROL: DBCC CHECKDB Will Not Stop
A DBCC CHECKDB session waiting on PREEMPTIVE_OS_DEVICEIOCONTROL did not end, even after a stop job and a kill. The wait is outside SQL Server’s control, so nothing inside SQL Server could clear it. Restarting SQL Server removed the session.
Decoding wait_resource: From a KEY or PAGE Lock to the Table
Decode wait_resource for blocked requests, map KEY and PAGE locks to objects, and keep undocumented row-hash checks on test copies.
SQL SERVER – Many BACKGROUND Threads with Command VDI_CLIENT_WORKER
Seeing many BACKGROUND threads with command VDI_CLIENT_WORKER? Here is what the VDI_CLIENT_WORKER and VDI_CLIENT_OTHER wait types mean.