SQL Server Advanced Troubleshooting and Performance Tuning

Appendix A. Wait Types

This appendix provides a list of the most common wait types you’ll encounter during troubleshooting. It outlines conditions when the waits may occur and gives you some tips on high-level troubleshooting strategy.

Do not consider this appendix to be a replacement for the Microsoft documentation or the SQLSkills Wait Types Library. Nevertheless, it should work as good reference material within the book.

ASYNC_IO_COMPLETION

The ASYNC_IO_COMPLETION wait occurs during asynchronous I/O operations not related to the buffer pool. Common cases include:

• Regular checkpoint

• Internal checkpoint when you start a database backup or run DBCC CHECKDB

• Reading GAM pages from data files

• Reading data pages from the database during a database backup (unfortunately, this tends to skew the average wait time, making it harder to analyze)

A noticeable percentage of ASYNC_IO_COMPLETION waits may indicate an overloaded I/O subsystem, especially when it is present with other I/O-related waits. Perform I/O system troubleshooting (see PAGEIOLATCH waits later in this appendix).

See Chapter 3 for additional details.

ASYNC_NETWORK_IO

The ASYNC_NETWORK_IO wait occurs when SQL Server waits for a client to consume data. Three common cases may trigger this wait:

• A slow network, often when clients are connected to the database server in another datacenter or in the cloud

• Client applications running in under-provisioned virtual machines (VMs) or on overloaded servers

• Incorrect ...