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, shown in Figure 11.19, when needing to gain 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 11.19 The Activity Monitor

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

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

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

• Resource Waits—This section displays resource waits vertically, based on 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—This 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—This last 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.