Category: Wait Types
-
PAGEIOLATCH_SH Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Storage & Capacity Part of the SQL Server Wait Types Library. PAGEIOLATCH_SH appears when a query needs a data page that is not in the buffer pool, so SQL Server must read it from disk. The SH stands for shared latch, the…
Written by
-
ASYNC_NETWORK_IO Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library. ASYNC_NETWORK_IO appears when SQL Server has finished producing rows and is waiting for the client application to read them from the network buffer. SQL Server filled its output buffer, the client…
Written by
-
PAGELATCH_EX Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library. PAGELATCH_EX (and its sibling PAGELATCH_UP) is a latch wait on a page that is already in memory. That one word, memory, is the whole story. This is not disk I/O. If…
Written by
-
AM_INDBUILD_ALLOCATION and AM_SCHEMAMGR_UNSHARED_CACHE Wait Types in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library.Related deep dive: IO_COMPLETION Wait Type. Two Access Methods (AM) waits share this page: AM_INDBUILD_ALLOCATION: a thread waiting for exclusive access to an extent during allocations while building or rebuilding indexes. Parallel…
Written by
-
WAITFOR Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library.Related deep dive: SOS_SCHEDULER_YIELD Wait Type. WAITFOR is the most literal wait type in the DMV: it records sessions executing a WAITFOR DELAY or WAITFOR TIME statement. The duration is exactly what…
Written by
-
BACKUP Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Backup & Recovery Part of the SQL Server Wait Types Library.Related deep dive: BACKUPIO and BACKUPBUFFER Wait Types. BACKUP is recorded when a thread involved in backup processing waits to synchronise with the other threads of the same backup. Backups run as…
Written by
-
BACKUPTHREAD Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Backup & Recovery Part of the SQL Server Wait Types Library.Related deep dive: BACKUPIO and BACKUPBUFFER Wait Types. BACKUPTHREAD is recorded when a thread involved in a backup or restore waits for other threads in the same operation to finish their part.…
Written by
-
BAD_PAGE_PROCESS Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library.Related deep dive: PAGEIOLATCH_SH Wait Type. BAD_PAGE_PROCESS is recorded by the background suspect page logger, the process that records 823 and 824 I/O errors into msdb.dbo.suspect_pages, as it throttles itself to run…
Written by
-
BROKER_TASK_STOP Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library.Related deep dive: SOS_SCHEDULER_YIELD Wait Type. BROKER_TASK_STOP is recorded when a Service Broker internal task handler waits (up to ten seconds at a time) to shut itself down because there are no…
Written by
-
BROKER_TO_FLUSH Wait Type in SQL Server
⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting Part of the SQL Server Wait Types Library.Related deep dive: SOS_SCHEDULER_YIELD Wait Type. BROKER_TO_FLUSH is recorded by the Service Broker lazy flusher, the background thread that writes inactive transmission objects (the structures tracking message transmissions per dialog) out to…
Written by
Start Here
- DBA Scripts: The Complete Guide
- Run a Full Health Check
- DBA Scripts & Tools
- SSMS: The Complete Guide
- Wait Types Library
The Pillars
Blog Categories
- Backups & Recovery (5)
- DBA Scripts (147)
- High Availability (HA) (7)
- Installation & Configuration (16)
- Maintenance (4)
- Migration & Upgrades (1)
- Monitoring (6)
- Performance Tuning (5)
- Security (Encryption & Permissions) (3)
- T-SQL Fundamentals (11)
- Troubleshooting (23)
- Wait Types (235)
Latest Posts
- Compatibility Levels: What Actually Changes When You Bump It
- SQL Server Management Studio (SSMS): The Complete Guide
- SQL Server Collation: Case Sensitivity and the Cross-Database Trap
- SQL Server Isolation Levels (and When to Reach for RCSI)
- DBA Scripts: Get Database Summary
- DBA Scripts: Get Database Inventory
Blog Tags
Access (4) Always On Availability Groups (4) Auditing (7) Backups (6) Blocking (4) Blocking Locking (6) Collectors (7) Database Backups & Recovery (8) Database Backups and Recovery (5) Database Maintenance (7) DBA Tools (24) Disk Space (5) Extended Events (8) Features (6) High Availability (6) Indexes (7) Installation (7) Inventory (8) Jobs (3) Maintenance (7) Migration (15) Monitoring (27) Performance (21) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (5) Security (10) SQL Agent (5) SQL Scripts (17) SQL Server Administration (10) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (3) SQL Server Management Studio (SSMS) (6) SQL Server Monitoring (7) SQL Server Networking (9) SQL Server Security (7) Storage and Capacity (7) T-SQL (10) Temp DB (3) Tracing (7) Transaction Logs (4) Troubleshooting (24) Windows Server (4)