Is there a Missing Index Report tool? (or better yet one that automatically adds the indexes?)

Can anyone point me in the direction of a Microsoft Tool or menu option inside SQL Server 2005 that displays all the "missing" indexes that I should add to make the system perform at optimal levels?

I'm assuming that to avoid full table scans that _every_ query column-combination that is ACTUALLY USED by my code over the course of a day should have an index created, right?

Over the course of the day I'd like to run a report that says:

In the last 24 hours you're missed these indexes:

Book.BookID
Book.UPC

Catalog.Name

Customer.PhoneNumber
Customer.CustomerType

etc.

It would be even cooler if you could press a button that says "Create these indexes now?"

Wouldn't this avoid hours of research?

Does Microsoft offer such a thing?  If not, what do folks do to make sure they don't miss any indexes?

Pete
LVL 2
ZuZuPetalsAsked:
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.

BrandonGalderisiCommented:
There's a DMV called sys.dm_db_missing_index_details you can select from.
0

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
BrandonGalderisiCommented:
0
mastooCommented:
You might not want indexes on everything.  For a given workload you'll want to look at the payback from an index to be sure it is worth adding.  Indexes take space and can slow down data modification.
0
BrandonGalderisiCommented:
Correct mastoo.  

You don't want to add an index on EVERY column.  

Additionally: you may want to look at sys.dm_db_index_operational_stats to see how your existing indexes are doing.

You may want to see if you have any indexes that have excessive overhead.
0
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 SQL Server 2005

From novice to tech pro — start learning today.