Question: Why Index Scan is happening even thought index exists on predicate column?
Ans:
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’
•Functions like upper, lower, substring are applied on the predicate columns of the query, e.g.
select *from Person.Address where substring(City,1,4) =’Both’
-> select *from Person.Address where City like ‘Both%’
-> This one actually can make use of an Index on City.
•In like operator, % wild character is used as the first character in the search string, e.g.
select *from Person. where City like ‘%Both’
-> select *from Person.Address where City like ‘Both%Address’
-> If this is what you mean, % inside the search string, it is similar as the case above, where it can use the Index to find all matches starting with ‘Both’.