Grant Fritchey wrote:
Generally speaking, the missing index recommendations taken from the DMV tables are useless. That's because they are not, and cannot be, linked to a given query. So you just have an index recommendation, divorced from context.
Very interesting. I rely on the dm views for the overwhelming majority of my tuning. For example, we have hundreds and hundreds of dbs and thousands and thousands of queries. And developers in different parts of the globe whose code I don't control. I don't have time to look at query plans for all of them. Moreover, a single query plan only tells me about that specific run of the query, not how it might run with other values specified in the WHERE clause.
I do often check the TOP (nn) worst queries -- particularly in the initial attempts at tuning a given instance -- and will look at those specifically if/as needed. But otherwise, to me the best overview of all activity is the dm view data.
Since the dm views are cleared on restart, as Grant noted, it's best if the instance has been up for at least 7 days, and of course longer than that is even better. I'd be leery of using dm data from only a day or two of uptime (unless the stats were very clear that a certain index was needed).
Now, yes, you do have to carefully analyze the dm data. And you certainly do not want to blindly create all (or often even most) of the indexes that are returned by the missing_indexes stats. It's an involved process that you improve at with experience. Can I give you any easy rules to follow? Not really.
There is one rule, however. For initial tuning, focus on getting the best clustered index on every table. The clus index is the single most important factor in overall table performance. After that you can work on the nonclus index(es).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!