Missing Indexes

Hello all,

I want to know how to apply missing indexes once we get a list of missing indexes using DMV queries.

For details please refer attached file
Who is Participating?
PadawanDBAConnect With a Mentor Operational DBACommented:
easiest way, in my opinion, is to just use SSMS:

View > Object Explorerer Details > highlight the 'Indexes' subfolder of the table in question > multiselect the indexes in the object explorer details window > right click > script index as > create to > new query window

Which will give you the TSQL statements that were used to create the indexes that are currently on that table.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure what you're asking.  The attached file has the CREATE INDEX statments that can be used to create those indexes it identified as missing.
omkaar4Author Commented:
Sorry I have just attached it.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

omkaar4Author Commented:
I used DMV query to get list of missing indexes.
But after this we need to compare these  recommendations with the current indexes on that table. I dont know how to compare them.
omkaar4Author Commented:
No i have T SQL statements that were used to create index.

I want to know how to compare the missing index recommendations we got from DMV queries I used with the current indexes on that table.

How to determine whether we really need to apply these missing index  recommendations. We cannot just apply it as it is, as it can have some overlapping also
Chris LuttrellSenior Database ArchitectCommented:
There is no automatic way to compare them, the DMV query has basically already done that by giving you statements for MISSING INDEXES.  They are missing, therefore they will not be exact duplicates.
What you probably want to do is see if there are similar indexes that might be inhanced by adding a column to the index itself or adding included columns.  Also you want to check and not create both indexes like these in your example where the STYLE column would not be needed if it is the first column in the SACSE index:
index_name           	index_description	                                      index_keys
IDX_PRODUCT_SACSE	nonclustered located on PRIMARY                               STYLE, ACTIVE_FLAG, CMS_SITE_ID, START_DATE, END_DATE
IDX_PRODUCT_STYLE	nonclustered located on PRIMARY	                              STYLE

Open in new window

There is not an automated way to do this, it takes domain knowledge of your system and human reasoning, otherwise all this work would be automated already and there would be no such job as DBA or Database Developer.
Here are a couple excelent post from one of the foremost experts on this, Kimberly L. Tripp:
http://www.sqlskills.com/blogs/kimberly/use-this-new-sql-server-2012-rewrite-for-sp_helpindex/ (it says 2012, but read the article, it works with 2008 as well)
and Her main "Index Articles" jump page
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.