20 June,2012 by Jack Vamvas
Question: Why is the Query Optimizer choosing a Clustered Index Scan?
Answer: The Query Optimizer chooses a Clustered Index Scan for different reasons.
1) The Query Optimizer has no alternative. For example, the statement SELECT * FROM myTable may require to read every row in the table. If a Clustered Index is present , than it must touch every row
2) Are the predicates Sargable? Can they exploit the index ?
A statement such as the following will be non-sargable. The index will need to evaluate the Function for every row. If if the index exists – it cannot exploit it
SELECT col1, col2 FROM myTable WHERE Function(Co) = ‘hdhdh’
3) Are subtle implicit conversions occurring? For example is a VARCHAR attempting to convert into a datetime?
4) A Nonclustered Index exists but the index tipping point is not met
5) Cardinality estimates. The Clustered Index Keys statistics supply the estimate of the cardinality estimate - i.e Amount of rows matching. The optimizer may choose a Clustered Index Scan , where the DBA expects a Seek. The optimizer makes an evaluation of the JOINS – and may decide a SCAN is more effectiveThe more complex queries , with multiple joins
6) Does a suitable Index exist? Are the predicate columns included as part of a Index?
Use this query to identify the indexes and columns used. In which indexes do predicates exist
SELECT ix.name as 'ix_name' ,ix.object_id, t.name, ix.index_id ,ixcols.index_column_id ,col.name as 'ix_col_name' FROM sys.indexes ix INNER JOIN sys.index_columns ixcols ON ix.object_id = ixcols.object_id and ix.index_id = ixcols.index_id INNER JOIN sys.columns col ON ixcols.object_id = col.object_id and ixcols.column_id = col.column_id INNER JOIN sys.tables t ON ix.object_id = t.object_id WHERE (1=1) AND ix.is_primary_key = 0 AND ix.is_unique = 0 AND ix.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND t.name = 'myTable' AND col.name IN ('col1','col2') ORDER BY ix.name, ix.index_id, ixcols.index_column_id