Glenn Berry (Blog) writes a lot of queries to extract information from the system DMV’s. One of them in particular I found extremely helpful in fixing some of the issues in my system. I took his query (the CTE at the top) and added some text manipulation to actually generate the create statements for you to save you some time. I had much grander plans for this, but unfortunately I’ve been meaning to post this for over a month now and simply haven’t had time to get back to it, so rather than just let it go by the wayside and never post it, I figured I’d just post what I had now and then possibly post an update sometime in the future if I ever finish it.

A couple of the known problems right now are:

Index names could already be taken, there’s nothing here that checks to make sure they are unique based on other indexes in your database.

No compression options are taken into account.

That said, I still found this fairly useful and hopefully somebody else will as well. Thanks again to Glenn for all his excellent work at creating queries to pull information from the DMV’s.

Comments

Posted by Jason Brimhall on 24 May 2010

Posted by Anonymous on 25 May 2010

Pingback from Twitter Trackbacks for SQL Server Central, Create Indexes from the Missing Indexes DMV - Never Say Never [sqlservercentral.com] on Topsy.com

Posted by Glenn Berry on 25 May 2010

Nice query Seth. People should be a little careful not to just blindly create indexes to match what the missing index DMV query wants. You should always look at your existing indexes and consider your workload, and use your judgement to decide whether to add new indexes. I have seen too many people go wild and add way too many indexes after they start using the missing index query.

Posted by Seth Phelabaum on 25 May 2010

Glenn,

I agree completely, thanks for filling in that hole in my post. I created probably 10 indexes from it (but in my case, I knew I was missing quite a few) and ignored many others. When making this decision, I took a lot of things into account. Things like:

How many indexes were already on the tables.

How many columns the suggested index was on

How often those columns were updated

What kind of processes the table was involved in (was there a lot of transactions/locking?)

Whether the table was directly interacted with by users or more often by system processes.

Whether or not the index looked like it was from an automated report.

Whether or not I already had that exact index (in some cases, I've found the missing index dmv still has indexes which are already present, although I couldn't tell you why.

This query should only be used as another diagnostic tool, not as a blanket fix all.