Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Create Indexes from the Missing Indexes DMV

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.

;WITH I AS (

-- Missing Indexes current database by Index Advantage

-- This DMV Query written by Glenn Berry

SELECT user_seeks * avg_total_user_cost *

      (avg_user_impact * 0.01) AS [index_advantage],

migs.last_user_seek,

mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns,

mid.included_columns,migs.unique_compiles, migs.user_seeks,

migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

WHERE mid.database_id = DB_ID()

      AND user_seeks * avg_total_user_cost *

      (avg_user_impact * 0.01) > 9000 -- Set this to Whatever

)

 

SELECT 'CREATE INDEX IX_'

            + SUBSTRING([Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table])+4)+3,

                              LEN([Database.Schema.Table]) - 

                              (CHARINDEX('].[',[Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table])+4)+3))

            + '_' + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(

            ISNULL(Equality_Columns,inequality_columns),

            '[',''),']',''),' ',''),',',''),20)

            + ' ON '

            + [Database.Schema.Table]

            + '('

            + ISNULL(equality_columns,'')

            + CASE WHEN equality_columns IS NOT NULL AND

                              inequality_columns IS NOT NULL

                  THEN ','

                  ELSE ''

              END

            + ISNULL(inequality_columns,'')

            + ')'

            + CASE WHEN included_columns IS NOT NULL

                  THEN ' INCLUDE(' + included_columns + ')'

                  ELSE ''

              END CreateStatement,

            'IX_'

            + SUBSTRING([Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table])+4)+3,

                              LEN([Database.Schema.Table]) - 

                              (CHARINDEX('].[',[Database.Schema.Table],

                              CHARINDEX('].[',[Database.Schema.Table])+4)+3))

            + '_' + LEFT(REPLACE(REPLACE(REPLACE(REPLACE(

            ISNULL(Equality_Columns,inequality_columns),

            '[',''),']',''),' ',''),',',''),20)

                  IndexName

FROM I

Comments

Posted by Jason Brimhall on 24 May 2010

Nice script Seth.

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.

Posted by Anonymous on 25 May 2010

Pingback from  PHP help. mysql_fetch_array() doesn’t work. please, need immediate help? | BingSite

Posted by Anonymous on 25 May 2010

Pingback from  PHP help. mysql_fetch_array() doesn’t work. please, need immediate help? | BingSite

Leave a Comment

Please register or log in to leave a comment.