I just laid hands on a great little guide to the Dynamic Management Views in SQL Server 2008.  It’s a great introduction / quick reference guide sort of book.  With it, as long as you know what information you’re looking for, you can jump right to the view that can help you find the data you’re looking for (these are the droids you’re looking for!)

When you go take the 70-433 you’re going to need to be familiar with what you sorts of information you can find in the different dynamic management views.

In the real world you’ll have access to the MSDN, and to your copy of the book on the left, but during an exam, you’ll have to rely on just your head.

So let’s see what you’ve got.

You’ve created several new tables in your database, and you have developers writing queries against those tables all the time.  But none of them are running very well.  If you’re me then if you have a query that isn’t returning hundreds of thousands of rows per second it isn’t fast enough.

What dynamic management view or views would you use to see if there are any indexes that the query optimizer thinks is missing?

sys.dm_db_missing_index_details

This DMV gives you detailed information on indexes the optimizer would have used, if they were defined.  In this view you can see the table (database_id + object_id can let you join to other views to get the names, or you can check the statement column), columns used for WHERE and ON clauses (equality_columns or inequality_columns), columns in the SELECT clause(included_columns).

sys.dm_db_missing_index_columns(@index_handle)

This is a Dynamic Management Function (DMF), rather than a DMV.  This means you have to use an APPLY operation rather than a join operation to join it to the missing index details.

SELECT 
     * 
FROM sys.dm_db_missing_index_details mid
CROSS apply sys.dm_db_missing_index_columns(mid.index_handle) mic
ORDER BY
     mid.index_handle

By connecting this DMF, you can get a row for each column missing from the index.  This is useful when you want to create the code to generate your CREATE INDEX statements.  You can loop through this list and you know whether to add the column to the index’ ON clause or INCLUDE clause.  Keep in mind a single column could appear as both an equality/inequality and an include column_usage.

That just means it could be used as both.  I usually use those columns in the ON clause, but you may want to try it both ways to see which one would be most effective for your queries.

sys.dm_db_missing_index_group_stats

This DMV would actually provide you the details on how effective this INDEX might be to your queries.  It includes the number of seeks and scans that could have been addressed with this index had it existed.  Higher numbers are better here.

It also shows the last time that index would have been used in a seek or scan, had it existed.

It also estimates the user cost and user impact.  The avg_total_user_cost is a measure of how much savings there would be with this index.  If you run a graphical execution plan for a query, you’ll notice when you roll over icons there, you see rows detailing cost.

The avg_total_user_cost is the amount of savings you’d see in those query costs, should you implement the index in question.  The avg_user_impact is an percentage estimate on how much the query costs would drop.

sys.dm_db_missing_index_groups

This simply maps the many-to-many relationship between missing index details and missing index group stats.

These are the DMVs (and one DMF) you’d need to use to identify missing indexes for your queries.  But be careful simply adding indexes just because the optimizer tells you to.  The optimizer is only guessing.  Just because it appears in these DMVs doesn’t mean you have to add it.

If you added 20 indexes to a table to speed up reads, you’re slowing down every insert and update to that table.  You have to ask yourself is this table read intensive, or write intensive?

Even if you’re only adding one index to a table, try to find the one that actually helps performance.  Test the options that these views suggest.  Compare their impact.  In the end, you have to use analysis to determine which indexes work for your situation.

Good luck, and may the Force be with you!