CODEWITHSUNDEEP
sql-server
TheGeneral
TheGeneral

Reputation: 81493

Inconsistent results with SQL Server Execution plan when using variables

I have a table with 5 million records as follows

Id          BasePrefix  DestPrefix  ExchangeSetId   ClassId
11643987    0257016         57016           1           3
11643988    0257016         57278           1           3
11643989    0257016         57279           1           3
11643990    0257016         57751           1           3

SQL Tuning adviser recomended the following index

CREATE NONCLUSTERED INDEX [ExchangeIdx] ON [dbo].[Exchanges]
(
    [ExchangeSetId] ASC,
    [BasePrefix] ASC,
    [DestPrefix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

However given the following

DECLARE @exchangeSetID int = 1;
DECLARE @BasePrefix nvarchar( 10 ) = '0732056456';
DECLARE @DestPrefix nvarchar( 10 ) = '30336456';
DECLARE @BaseLeft nvarchar( 10 ) = left(@BasePrefix,4);

These 2 queries give me vastly different execution plans

Query 1

SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE '0732' + '%'
   AND '0732056456' LIKE BasePrefix + '%'
   AND '30336456' LIKE DestPrefix + '%';

enter image description here

Query 2

SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE @BaseLeft + '%'
   AND @BasePrefix LIKE BasePrefix + '%'
   AND @DestPrefix LIKE DestPrefix + '%';

enter image description here

The difference between the 2 queries are @BaseLeft and '0732' respectively

Basically, in the first example the index is used, and in the second, not so much

Is there any compelling reason why this should be so?

And if this is not just a fundamental flaw in my thinking, how could i pass a variable to the second query and make use of the index?

Upvotes: 1

Views: 100

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

The explanation of this behavior is the tipping point ( #1, #2 ).

Basically, depending on predicate's selectivity which influences how many 8K data pages are read from buffer pool, SQL Server has two options to filter rows:

1) Index Seek + Key/RID Lookup

2) Table/[Clustere] Index Scan.

Why SQL Server could use a Scan instead of Seek + Lookup? Because, in some cases (low/medium/medium-high selectivity) , using a Seek + Lookup could read from buffer pool more pages than a single Scan.

What to do? You should create a covered index thus:

create nonclustered index ...
on ... (...)
include (ClassId);

Upvotes: 1

Related Questions