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

 Read Also

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