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.

I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column.

When I look at the execution plan for the following query it seeks the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate = '2012-04-12'

However when i look at the execution plan for this query it does not seek the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate > '2012-04-12'

If I force it to use the index the query works and is quick, but if I do not force it, it takes forever. Why isn't SQL using the index on its own?

share|improve this question
11  
Index Tipping Point –  Remus Rusanu Apr 25 '12 at 22:32
5  
Is the plan trivial? How many rows actually fall at midnight? What happens if you reduce SELECT * to columns covered by the index (or change the index to cover more columns, or experiment with a clustered index on that column instead)? Can you show the execution plan? How many rows are we talking about? If it is a lot of rows what looks like a seek may actually be a range scan, though that doesn't explain the performance difference. More details will help. –  Aaron Bertrand Apr 25 '12 at 22:35
2  
And by performance difference, I don't mean between query 1 and 2, I mean between query 2 without the hint and query 2 with the hint. Is the hint a generic FORCESEEK or a specific WITH INDEX(x)? –  Aaron Bertrand Apr 25 '12 at 22:41

1 Answer 1

Ensure your statistics are updated for your table and indexes.

Current statistics are crucial for determining the correct plan.

In your plan if the "Actual Number of Rows" and the "Estimated Number of Rows" are fairly close then your statistics are ok.

If they need updated try using a FULLSCAN - sql server uses it's own sample size but this can sometimes be too small.

share|improve this answer

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.