Useful vs Useless Bitmaps In SQL Server Query Plans

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.

SQL Server Query Plan

In the outer (top) branch of the plan, a Bitmap is created. It gets applied at the Scan of the Users table.

SQL Server Query Plan Tool Tip
Louder than Bits

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.

SQL Server Query Plan
Participation Ribbon

Helpful Bitmaps

In a helpful Bitmap plan, the details look much different.

SQL Server Query Plan

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.

SQL Server Query Plan Tool Tip
Where else can I go?
  • 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 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.