No more excuses of missing indexes with Activity Monitor in SQL Server 2008

The new version of Activity Monitor for SQL Server 2008 Management Studio has a new look and feel from the old one in 2005.

The new Activity Monitor has a Recent Expensive Queries pane that allows you to see which of your active queries based on the data in the dm_exec_requests DMV. By default the pane is sorted by CPU as indicated by the little down arrow in the header next to the CPU label.

 

In the example above, the first query in the list is using 822 milliseconds per second of CPU time – that’s a lot for my little virtual machine.

If you right click on the query line, you’ll see a new command called Show Execution Plan.

This command launches the Graphical Show Plan UI for the query based on the current plan in the query plan cache.

 

Without having to look very long at the execution plan, you can plainly see in the third line of the header – the green text indicating there is a missing index.

If you want to create the index, you can right click in the execution plan and select the “Missing Index Details…” command.

This command takes the missing index information stored in the plan to create a new editor window with the index information.

 

By using the T-SQL Editor’s Specify Values for Template Parameters dialog, you can specify the index name.

If everything looks good, you can remove the comments from around the index command and execute the statements.

The end result in this case resulted in a new query plan that reduced logical reads by 5 times.

This was a simulated scenario that I demonstrated here, but the process works the same way in your environment.

Like any tool, you need to look at your indexes holistically using the Database Tuning Advisor, but Activity Monitor gives you quick insight into your expensive queries.

Comments

• Anonymous
  January 13, 2009
  The comment has been removed
• Anonymous
  January 19, 2009
  The comment has been removed
• Anonymous
  May 15, 2009
  One of the attendees of my TechEd session “DAT315 - Manageability Series: Uncover Hidden Secrets of T-SQL