Clustered Index Scan when using greater than in WHERE clause

Question

I have the following table

create table t1(
col1 varchar(255) NOT NULL,
col2 varchar(255) NULL,
col3 bigint NULL,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED
 (
   col1 asc
 )
)

CREATE NONCLUSTERED INDEX I_col3 ON t1(col3 desc)

This table has ~10000 rows with col2 always being populated and col3 has varying percentages of rows that are NOT NULL.

I am running the following query

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2 FROM t1
WHERE col3 > @number

SQL is always generating a execution plan with a clustered index scan.

Now if I run the query as an adhoc query SQl does an index seek on I_col3 with a key lookup

SELECT col1, col2 FROM t1
WHERE col3 > 123456

The value being passed in the WHERE clause results in a small number of rows being returned (e.g. 3)

When running the ad-hoc query the execution plan shows the Estimated Number of Rows as 3.32. However, when I run the parameterized query it shows 2796 estimated rows.

If I add an index hint to the ad-hoc query it still shows 2796 estimated rows (not that I expect this to change) but it does do an index seek. When comparing the number of logical reads between the ad-hoc and parameterized query the Parameterized Query: Scan count 1, logical reads 89, physical reads 1, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ad-Hoc Query Scan count 1, logical reads 8, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And when forcing the index using the index hint Scan count 1, logical reads 12, physical reads 1, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I'm currently running SQl 2014 CP1-CU5. I've tried using trace flag 9184 to force SQL to use the old cardinality estimator which reduces the estimated rows to 1627 but still does a clustered index scan. I've also tried on SQL 2008 R2 and it has the same behavior as SQL 2014 with trace flag 9184.

In between each iteration of the query I am clearing the procedure cache and buffer pool.

I should also noted that I have also tried the following

1. Filtered INDEX on col3 WHERE NOT NULL
2. Filtered STATS on col3 WHERE NOT NULL

I've also done a full rebuild on the both the clustered and non clustered indexes and verified that stats are up to date.

Any suggestions or ideas?

Thanks again

UPDATE: From reading through some links from Martin's answer I found the following blog post:

http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/

I confirmed that using sp_executesql or exec(@sql) does result in an index seek execution plan being generated and reused regardless of what the parameter value(s) are.

DECLARE @sql nvarchar(max)
SELECT @sql = 'SELECT col1, col2, FROM t1 WHERE col3 > @number'
EXEC sp_executesql @sql, N'@number bigint', @number = 123456789

Answer 1

The values of variables aren't generally sniffed so it will just assume a flat 30% of the table will be returned for that greater than predicate against an unknown value (cf. Selectivity Guesses in absence of Statistics).

When you use the literal it can look up the known value in the column statistics to get a much more accurate estimate.

If it estimates such a high percentage of the table will match it is exceedingly unlikely to give you a plan with lookups (the exact tipping point depends on how wide the table and index is).

If you use option (recompile) the variable value is able to be sniffed however...

DECLARE @number bigint
SET @number = 123456
SELECT col1, col2
FROM t1
WHERE col3 > @number
OPTION (RECOMPILE);

Will likely give you the same plan as when using the literal.

Answer 2

You could create a stored procedure that uses dynamic SQL to allow SQL Server to decide the best plan for a given value of col3. This is similar to @Martin's answer with the OPTION (RECOMPILE), with the added benefit of execution plan caching for queries that run more than once.

CREATE TABLE dbo.t1
(
    col1 varchar(255) NOT NULL,
    col2 varchar(255) NULL,
    col3 bigint NULL,
    CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED
    (
        col1 asc
    )
);

CREATE NONCLUSTERED INDEX I_col3 ON dbo.t1(col3 desc);

The below stored procedure will create a unique plan that is cached for each value of the @Col3 parameter¹:

IF OBJECT_ID('dbo.GetRows') IS NOT NULL
DROP PROCEDURE dbo.GetRows;
GO
CREATE PROCEDURE dbo.GetRows
(
    @Col3 BIGINT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(MAX);
    SET @cmd = 'SELECT t1.col1
    , t1.col2
FROM dbo.t1
WHERE t1.col3 > CONVERT(BIGINT, ' + CONVERT(NVARCHAR(30), @Col3) + ');';
    EXEC sys.sp_executesql @cmd;
END
GO

Each plan generated here will be cached. If you have a large number of possible values for the @Col3 parameter, and each individual query is ran infrequently, you may want to enable "optimize for ad-hoc workloads".

I inserted some test data into the table using this:

INSERT INTO dbo.t1 (col1, col2, col3)
SELECT o1.name + CONVERT(VARCHAR(30), o2.object_id), o2.name, o1.object_id
FROM sys.objects o1, sys.objects o2;

Since sys.objects has 113 rows in my tempdb, this results in 12769 rows inserted into dbo.t1.

Let's see what happens with the stored procs for three interesting cases. The first case is for a col3 value that does exist (and has 113 rows), the second case is for a value that does not exist in col3, and the final case returns all the rows in the table:

EXEC dbo.GetRows 2105058534;
GO
EXEC dbo.GetRows 2105058535;
GO
EXEC dbo.GetRows -2105058533;
GO

The plans for these executions are different; the first uses a scan of the table, the second uses a seek/lookup, which is clearly giving us the best of both worlds for the two queries:

The plan shape for the third case looks very similar to the first case, a simple table scan. However, if you look closely, the expected row counts differ quite drastically. You can see this in the plan shape by looking at the thickness of the connecting line between the two plan operators:

Looking at the plan cache, we see three plans have been created that will be re-used if we re-run any of the statements. I'm using this to look at the plan cache:

SELECT decp.objtype
    , decp.refcounts
    , decp.usecounts
    , decp.size_in_bytes
    , dest.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) dest 
WHERE dest.text LIKE '%t1.col3 > CONVERT(%'
    AND dest.text NOT LIKE '%cached_plan%'

Which displays this:

Note rows 2 to 4 show the ad-hoc cached plans for the three executions of the stored proc. Row 1 is the stored proc itself. If we enable the optimize for ad-hoc workloads option, the ad-hoc plans will only actually be cached if the query runs 2 or more times. Purely single-use queries will only result in a stub being saved into the procedure cache.

---

^{1 - notice the stored procs use CONVERT(BIGINT, @Col3) in the where clause - this is important in dynamic SQL since the optimizer will not necessarily correctly guess the data type of the passed in value which may cause incorrect cardinality estimates due to implicit conversions in the plan.}

Answer 3

The most effective method I've found is to give the query plan more information about the expected values of parameters.

Option (optimise for (@var1 = 2000, @var2 = 4000))

Apologies writing this from mobile so not as easy to test against your query