• This is not a good idea.

    The missing index feature is a great place to start index tuning, but it will result in a lot of near-duplicate indexes (eg index on col1, col2; index in col1, col2, include col3; index on col1, col2, col4, include col3, col5). It's going to over-index the database resulting in more indexes than needed and hence slower modifications.

    I've had clients who have done things like this, or run DTA and accepted all suggestions (and DTA will generally come up with a smaller set of indexes than missing index DMV, as it considers the workload as a whole, while missing indexes considers queries in isolation), and they generally have a lot of overlapping indexes, overly large indexes (between key and include having most of the table), unused indexes and similar.

    The last time I saw that was a client last month. One table had 12 indexes on it (excluding the clustered index). After looking at all the queries that currently affect that table, I dropped the whole lot and created 3 which support all the queries better than the 12 did.

    This has been designed to specifically ensure that duplicate indexes never occur if you actually look at the script

    How? It's checking on name, but not the columns in the index that I could see.

    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