BPSORT is recorded when a thread participates in a batch-mode sort, the columnstore-adjacent sort implementation (batch mode required columnstore involvement before SQL Server 2019; since then batch mode on rowstore widened the audience). Threads cooperating on a batch sort synchronise through this wait.
Some of it is the ordinary sound of batch-mode sorts running.
Is It a Problem?
Mostly no, with one documented exception worth knowing cold: on SQL Server 2016-era builds, a batch-mode sort could hit an inefficiency so severe it resembled a non-yielding scheduler, a query that simply never completes. Microsoft shipped trace flag 9358 to disable batch-mode sorts for exactly this, and anecdotal evidence kept the flag relevant even after early CUs.
So: routine BPSORT accompanying big columnstore or batch-mode queries is fine; a specific query that “never finishes” while showing batch sort activity is the pathological case.
Common Causes
- Normal batch-mode sorts in columnstore and (2019+) batch-mode-on-rowstore plans.
- The 2016-era batch sort inefficiency on affected builds and query shapes.
- Very large sorts under tight memory grants stretching the coordination.
What To Do
- For a stuck query showing batch sort behaviour: capture the plan, check build currency, and test trace flag 9358 (or a query-level workaround that avoids the batch sort) to confirm the diagnosis.
- For routine heaviness, treat it like other big-sort problems: grant sizing via fresh statistics, and indexes that provide order where feasible.
- Keep current on CUs; batch-mode execution has been steadily fixed and improved since 2016.
How To See It
Rank it against everything else with Get-WaitStatistics, and inspect specific plans when one query dominates the wait.
Part of the SQL Server Wait Types Library.
Related deep dive: CXPACKET and CXCONSUMER Wait Types.
Leave a Reply