Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why does the below query perform an Index Scan rather than an Index Seek?

I would have thought the Index would act as a covering index?

CREATE NONCLUSTERED INDEX [IX_CustomerFileDate] 
ON [dbo].[UserData] 
(
    [CustomerID] ASC,
    [FileDate] ASC
)
INCLUDE ([UserID],[User])

-- query
SELECT c.User, c.UserID 
FROM ClientData c
WHERE CustomerID = @CustomerID
AND FileDate >= @StartDate
AND FileDate <= @EndDate

Execution Plan posted to dropbox

Thanks!

share|improve this question
3  
Your first two sentences are very different questions. Are you wondering why this is a Scan vs. Seek? Or are you wondering why your index (IX_CustomerFileDate) wasn't chosen over another index? –  Thomas Stringer Jan 6 at 15:25
1  
It is a covering index, but that doesn't mean that it should do a seek instead of a scan –  Lamak Jan 6 at 15:28
1  
Without seeing the actual execution plan, we could only guess. Guesses don't make for good answers. Could you post the actual (not estimated) plan somewhere, please? –  Aaron Bertrand Jan 6 at 15:28
3  
There is a huge difference between the estimated rows and the actual rows. Have you updated the statistics of that table recently?, doing that should make the optimizer choose a better plan for your query –  Lamak Jan 6 at 16:08
1  
@MartinSmith True. Thanks! –  Paul White Jan 7 at 20:17

1 Answer 1

The Execution plan shows a non clustered index scan:

enter image description here

Because you have a implicit conversion of your first index seek column SQL Server decided to do a full non clustered index scan. It's not doing a clustered index scan because the non clustered index is a covering index. 1)

enter image description here

You can see that the column customerID needs to be converted to a NVARCHAR(50) datatype.(red conversion). Because the column first needs to be converted, it can't be used. Since it's the first column in your index, it effectively makes the index useless for seeks or estimates.

Why is SQL Server doing a implicit conversion of the column? Because on the right side of the = sign you feed it a Nvarchar. Your not doing this explicit, since also the parameter @CustomerID first gets implictly converted to a NVARCHAR(3) (blue conversion).

I presume this is because your are filling @CustomerID with a Unicode value. However, It would suprise me that you would use a unicode values and yet have non unicode datatypes defined in your table. Maybe somebody else could confirm the reason for the blue conversion.

1) I deleted a wrong assumtion about the relevance of the amount of rows returned that Martin Smith was nice enough to point out to me.

share|improve this answer
1  
Regarding the blue conversion I think this is because the collation of the variable is different from the collation of the column. If I create a database with default collation COLLATE SQL_Ukrainian_CP1251_CI_AS and query a column of datatype SQL_Latin1_General_CP1_CI_AS I see that for example. –  Martin Smith Jan 7 at 8:05
    
Hi @Edward, I updated @CustomerID to match the table data type - varchar(50). It now uses an Index Seek, but is still doing conversions... Seek Keys[1]: Prefix: [TestData].[dbo].[UserData].CustomerID = Scalar Operator(CONVERT_IMPLICIT(varchar(50),[@CustomerID],0)), Start: [TestData].[dbo].[UserData].FileDate >= Scalar Operator([@StartDate]), End: [TestData].[dbo].[UserData].FileDate <= Scalar Operator([@EndDate])........ Also Estimated Number of Rows is still huge compared to Actual Number of Rows. –  k09 Jan 7 at 10:40
    
@k09 - Conversions of the parameters aren't such a problem. Presumably that is the collation issue. And you will almost certainly get the same plan anyway even if estimated vs actual is accurate. Did you update the statistics on the table though? Also in the XML version of the plan what are ParameterRuntimeValue and ParameterCompileValue? –  Martin Smith Jan 7 at 10:44
    
@k09 - Actually looking at your previous plan the values aren't sniffed at all. Are you using variables rather than parameters? You can add OPTION (RECOMPILE) to get accurate cardinality estimates but the plan will probably still end up the same. –  Martin Smith Jan 7 at 10:54
    
@Martin. Statistics updated on this specific Index. Is this enough? By Parameter do you mean using question mark? Using variables e.g. declare @StartDate datetime = '2014/01/03' –  k09 Jan 7 at 12:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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