Data World

Posts Tagged ‘index’

SQL Server – Cause Of Index Scan

Posted by Pramod Singla on May 16, 2011

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’


Posted in Performance Tuning, sql server | Tagged: , , | 1 Comment »