Data World

SQL Server – Cause Of Index Scan

Posted by Pramod Singla on May 16, 2011


Introduction: 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’
  •  
     

    Advertisements

    One Response to “SQL Server – Cause Of Index Scan”

    1. SQL Server Performance Tune said

      •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’.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: