It’s Hot Out There
Paul white (b|t) did what I think he does best: make a casual, off-hand remark about something mystifying with such absolute certainty that it makes your brain halt. At least that’s what happens to me.
It all started with:
“The Bitmap is hopeless.”
Earth Shattering Kaboom
I’d never considered Bitmaps in excruciating detail.
Sorta Bloom Filter-y. They show up in some parallel Hash and Merge Join plans (simplifying a bit because they’re hidden in serial Hash Join plans).
They seem nice. Early row reductions. Never thought of one as “hopeless”.
But then!
Hope Bloats
Let’s take a look at a plan with a hopeless Bitmap.
In the outer (top) branch of the plan, a Bitmap is created. It gets applied at the Scan of the Users table.
What makes it hopeless?
- The Users table has 2,465,710 rows in it
- Despite the Bitmap, we read 2,465,593 rows and
- We pass 2,465,590 of those rows along to the Repartition Streams
In other words, the Bitmap barely filtered out any rows whatsoever. Did it hurt performance? Am I mad at Bitmaps? No and no.
At least not here.
Beware Bitmap Placement
In some query plans, the Bitmap may not make it all the way down to the Scan operator.
If there’s a Partial Aggregate after the Scan, you may find the Bitmap applied at the Repartition Streams.
Better late than never, I suppose.
Helpful Bitmaps
In a helpful Bitmap plan, the details look much different.
Visually, this plan looks much different than the Hopeless Bitmap plan.
The number of rows (39,789) read from the scan is much lower than the table cardinality (2,465,710).
The details of the scan are also interesting.
- We did not have to read all 2,465,710 rows
- We only had to read 83,144 of them
- We were able to Bitmap out ~50% of them, down to 39,789
That’s a Bangin’ Bitmap.
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.