Greater than operator ignoring a nonclustered index?

Question

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?

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.
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)?

Jimbo · Answer 1 · 2012-04-26 14:33:11Z

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.

1 Answer