Common Query Plan Patterns For Joins: DOP and Bitmaps

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,

10/10

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

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.

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.

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.