Troubleshooting Bitmap Filter Selectivity Problems
Bitmap filtering can significantly improve the performance of data warehouse queries that use star schemas by removing non-qualifying rows early in the query plan. This reduces the rows passed through the join operator and all subsequent operators resulting in improved query response times. In SQL Server 2008, bitmap filtering can be introduced in the query plan after optimization 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. For more information about bitmap filtering, see Optimizing Data Warehouse Query Performance Through Bitmap Filtering.
Performance problems specific to bitmap filtering occur only when a bitmap filter becomes nonselective. A nonselective bitmap filter creates unnecessary overhead in the processing of a query and rather than improving performance, can cause the query to perform slower.
Diagnosing and Resolving Nonselective Bitmap Filters
To diagnose and resolve query performance problems related to nonselective bitmap filters, follow these steps:
Identify the queries that cause the performance problem.
Use SQL Server Profiler to help identify the slow query or queries. For more information, see Using SQL Server Profiler. You can also analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. For more information, see SET SHOWPLAN_TEXT (Transact-SQL), XML Showplans, and Displaying Graphical Execution Plans (SQL Server Management Studio).
Verify that the queries use bitmap filters.
You can capture query plans using sys.dm_exec_query_plan (Transact-SQL) and sys.dm_exec_query_stats (Transact-SQL) to verify whether the query actually uses bitmap filters. Alternatively, you can look for bitmap filters in the XML Showplan or graphical execution plan of the queries. For more information, see Interpreting Execution Plans Containing Bitmap Filters.
Verify that bitmap filtering is the cause of the performance problem.
Bitmap filtering is applied only in parallel query plans in which hash or merge joins are used. Optimized bitmap filtering is applied only in parallel query plans in which hash joins are used. To verify that a bitmap filter is impacting query performance, manually disable bitmap filtering by specifying the join hint LOOP in the query statement. This forces the optimizer to choose the specified join type rather than a hash join. You can also disable optimized bitmap filtering by specifying the join hint MERGE in the query statement. For more information about using these hints, see Join Hints (Transact-SQL) and Query Hints (Transact-SQL).
Use the trace event Bitmap Warning to track the queries in which bitmap filters are disabled.
By default, the query optimizer disables nonselective bitmap filters automatically. If the trace event fires, try to eliminate the reasons that the bitmap filter became nonselective. Bitmap filter non-selectivity can have the following causes:
Inappropriate cardinality estimates made by the query optimizer.
SQL Server estimates cardinalities primarily from histograms that are created when indexes or statistics are created, either manually or automatically. Out-of-date statistics and missing indexes can cause incorrect cardinality estimates, resulting in inaccurate cost calculations that may cause suboptimal query plans. Build useful indexes or statistics on the columns that are involved in the query. For more information, see Troubleshooting Poor Query Performance: Cardinality Estimation.
Memory pressure on the system.
In a heavily loaded system under memory pressure, queries with bitmap filters in the query plan can drop the bitmap operator when the queries do not get the minimum required memory to create the bitmap. System Monitor can be used to monitor the performance of SQL Server and non-SQL Server components that may be causing memory pressure on the system. For example, you can use the Memory Manager object to monitor overall server memory usage. For more information, see Monitoring Resource Usage (System Monitor). For information about troubleshooting memory pressure problems, see Troubleshooting Performance Problems in SQL Server 2005.
Not enough threads to run the query in parallel.
Bitmap filters are applied only in a parallel query plan. When the thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. It then executes the serial plan (one thread). For more information, see Degree of Parallelism.