• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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
0
ZuZuPetals
Asked:
ZuZuPetals
  • 3
1 Solution
 
BrandonGalderisiCommented:
There's a DMV called sys.dm_db_missing_index_details you can select from.
0
 
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now