• Article

Interpreting Execution Plans Containing Bitmap Filters

Parallel query execution plans that use bitmap filtering have a Bitmap operator in one or more operator subtrees. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves.

In SQL Server 2008, bitmap filtering can be introduced in the parallel query plan after optimization, as in SQL Server 2005, or introduced dynamically by the query optimizer during query plan generation. When the filter is introduced dynamically, it is referred to as an optimized bitmap filter. A query plan can contain both bitmap filters and optimized bitmap filters. The query optimizer determines when a bitmap filter or optimized bitmap filter is selective enough to be useful and in which operators the filter is applied. For more information, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering. 

When analyzing an execution plan containing bitmap filtering, it is important to understand how the data flows through the plan and where filtering is applied. The bitmap filter and optimized bitmap is created on the build input (the dimension table) side of a hash join; however, the actual filtering is typically done within the Parallelism operator, which is on the probe input (the fact table) side of the hash join. However, when the bitmap filter is based on an integer column, the filter can be applied directly to the initial table or index scan operation rather than the Parallelism operator. This technique is called in-row optimization.

Viewing Bitmap Filters in Showplans

To view bitmap filters in the query plan, use the SET options SHOWPLAN_ALL, SHOWPLAN_TEXT, or SHOWPLAN_XML, or click Include Actual Execution Plan in SQL Server Management Studio.

If an XML Showplan is produced, the physical and logical bitmap operators are listed in the following way:

<RelOp NodeId="2" PhysicalOp="Bitmap" LogicalOp="Bitmap Create" EstimateRows="88" EstimateIO="0" EstimateCPU="0.0718125" AvgRowSize="6893" EstimatedTotalSubtreeCost="0.229385" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">

The operator in which a bitmap filter is applied contains the name of the bitmap in the Probe Column property.

The operator in which optimized bitmap filter is applied contains a bitmap predicate in the form of PROBE([Opt_Bitmap1001], {[column_name]} [, 'IN ROW']). The bitmap predicate reports on the following information:

  • The bitmap name that corresponds to the name introduced in the Bitmap Create operator. The prefix 'Opt_' indicates that an optimized bitmap filter is used.

  • The column probed against. This is the point from which the filtered data flows through the tree.

  • Whether the bitmap probe is performed in-row. When in-row optimization is used, the bitmap probe is invoked with the IN ROW parameter. Otherwise, this parameter is missing.

Example

The following example demonstrates how optimized bitmap filtering is used in an execution plan. The two dimension tables DimProduct and DimCustomer join to the fact table FactInternetSales using a primary-key-to-foreign-key join on a single integer column.

USE AdventureWorksDW2008R2;
GO
SELECT * 
FROM dbo.FactInternetSales AS F
INNER JOIN dbo.DimProduct AS D1 ON F.ProductKey = D1.ProductKey
INNER JOIN dbo.DimCustomer AS D2 ON F.CustomerKey = D2.CustomerKey
WHERE D1.StandardCost <= 30 AND D2.YearlyIncome <= 50000;

The following illustration shows that an optimized bitmap filter is created in the operator subtree of both dimension tables after the dimension table has been scanned and the information needed to eliminate non-qualifying rows from the fact table is known. The filter is then applied to the fact table in the earliest possible location, the Table Scan operator. The application of the filter is shown in the Predicate section of the Table Scan properties. The information shown in the Predicate indicates that both optimized bitmap filters Opt_Bitmap1008 and Opt_Bitmap1009 are used to limit the rows returned from the fact table. The columns probed against in the fact table are listed as [F].[CustomerKey] and [F].[ProductKey]. The IN ROW parameter is shown, indicating that in-row optimization is used in the process. If in-row optimization is not possible, the bitmap filtering is applied to the Parallelism operator.

SQL Server query plan with bitmap filters.

Based on the illustration, the following conclusions can be made:

  • Optimized bitmap filters are created in two subtrees.

  • Both filters are applied dynamically to a single (Table Scan) operator.

  • The optimized bitmap filter estimated to be the most selective is implemented first.

  • The columns on which the dimension tables are joined to the fact table allows the use of in-row optimization. That is, the join is based on a single integer column.

  • The filter is applied at the earliest possible point in the query, resulting in a reduced number of rows flowing from the Table Scan operation through the remaining operators in the tree.