Leveraging the New SQL Server 2008 Activity Monitor Performance Dashboard

The SQL Server Activity Monitor is one of the first tools a DBA should leverage when there is need to gain a quick overview of a SQL Server 2008 system’s performance.  Activity Monitor has been completely rewritten in SQL Server 2008 and compared to its predecessors it is no longer limited to displaying Processes, Locks by Object, and Locks by Process.  Activity Monitor introduced a newly redesigned performance dashboard with intuitive graphs, performance indicators with drill down and filtering capabilities.  The new tool’s look and feel is very similar to the Resource Monitoring tool found in Windows Server 2008; however, the information captured and presented is broken down into 5 major sections dedicated to SQL Server performance monitoring. The sections, are Overview, Processes, Resource Waits, Data File I/O and Recent Expensive Queries.  Many SQL Server 2005 DBA have trouble launching the tool as they typically expect to see it under the Management node, however, that has changed.  The tool can be invoked by right-clicking a SQL Server instance within Object Explorer and specifying Activity Monitor. 

§    Overview Section – The first section is called Overview.  It provides a dashboard of initiative graphs and charts that illustrate the SQL Server systems Process Time, Waiting Tasks, Database I/O and Batch Requests/sec in real time. 

§    Processes Section – The next section in the dashboard is Processes.  When expanded a DBA can quickly monitor and asses the status of a given SQL Server process currently running on the system.    Activity Monitor displays a list of current processes on the server such as the session ID, the status of each process, who initiated it (by login ID), the database that the process is using, and the SQL command that the process is running. SQL Server assigns a unique number to each process; this unique number is called a server process ID (“spid”).  Moreover, metrics associated with each process ID is also presented.  A tremendous amount of data is presented in this section, therefore, it is possible to filter data by rows within a specific column.  By right-clicking a process a DBA can obtain more details, kill a process or trace the process directly in SQL Server Profiler. 

§    Resource Waits –  This section displays resource waits  vertically that are based on the following wait categories CPU, SQLCLR, Network I/O Latch, Lock, Logging, Memory, Buffer I/O, Buffer Latch and Compilation.  From a horizontal perspective, the Wait Time, Recent Wait Time, Average Waiter Counter and Cumulative Wait Time metrics are published for each Wait Category.  Similar to the Processes section, data can be filtered based on items within a column.

§     Data File I/O –  The Data File I/O section displays I/O activity for relational database files within the Database Engine.  It includes both the system and user databases.  Information is broken down by database and database file name.  In addition, MB/sec Read, MB/sec Written and Response Time (ms) are presented. 

§    Recent Expensive Queries – The final section in Activity Monitor is Recent Expensive Queries which provides DBAs the opportunity to capture the queries that are performing the worse and  negatively impacting a SQL Server instance.  Approximately 10 to 15 of the worst and most expensive queries are displayed in the performance dashboard.  The actual query is displayed with augmenting metrics such as Execution in Minutes, CPU ms/sec, Physical Reads/sec, Logical Write/sec, Logical Reads/sec, Average Duration in ms and Plan Count.  It is also possible to right-click the most expensive query and edit the query test or show the execution plan.

Enjoy!!!

Ross Mistry

Pre-order my latest title SQL Server 2008 Management & Administration