SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 13. Other Notable Wait Types

This rather short chapter covers several wait types I have not discussed yet. I will start with the ASYNC_NETWORK_IO wait type, which occurs when the client does not consume data from SQL Server fast enough. Next, I will talk about the THREADPOOL wait and the dangerous condition of worker thread starvation. After that, I will address backup-related wait types and ways to improve backup performance.

I will conclude the chapter with an overview of OLEDB and a few other preemptive wait types that occur when SQL Server calls OS API switching to preemptive execution mode.

ASYNC_NETWORK_IO Waits

ASYNC_NETWORK_IO is a common wait type I see in nearly every system. Inexperienced engineers usually guess by the wait type name, associating ASYNC_NETWORK_IO with bad network performance. This wait, however, indicates a much broader condition that occurs when SQL Server has to wait for the client application to consume data.

Slow networks can definitely trigger that condition, but more often than not, the cause is inefficient client application design. If the application reads and processes data row by row, this forces SQL Server to wait during processing.

Listing 13-1 shows the code pattern that will trigger the issue. The client application consumes and processes rows one by one, keeping SqlDataReader open. The SQL Server worker waits for the client to consume all the rows and generates the ASYNC_NETWORK_IO wait in the meantime.

Listing 13-1. Code pattern ...