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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PadawanDBAOperational 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.