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 dbname cmd --- ---- ---------- -------- -------- -------- --- ------ -------------- 11 11 sleeping diana diana olympus 0 sales SELECT 11 16 lock sleep diana diana olympus 18 sales WORKER PROCESS 11 17 lock sleep diana diana olympus 18 sales WORKER PROCESS 11 18 send sleep diana diana olympus 0 sales WORKER PROCESS 11 19 lock sleep diana diana olympus 18 sales WORKER PROCESS 11 20 lock sleep diana diana olympus 18 sales WORKER PROCESS 11 21 lock sleep diana diana olympus 18 sales WORKER PROCESS
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.
