MS SQL Server 2008 - New Features - SSMS Activity Monitor

New SQL Server Management Studio (SSMS) Activity Monitor

There are many new, awesome features in SQL Server 2008, but one I've been anticipating is something that should've been developed years ago--a graphical, near-real-time charts of performance indicators, such as CPU usage, wait states, I/O usage, etc. In SQL Server 2000, you had to run several system stored procedures, such as sp_who, sp_lock, sp_spaceused, and sp_monitor, to get an understanding of what was going on, and in SQL Server 2005, you have an activity monitor, but it's a grid consisted of rows and columns of data, not anything graphical. Another cool feature in v2008 is a listing of SQL queries deemed "expensive". With this information, you can better ascertain which queries might be impacting SQL Server performance.

Overview Performance Charts

With SQL Server 2008, you get a chart-based view of some of the more important performance indicators. The new Activity Monitor in SQL Server 2008 provides such as view. To get to it, simply right-click on a server in the SSMS Object Explorer pane, then click on Activity Monitor (contrast this with SQL Server 2005, in which you have to drill down into Management folder to get to AM). See the following image for a look Activity Monitor's Overview real-time charts.

Recent Expensive Queries and Other Features

This feature provides a near-real-time view of expensive queries, something that can help DBAs and developers get a handle on which queries might be impacting server performance, negatively. Other Activity Monitor features include Processes, Resource Waits, and Data File I/O, all in near-real-time views.

To adjust the refresh wait of all of the information in Activity Monitor (you can't set the refresh rate per individual section), simply right-click on on the Overview/charts area, click on Refresh Interval, then choose a rate.

Filters

You can filter the data in the grid sections by clicking on the filter indicator for the appropriate column and selecting the data/value to filter by, a very cool feature, as you might not want to view all of the information on the grid, only a select view/representation.

Backward Compatibility

SQL Server 2005's Activity Monitor will work with SQL Server 2005 but not with v2000 or earlier releases. One thing I noticed with RC0 is that it doesn't show Processor Time statistics for SQL Server 2005 servers.

Comparing SQL Server 2005's Activity Monitor

In SQL Server 2005, Activity Monitor is limited by Processes, Locks by Object, and Locks by Process, and none of them are graphical. You can't view all of the information on one form. To view each search, you right-click on Activity Monitor, typically in Object Explorer in SSMS, then choose a section. With SQL Server 2008's Activity Monitor, you get to view all of the performance-based information on one form, making it easier to view and manage. SQL Server 2008's new Activity Monitor is a much-welcomed feature!