Distinguishing Access and Filter-Predicates
The SQL Server database uses three different methods for applying where clauses (predicates):
- Access Predicate (“Seek Predicates”)
The access predicates express the start and stop conditions of the leaf node traversal.
- Index Filter Predicate (“Predicates” or “where” for index operations)
Index filter predicates are applied during the leaf node traversal only. They do not contribute to the start and stop conditions and do not narrow the scanned range.
- Table level filter predicate (“where” for table operations)
Predicates on columns which are not part of the index are evaluated on the table level. For that to happen, the database must load the row from the heap table first.
The following section explains how to identify filter predicates in SQL Server execution plans. It is based on the sample used to demonstrate the impact of index filter predicates in Chapter 3. The appendix has the full scripts to populate the table.
CREATE TABLE scale_data ( section NUMERIC NOT NULL, id1 NUMERIC NOT NULL, id2 NUMERIC NOT NULL );
CREATE INDEX scale_slow ON scale_data(section, id1, id2);
The sample statement selects by
SELECT count(*) FROM scale_data WHERE section = @sec AND id2 = @id2
In Graphical Execution Plans
The graphical execution plan hides the predicate information in a tooltip that is only shown when moving the mouse over the
Index Seek operation. Hover over the
Index Seek icon to see the predicate information—really, on this web-page.
The SQL Server’s Seek Predicates correspond to Oracle’s access predicates—they narrow the leaf node traversal. Filter predicates are just labeled Predicates in SQL Server’s graphical execution plan.
In Tabular Execution Plans
Tabular execution plans have the predicate information in the same column in which the operations appear. It is therefore very easy to copy and past all the relevant information in one go.
DECLARE @sec numeric;
DECLARE @id2 numeric;
SET STATISTICS PROFILE ON;
SELECT count(*) FROM scale_data WHERE section = @sec AND id2 = @id2;
SET STATISTICS PROFILE OFF;
The execution plan is shown as a second result set in the results pane. The following is the
StmtText column—with a little reformatting for better reading:
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(...)) |--Stream Aggregate(DEFINE:([Expr1008]=Count(*))) |--Index Seek(OBJECT:([scale_data].[scale_slow]), SEEK: ([scale_data].[section]=[@sec]) ORDERED FORWARD WHERE:([scale_data].[id2]=[@id2]))
SEEK label introduces access predicates, the
WHERE label marks filter predicates.
Chapter 3, “Performance and Scalability”, demonstrates the performance difference access and index filter predicates make.