Post reply

Duplicate Index

  • November 25, 2009 at 2:02 am

    #137387

    Dear All,

    Due to use of Tuning advisor sometimes duplicate indexes are created.

    For example table originally had an index on Field1, tuning advisor suggested an index on Field1,Field2...

    Now should index on Field1 be removed or what?.

    Please note that some queries use Field1 only but other use Field1,Field2....

    Thanks in advance

  • November 25, 2009 at 2:13 am

    #1083870

    You can get some idea from sys.dm_db_index_usage_stats DMV. Of course too many indexes degrade the performance.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 25, 2009 at 2:36 am

    #1083877

    Sometimes the first index is used and other times the other one, when i removed on of them on backup of life database, i found that the queries using the index on the single field are now switching to the other composite index.

    I prefer to remove those duplicate indexes but i am afraid it causes any performance degradation.

    Thanks

  • November 25, 2009 at 7:33 am

    #1084008

    nadersam (11/25/2009)

    I prefer to remove those duplicate indexes but i am afraid it causes any performance degradation.

    So do some tests (in a dev or test environment), see how queries perform, remove the duplicate indexes, see if there's been any major performance degradation.

    You should do this before making any index changes anyway.

    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

  • November 25, 2009 at 11:51 am

    #1084252

    Gail has provided sound advice. Also consider the additional performance hit (small as it may be) you are experiencing due to the maintenance of those duplicate indexes.

    Test your queries and performance, then adjust your indexes as needed.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 30, 2009 at 12:05 am

    #1085260

    Thanks for all replies

Viewing 6 posts - 1 through 5 (of 5 total)

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