Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why clustered index scan is called everytime instead of Non-clusered index? Expand / Collapse
Author
Message
Posted Friday, October 9, 2009 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:34 AM
Points: 46, Visits: 59
I would like to know why non-clustered index on three or more columns does not work even when the columns are put in the same order in Where clause as in the index and instead of that clustered index scan is called in the Execution plan.

I found through search that Non clustered index is not called because my where clause was not enough selective but what if I need more data and retrieval should be fast.

Amit Gupta
Sr. Software developer
New Delhi
Post #800624
Posted Friday, October 9, 2009 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 11,148, Visits: 12,889
Well, without more details it's hard to give you an answer. Basically the Query Optimzer has decided that, given the available options, a clustered index scan is the fastest access method. It could be that your non-clustered index does not cover the query (include all the columns returned in the select list). If you post some DDL and a query for an example someone may be able to be more specific.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #800786
Posted Friday, October 9, 2009 9:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #800828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse