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,

triple x

10/10


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

impressionable

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.

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?

Fools.

40/40


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

OBSERVE IF YOU WILL

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!



One thought on “Common Query Plan Patterns For Joins: DOP and Bitmaps

Leave a Reply

Your email address will not be published. Required fields are marked *