Now that you have tried the script I presented last wednesday on your production server(s)…
Hi again and thank you for all replies!
Now that you aquainted yourself with the missing index dmv:s, it is time to set the tools into proper work!
The best use of the dmv:s is actually to get a quick notice about which table is having the most problem trying to stay up to date. The top table in the dmv list is having issues. Key key for you as dba is to find out WHAT exactly the problem IS…
I normally start by setting ut a (server) trace of table access (as I normally am not involved in the software development process, for some strange reason; google: “developer dba conflict”). By analyzign the trace I will get two major results:
1. The highest average CPU process
2. The most frequent process.
The highest CPU is normally located to a poorly designed stored procedure, which is a fun problem for a DBD to chew on, the second problem is something you will have to contact the dev. team to have corrected (Q: Why the “&# are you asking “SELECT * FROM table” 60 times per minute in your application, ever heard of caching?). Finally, you will NOT use the dmv:s to create your new indexes, but instead via the steps above find the slow parts of the stored procedure and feed these parts into the Database Enging Tuning Advisor, which is a MUCH more qualified tool than the dmv:s. Don’t forget, however, that the dmv:s will always tell you WHERE the problem lies, which the DETA will not.
Happy hunting!