9

I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise.

I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek Predicate and also a Predicate

What's the difference between Seek Predicate and Predicate?

enter image description here

Note: I can see that there are plenty of problems with this node (e.g. the Estimated vs Actual rows, the residual IO), but the question does not relate to any of that.

  • 2
    The seek predicate assists with the join, filtering only to the rows that are also found in the other table (that you've redacted). The predicate (a residual predicate) then eliminates the rows with the specific status of 2. – Aaron Bertrand May 30 '17 at 3:21
  • 3
    Rob Farley stated the following in a comment here: The Seek Predicate can be used to find the start of the RangeScan and then when to stop, while the Predicate is the "check" that is applied to every row in the Range. – Aaron Bertrand May 30 '17 at 3:26
15

Let's throw one million rows into a temp table along with a few columns:

CREATE TABLE #174860 (
PK INT NOT NULL, 
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (PK)
);

INSERT INTO #174860 WITH (TABLOCK)
SELECT RN
, RN % 1000
, RN % 10000
FROM 
(
    SELECT TOP 1000000 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN
    FROM   master..spt_values v1,
           master..spt_values v2
) t;

CREATE INDEX IX_174860_IX ON #174860 (COL1) INCLUDE (COL2);

Here I have a clustered index (by default) on the PK column. There's a nonclustered index on COL1 that has a key column of COL1 and includes COL2.

Consider the following query:

SELECT *
FROM #174860
WHERE PK >= 15000 AND PK < 15005
AND COL2 = 5000;

Here I'm not using BETWEEN because Aaron Bertrand is hanging around this question.

How should SQL Server optimizer that query? Well, I know that the filter on PK will reduce the result set to five rows. SQL server can use the clustered index to jump to those five rows instead of reading through all million rows in the table. However, the clustered index only has the PK column as a key column. Once the row is read into memory we need to apply the filter on COL2. Here, PK is a seek predicate and COL2 is a predicate.

enter image description here

SQL server finds five rows using the seek predicate and further reduces those five rows to one row with the normal predicate.

If I define the clustered index differently:

CREATE TABLE #174860 (
PK INT NOT NULL, 
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (COL2, PK)
);

And run the same query I get different results:

enter image description here

In this case, SQL Server can seek using both columns in the WHERE clause. Exactly one row is read from the table using the key columns.

For one more example consider this query:

SELECT *
FROM #174860
WHERE COL1 = 500
AND COL2 = 3545;

The IX_174860_IX index is a covering index because it contains all of the columns needed for the query. However, only COL1 is a key column. SQL Server can seek with that column to find the 1000 rows with a matching COL1 value. It can further filter down those rows on the COL2 column to reduce the final result set to 0 rows.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.