Activity Monitor

Undoubtedly, the Reliability and Performance Monitor is a great tool for administrators to monitor resource usage; however, an administrator should first leverage the SQL Server Activity Monitor, as shown in Figure 9.5, when needing to gain some quick insight into a SQL Server system’s performance. In SQL Server 2008, the Activity Monitor introduced a new performance dashboard with intuitive graphs and performance gauges with drill-down and filtering capabilities. The new tool’s look and feel is similar to the Reliability and Performance Monitor, but the information captured is broken down into five main sections dedicated to SQL Server performance monitoring.

Figure 9.5. Activity Monitor in SQL Server 2008 R2.

The sections are Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. In SQL Server 2008 R2, right-clicking a SQL Server instance within Object Explorer and specifying the Activity Monitor will launch the tool, as shown in Figure 9.5.

• Overview— Shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec), and the Number of Batch Requests/second.

• Processes— Lists all the active users who are connected to the SQL Server database engine. This is beneficial for administrators, because they can click any of the session IDs, run a SQL Server Profiler trace to capture all its activities, or even kill a specific process.

• Resource Waits— Displays resource waits vertically 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. Analogous to the Processes section, data can be filtered based on items within a column.

• Data File I/O— Displays disk-level I/O information related to all the data and log files of user and system databases. Administrators can use this to rapidly recognize databases that are performing badly because of disk bottlenecks.

• Recent Expensive Queries— The last section in Activity Monitor is Recent Expensive Queries. This section gives administrators the opportunity to capture the queries that are performing the worst and negatively influencing 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 show the execution plan.