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 SECTION and ID2:

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]))

The SEEK label introduces access predicates, the WHERE label marks filter predicates.