Does ASYNC_NETWORK_IO wait types result in thread locking until entire result is consumed by client application?

[jonetta]

ASYNC_NETWORK_IO wait types are mostly caused because the client application cannot process the data that arrives from SQL Server fast enough.

As the query executes, and as the results start getting available for consumption, the client application starts consuming the results. Assuming the result is held by say 4 threads, then, until the result is entirely consumed by the client application, do the threads that hold the data remain locked?

[simrah]

Assuming the result is held by say 4 threads, then, until the result is entirely consumed by the client application, do the threads that hold the data remain locked?

Not necessarily; it depends on the execution plan shape.

Result rows are assembled into packets for transmission to the client by the worker thread associated with the parent task. This thread will become blocked and wait on ASYNC_NETWORK_IO if the client is slow to consume results. Only the parent task's worker returns rows to the client.

Additional parallel worker threads active in other areas of the plan run independently, and have no direct knowledge of the state of the parent task. They can, generally, continue to execute even if the parent task's worker is waiting.

That said, these parallel workers will eventually produce rows that need to be consumed by the parent task. When exchange buffers become full, the parallel workers will also experience waits like CXCONSUMER or CXPACKET until the parent task's worker resumes and reads rows from the exchange.

For more background on the parent task and how parallel execution works see my articles:

• https://www.sql.kiwi/2020/07/how-maxdop-really-works.html
• https://sqlperformance.com/2013/10/sql-plan/parallel-plans-branches-threads
• https://sqlperformance.com/2021/03/sql-performance/parallel-plans-start-1