SQL Server Performance

Index not being used

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by EMoscosoCam, Nov 24, 2010.

  1. EMoscosoCam Member

    Hello
    I have a table with a field called UpdateTimeStamp, with a datetime datatype and there is an Index for that field. In the following simple query, I can see that a Clustered Index Scan is performed instead of the expected Index Seek. What could be the reason?
    select * from MyTable where UpdateTimeStamp>@LastTransferTimeStamp
    Thanks a lot.
  2. Adriaan New Member

    The scan is against the clustered index of your table, which typically is the Primary Key (which is a clustered index by default).
    You mention that there is an index on the UpdateTimeStamp column, but does the index cover only that column? If there are more columns in the same index, then is UpdateTimeStamp the first one listed in the index definition?
  3. EMoscosoCam Member

    Thanks for your reply. The mentioned Index convers only the UpdateTimeStamp column in ascending order.
  4. Adriaan New Member

    .. and I presume it it NOT the clustered index for that table?
  5. EMoscosoCam Member

    Of course, is a different Index created specifically to retrieve faster the rows where UpdateTimeStamp>@LastTransfer
    And I have just noticed that if I specify the columns to output, and Index Seek (for the referred Index) and an Index Scan are performed.
    select PKColum, OtherColumn, UpdateTimeStamp from MyTable where UpdateTimeStamp > @LastTransfer
    I presume that depending also of the columns being retrieved, Indexes are used.
  6. Adriaan New Member

    Is @LastTransfer declared as a DATETIME variable?
  7. EMoscosoCam Member

    Yes, the same datatype as the UpdateTimeStamp column.
  8. Adriaan New Member

    An Index Scan is still better than a Table Scan, so you do have the benefit of having the index.
    If you were filtering for a specific date, or a date range, then you would probably get an Index Seek on the index covering UpdateTimeStamp.
    With a < or > operator, the query engine will guess about one third of the rows will match the criteria, so no point in doing a seek since it needs to read a lot of rows for the results anyway.

Share This Page