Common Query Plan Patterns For Joins: DOP and Bitmaps


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
triple x


When bitmaps do their job, you can tell. For example, here’s an example of an effective bitmap:

SQL Server Query Plan

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.


When they don’t, you can also tell. This bitmap hardly eliminates any rows at all.

SQL Server Query Plan
down down down

But wait! This query runs at DOP 4. You can tell by looking at the number of executions.

Who runs queries at DOP 4?



At higher DOPs, that useless bitmap becomes much more effective.

SQL Server Query Plan

At DOP 8, we filter out about 600k rows, and at DOP 16 we filter out about 830k rows.


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 on 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.