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.

Activity Monitor CPU2 01

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.

Activity Monitor CPU2 02a


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.

Activity Monitor CPU2 03a

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

Activity Monitor CPU2 04a


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.

Activity Monitor CPU2 05a

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

Activity Monitor CPU2 06a


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

Activity Monitor CPU2 08a

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.

Activity Monitor CPU2 11a

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.