Why SQL Server is not using non-clustered index

Question

Why SQL Server is not using non-clustered index

vigneshbeece

Mr or Mrs. 500

Points: 569
More actions
November 21, 2017 at 3:17 am

#324584

In SQL server 2008R2, Table having Non-clustered index and table contains 2 crore records, while executing "Select count(*) from table name" it going for table scan, its not using non clustered index. Why index is not used?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

It's going to have to scan something, since there's no filter on that query. Optimiser calculated that a scan of the table will be the most efficient.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vigneshbeece Mr or Mrs. 500 Points: 569 More actions · Answer 2

If any where condition is there, optimizer would use Non-clustered index right?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Maybe. Depends on the predicate, the index key columns and the rest of the query. It's not as simple as you're making out.
There's a lot of posts on indexes and index usage on my blog. https://www.sqlinthewild.co.za/index.php/category/sql-server/indexes/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6345 More actions · Answer 4

Since it does cost based optimization , based on 'estimates' SQL decides to go with the least costly plan. Depending upon , say , how stale are the stats for example , the optimizer will prefer doing a scan if its cheaper (at plan level) than using the index .