2

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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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