Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor

share|improve this question
    
RE: " can we find the missing index using Database Engine Tuning Advisor"? Yes. That is one of its functions. You need to provide a workload for it to analyze though. –  Martin Smith Aug 27 '13 at 8:55
add comment

2 Answers

up vote 3 down vote accepted

SQL Server renders a handful of DVMs that can provide you with this information. See the TechNet reference on About the Missing Indexes Feature.

Mainly, you will be dealing with four DMVs:

Note: This is how you do it. But be very careful when considering "missing indexes". You could end up with a large amount of indexes and data manipulation will take a hit from that. This does not replace prudent index consideration/creation.

share|improve this answer
    
Worth pointing out Glenn Berry's diagnostic scripts (sqlserverperformance.wordpress.com/tag/dmv-queries) and Aaron's Don't just blindly create those "missing" indexes! (sqlperformance.com/2013/06/t-sql-queries/missing-index) –  Kin Aug 26 '13 at 14:40
add comment

If you are new to this have a look at Brent Ozar/Kendra Little's script sp_blitzindex. You get a very nice report on the state of the indexes in your database.

Are there any missing, maybe there are a lot of indexes that are unused, a lot of heaps maybe? All this is addressed and more in this report.

I use this frequently, copy the result to an excelsheet, make necessary changes, use color coding and add date of change. Then run it again a couple of weeks later and compare and so on. It is an easy way to get in to index tuning.

BlitzIndex (Do not forget the video tutorial)

Do not forget that an unused index is maybe only used once a year and could be very important, dialogue with developers is key to success.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.