SQL Server - Why does Query Optimizer choose a Clustered Index Scan?

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 ?Query to find non – sargable sql statements – SQL Antipattern #009

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


 Read Also on Query Optimizer,Trivial Plans , DMV

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Server Optimizer and Trivial Plan - SQL Server DBA

sys.dm_exec_query_optimizer_info - SQL Server DBA

Query to find non – sargable sql statements – SQL Antipattern #009

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - Why does Query Optimizer choose a Clustered Index Scan?

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer