A join plan can discard unwanted fact rows before the join finishes matching them. Bitmap filters provide that early screening, and the actual plan shows where the screening took place.
Start With the Filtered Dimension
A reporting query joins a large fact table to a smaller dimension. The dimension filter selects a limited set of keys. Rows carrying other keys cannot contribute to the final result, so rejecting them early saves downstream work.
A bitmap is a compact representation of candidate key values. It helps reject rows that cannot match. The hash join still checks the surviving rows, because passing the bitmap is not the final proof of a join match.
I read the dimension predicate before interpreting the bitmap. A filter selecting almost every dimension key provides a different opportunity from a filter selecting a small subset. The icon alone does not describe the benefit.
In parallel row mode plans, look for the Bitmap operator around a hash join and its probe side. Batch mode plans can use bitmap filtering too, with plan presentation differing by execution mode. Inspect the properties instead of requiring one identical picture.
The optimizer chooses whether this filtering is useful. You cannot request a bitmap directly through a normal query option. Parallelism, join shape, estimated selectivity, and available access paths all affect the decision.
Create a Fact Table With a Selective Join
Use a disposable database for this demonstration. Its data generator requires SQL Server 2022 or later and database compatibility level 160 or higher. Confirm that setting before running GENERATE_SERIES.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
CREATE TABLE dbo.BitmapDimension
(
DimensionID int NOT NULL PRIMARY KEY,
RegionCode int NOT NULL
);
CREATE TABLE dbo.BitmapFact
(
FactID int NOT NULL PRIMARY KEY,
DimensionID int NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT dbo.BitmapDimension
SELECT value, value % 20
FROM GENERATE_SERIES(1, 1000);
INSERT dbo.BitmapFact
SELECT value, 1 + value % 1000,
CONVERT(decimal(12,2), value % 250)
FROM GENERATE_SERIES(1, 1000000);
UPDATE STATISTICS dbo.BitmapDimension WITH FULLSCAN;
UPDATE STATISTICS dbo.BitmapFact WITH FULLSCAN;The row limits describe requested sample input. They are not observed counts from a tested instance. Increase or reduce the setup only within the capacity of your test environment.
The fact table intentionally lacks a separate index on DimensionID. That makes scanning a plausible choice for the aggregate. Production tables can offer different choices, so do not treat this setup as an indexing recommendation.
Locate Bitmap Filters and Their Probe
Enable the actual execution plan in SSMS and run the aggregate. MAXDOP 4 sets a ceiling for this query. It does not force the optimizer to choose parallel execution or create a bitmap.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(f.Amount) AS TotalAmount
FROM dbo.BitmapFact AS f
JOIN dbo.BitmapDimension AS d
ON d.DimensionID = f.DimensionID
WHERE d.RegionCode = 3
OPTION (MAXDOP 4);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Find the filtered dimension input and the Hash Match join. Then inspect any Bitmap operator and its defined value. The plan assigns an internal bitmap name that connects construction with probing.
Look at the fact scan's predicate properties for the corresponding PROBE expression. The filter can be pushed toward the scan or applied elsewhere in the parallel plan. Follow that expression rather than assuming the icon's location is the whole story.
For integer keys, an in-row probe can perform early screening during the scan. Other plans apply the probe at another stage. Read the actual expression and execution mode before describing where rows disappeared.
On my SQL Server 2025 test instance, the filtered query ran in batch mode with no separate Bitmap operator. The fact scan carried the PROBE predicate itself. It read all 1,000,000 rows and passed 50,000 to the join, which matches the 50 selected dimension keys.
If no bitmap appears, inspect the chosen join, parallel exchanges, and estimates. A serial plan or a different join strategy does not prove an engine defect. It establishes that your instance selected another valid plan.
Compare Scan Output With a Broader Query
Run a second aggregate without the dimension filter. Keep the tables, connection settings, and maximum degree of parallelism unchanged. Capture both actual plans and the messages containing reads and timing.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(f.Amount) AS TotalAmount
FROM dbo.BitmapFact AS f
JOIN dbo.BitmapDimension AS d
ON d.DimensionID = f.DimensionID
OPTION (MAXDOP 4);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Compare Actual Number of Rows leaving the fact scan with the broader query's scan output. Also compare rows read where that property is available. Those values answer different questions when a predicate rejects rows during scanning.
A bitmap does not guarantee fewer data pages read. A full scan can still read the same pages while delivering fewer rows to exchanges or the join. That downstream reduction can be useful even when logical reads remain similar.
The two queries return different business results. Their comparison demonstrates selectivity, not interchangeable implementations of one answer. Do not declare a performance win by comparing totals that were supposed to include different rows.
Read Parallel Work Alongside Bitmap Filters
Expand per-thread runtime details where the plan viewer provides them. Compare rows across parallel scan, exchange, and hash join workers. Uneven distribution can limit the benefit even when the bitmap rejects substantial input.
Check estimated and actual rows on the dimension filter. Poor estimates can influence the chosen join and memory grant. The bitmap is one part of that plan, not a replacement for accurate statistics.
I check the exchange traffic before attributing every improvement to page reads. Early filtering can reduce rows crossing a parallel exchange. That saves work in a different place from an index seek.
Which operator still handles most of the surviving rows? Inspect spills, residual predicates, and the final aggregate. A selective bitmap does not correct an expensive remaining expression or an undersized memory grant.
Treat the Bitmap as a Plan Decision
Keep the selective and broad plans with the same capture details. Record query text, input values, compatibility level, and collection time. Plans from different settings do not form a clean comparison.
Do not change server-wide parallelism because one bitmap looks promising. Rehearse query-level changes against representative workload. The optimizer's current choice depends on costs and data distribution, not a universal preference for this operator.
When the production query scans heavily, consider the whole access path. A suitable index, a different report shape, or improved statistics can change where filtering happens. Preserve the query's meaning while testing those alternatives.
Read operator execution counts alongside row totals. A repeatedly executed input needs different interpretation from one scan producing the same total rows. Also check whether the viewer displays totals or per-execution averages for a property. Keep that distinction consistent across both plans. Otherwise, a change in loop execution can look like bitmap filtering even when the comparison is measuring different amounts of work.
Bitmap filters deserve attention when they remove rows before expensive downstream work. Compare the complete plan because bitmap filters do not make an unsuitable join strategy correct.
Related reading on this blog: NonParallelPlanReason: Why a Query Refuses to Go Parallel and Number of Rows Read Per Threads in Parallel Operations.
A bitmap filter is not the final join answer, it is an early chance to stop carrying rows that cannot contribute.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
