1/10
Bitmaps can be really useful in parallel hash and merge join plans. They can be used like sargable, but not seekable, predicates.
Where they get created and where they get used is a bit different,
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2021/04/2021-04-01_19-45-56.jpg)
10/10
When bitmaps do their job, you can tell. For example, here’s an example of an effective bitmap:
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2021/04/2021-04-01_18-47-11.jpg)
At the index scan, we filter out all but around ~40k rows from the Users table.
That’s uh… Eh you can find a percentage calculator.
0/10
When they don’t, you can also tell. This bitmap hardly eliminates any rows at all.
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2021/04/2021-04-01_19-29-55.jpg)
But wait! This query runs at DOP 4. You can tell by looking at the number of executions.
Who runs queries at DOP 4?
Fools.
40/40
At higher DOPs, that useless bitmap becomes much more effective.
![SQL Server Query Plan](https://erikdarling.com/wp-content/uploads/2021/04/2021-04-01_19-34-06.jpg)
At DOP 8, we filter out about 600k rows, and at DOP 16 we filter out about 830k rows.
99/1
Like many operators in query plans, Bitmaps aren’t parallel “aware”, meaning there will be one Bitmap per thread.
At times, if you find a Bitmap underperforming at a lower DOP, you may find some benefit from increasing it.
Of course, you may also find general relief from more threads working on other operators as well. Sometimes more CPU really is the answer to queries that process a whole bunch of rows.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
One thought on “Common Query Plan Patterns For Joins: DOP and Bitmaps”
Comments are closed.