When many worker processes are returning query results, you may see blocking between worker processes. This example shows five worker processes blocking on the sixth worker process:
sp_who 11
fid spid status loginame origname hostname blk_spid dbname tempdbname cmd block_xloid threadpool --- ---- ----------- --------- --------- --------------- -------- ----------- ----------- ----------------- ----------- ----------------- 11 11 sleeping diana diana olympus 0 sales tempdb SELECT 0 syb_default_pool 11 16 lock sleep diana diana olympus 18 sales tempdb WORKER PROCESS 0 syb_default_pool 11 17 lock sleep diana diana olympus 18 sales tempdb WORKER PROCESS 0 syb_default_pool 11 18 send sleep diana diana olympus 0 sales tempdb WORKER PROCESS 0 syb_default_pool 11 19 lock sleep diana diana olympus 18 sales tempdb WORKER PROCESS 0 syb_default_pool 11 20 lock sleep diana diana olympus 18 sales tempdb WORKER PROCESS 0 syb_default_pool 11 21 lock sleep diana diana olympus 18 sales tempdb WORKER PROCESS 0 syb_default_pool
Each worker process acquires an exclusive address lock on the network buffer while writing results to it. When the buffer is full, it is sent to the client, and the lock is held until the network write completes.
