Post reply

Index is not used (but it is, after editing something....)

  • January 15, 2009 at 6:14 am

    #129470

    There is an index which isn't used in the explain plain. The query performs very bad (it should be using the index....).

    It's very unwanted to use a hint in the code, SQL should use the appriopriate plan by itself.

    But editing the index (not changing anything) makes SQL to use the index. WHY? How can I work around this behaviour?

    Restarting the instance or restoring to an old dump gives the same behaviour every time. The index is only used after editing the sort order and reset it to the original value......

  • January 15, 2009 at 6:48 am

    #927249

    SQL won't use the index if it deems that it would be more efficient to perform a table scan.

    What does the query, the indexes on the table and the query plan look like?


    Shamless self promotion - read my blog http://sirsql.net

  • January 15, 2009 at 6:57 am

    #927256

    Instead of editing the Index, Did you try updating the Stats of that table? Maybe the Statistics is outdated and that is why the query engine is taking a plan where it thinks the Table Scan is more efficient.

    -Roy

  • January 15, 2009 at 8:07 am

    #927345

    By rebuilding the index you have updated statistics and removed fragmentation. Both can make SQL pick an index it previously ignored. My bet's on the statistics.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply