This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

Continuing our journey through some of the features of Microsoft SQL Server Management Studio, we will today look at a feature that is very helpful in getting a quick overview of the SQL Server performance. The Activity monitor has been much talked about and is generally a starting point for most performance troubleshooting projects, and hence I was quite surprised when this one came in the “Underappreciated features” list.

In any case, here’s a quick overview (more like a crash course) on the various features of Activity Monitor. Please excuse the slightly higher load-time – the Activity Monitor is best explained visually.

The Activity Monitor

Accessing the Activity Monitor

The activity monitor can be accessed in multiple ways. However, the first one I would particularly recommend for DBAs whose most important activity upon connecting to a server is to view it’s ongoing activity or those who connect to a server to troubleshoot performance issues.

At Startup (Tools –> Options)	From the SSMS (On the toolbar)	From the Object Explorer (Right click on the server instance name)

Navigating through the Activity Monitor

Activity Monitor is a tabbed document window that has the following expandable and collapsible panes:

1. Overview
2. Active User Processes/Tasks
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries

Overview

The active user processes are represented in the Activity Monitor in terms of a percentage (%) value. This is obtained by monitoring the SQL Server process “sqlserv.exe” spread over all of the available CPUs, not for the entire server.

Active User Processes/Tasks

The information on this pane is similar to the sp_who2 and to a combination of the DMVs sys.dm_exec_sessions and sys.dm_exec_requests.

This pane shows information for active user connections to the instance. You can perform the following tasks on any particular SPID from this view:

1. Monitor in detail in the SQL Server Profiler
2. KILL the process

Here’s how to monitor a process in the SQL Server Profiler:

Let’s say I have two instances of SSMS running against a SQL Server. One is running a SPID of (54) and the other a SPID of (58). You can use the following query to get the SPID of the process you are running:

SELECT @@SPID

Simply right-click on the row with the required SPID and chose “Trace Process in SQL Profiler”. All operations done on SPID (54) can now be monitored via the Profiler.

Choosing a process to monitor	SQL Server Profiler

Here’s how to kill a process from the Activity Monitor:

The Activity Monitor is the best tool to start digging into performance issues that you might be experiencing with your server. In such a case, once you have identified and monitored the offending process, you might want to kill it. Instead of going to a new SSMS query window, you can kill the process from the Activity monitor itself.

Choosing a process to Kill	Attempting to fire a query in the SSMS query window using SPID (54)

Resource waits

This internally uses the DMV – sys.dm_os_wait_stats. Resource waits measure the amount of time a worker thread has to wait until it can gain access to the resources on the server that it needs, such as memory or CPU. A high resource wait time might indicate a resource bottleneck.

TIP: A useful feature that all panes in the Activity Monitor provide is filtering. Simply click on the drop-down boxes at that top of each column. You can also sort the view as per your requirement.

Data File I/O

As the name itself indicates, this pane shows information about the database files for the databases that belong to the instance. If you want to find out the most actively database, this is the place to look in.

Recent Expensive Queries

From Books-On-Line, this pane shows information about the most expensive queries that have been run on the instance over the last 30 seconds. The information is derived from the union of sys.dm_exec_requests and sys.dm_exec_query_stats, and includes queries in process and queries that finished during the time period.

Once you have boiled down to a query being the root cause of a performance problem, this pane will help you identify the problem query/set of queries.

As I was writing this post, I only ran one query on my test server instance. Hence, I get only one row in the Recent Expensive Queries pane.

Here’s the most interesting & useful part. The pane allows you to view the entire query, and also view the associated graphical execution plan.

Options (Right click on the required query)
Viewing the Query Text
Viewing the Execution Plan

TIP

You might miss the fact that tool-tips are available for your assistance throughout the Activity Monitor, providing you with useful information on what you are seeing. Some tool-tips even tell you the name of the DMV that populates the particular information on the Activity Monitor.

Permissions

Here is a quick summary of the permissions that one needs in order to use the Activity Monitor. More details on how to selectively apply security to the Activity Monitor is available from Pinal’s blog here.

Operation	Permission
Viewing the Activity Monitor	VIEW SERVER STATE
Killing a process	User must be a member of the sysadmin or processadmin fixed server role

Word of caution

The Activity Monitor continuously polls the SQL Server via the DMVs for refreshing the data on-screen. Running the Activity Monitor for extended periods of time will cause performance issues on the server. Ironically, the very tool that helps you to troubleshoot performance issues can be responsible for creating these issues. Hence, use it in moderation.

Before you go, if you actively use Activity monitor or have used it in the past, I would be curious to know the scenario under which you used it (minus the business specific details of course). Please do leave your feedback, I appreciate it.

Until we meet next time,

Be courteous. Drive responsibly.