Author: Peter Whyte

  • ASYNC_NETWORK_IO Wait Type in SQL Server

    ⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting 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 hasn’t acknowledged it yet, and SQL Server is…

    Written by

    Peter Whyte

  • PAGELATCH_EX Wait Type in SQL Server

    ⏳Part of the SQL Server Wait Types Library, every wait type explained.Related pillar: Performance & Troubleshooting 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 you see PAGE**IO**LATCH you have a storage problem;…

    Written by

    Peter Whyte

  • DBA Scripts: Get Job Schedules and Duration Trends

    ,

    🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Maintenance & Automation › SQL Agent & Jobs When Jobs Run, and Whether They’re Taking Longer to Do It SQL Agent Job Failure Summary tells you what broke. These two scripts answer two quieter but just as important questions: Get-JobScheduleSummary shows exactly when…

    Written by

    Peter Whyte

  • How to Shrink SQL Server Database Files in Chunks

    🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Storage & Capacity A data file that’s grown far past what the database actually needs, after a one-off bulk load, an archive cleanup, a migration that left behind a much bigger footprint than the data now occupies, eventually needs to come back down.…

    Written by

    Peter Whyte

  • DBA Scripts: Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan

    ,

    🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Performance & Troubleshooting › Blocking & Locking Three Different Views of the Same Underlying Problem Blocking Chains shows what’s blocked right now. These three scripts go wider and deeper: Get-LockEscalationStats finds which tables have converted row/page locks into full table locks (a common,…

    Written by

    Peter Whyte

  • 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 deep dive: IO_COMPLETION Wait TypeRelated pillar: Performance & Troubleshooting 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 index builds allocate space aggressively, and workers occasionally…

    Written by

    Peter Whyte

  • WAITFOR Wait Type in SQL Server

    ⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: SOS_SCHEDULER_YIELD Wait TypeRelated pillar: Performance & Troubleshooting 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 the T-SQL asked for. Nothing is contended and…

    Written by

    Peter Whyte

  • BACKUP Wait Type in SQL Server

    ⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Backup & Recovery BACKUP is recorded when a thread involved in backup processing waits to synchronise with the other threads of the same backup. Backups run as a multi-threaded pipeline (readers, writers, a coordinator), and…

    Written by

    Peter Whyte

  • BACKUPTHREAD Wait Type in SQL Server

    ⏳Part of the SQL Server Wait Types Library, every wait type explained.Related deep dive: BACKUPIO and BACKUPBUFFER Wait TypesRelated pillar: Backup & Recovery BACKUPTHREAD is recorded when a thread involved in a backup or restore waits for other threads in the same operation to finish their part. Backups are multi-threaded pipelines, readers pulling pages, writers…

    Written by

    Peter Whyte

  • DBA Scripts: Get Trace Flags and Resource Governor Configuration

    ,

    🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.In: Server & Configuration Two Things a Previous DBA Might Have Set and Never Told You Trace flags and Resource Governor are both instance-wide behavior changes that don’t show up in sp_configure at a glance, don’t appear in the database, and can sit silently…

    Written by

    Peter Whyte

Start Here

The Pillars

Blog Categories

Latest Posts

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) Error Log (3) Extended Events (8) Features (6) High Availability (6) Indexes (7) Installation (7) Inventory (8) Maintenance (6) Migration (14) 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)