Question: Why Index Scan is happening even thought index exists on predicate column?
Generally,Query Optimizer (QO) tries to use an Index Seek which means that optimiser has found a useful index to retrieve recordset. But sometimes despite of an index being created on a query predicate columns, Query Optimiser uses Index Scan instead of Index Seek. The causes of using index scan are explained below.
Causes of Index Scan
- The index is not selective enough.Use DBCC show_statistics (‘tableName,’indexName’) to check the selectivity of an index. Selectivity is inversely proportional to the density. It means low density of the index is good.
- The number of pages returned by the query have reached the tipping point.
- The first column of the index is not included in the query predicate.
- Statistics are stale.
- Functions like upper, lower, substring are applied on the predicate columns of the query.
- In like operator, % wild character is used as the first character in the search string, e.g.
select *from Person. where City like ‘%Both’