Data World

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?

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s