Analyzing The Health Of Your SQL Server Using Activity Monitor

SQL Server Activity Monitor is a feature built into SQL Management Studio that allows you to get a real-time overview of the health of your SQL Server environment.  Monitoring the state of your server is a vital step to identifying and correcting performance concerns before they become serious issues.  Though most of the features within Activity Monitor can be performed manually using system stored procedures and querying various Dynamic Management Views, the Activity Monitor’s big advantage is that is provides user friendly graphical interface for monitoring the system and allows you to perform certain tasks without ever having to open a query window.

There are several ways to open Activity Monitor.  The first is to right-click on any connected server and then select Activity Monitor.

open_activity_monitor_01

You can also launch the Activity Monitor from the SQL Management Studio toolbar.  Whichever server you are currently connected to will, by default, be used by Activity Monitor when clicking it from the toolbar.

open_activity_monitor_02

Once you have opened the Activity Monitor you will see a four graphs that provide an overview of the server state.  Below the graphs are four additional panes that can be expanded to provide in-depth data regarding the overall server state. The options include Processes, Resource Waits, Data File I/O, and Recent Expansive Queries.  I will discuss each pane in a bit more detail below.

activity_monitor

The graphs will continue to monitor the server state as long as Activity Monitor is running.  As you can see in the above example, there was a spike in both the Waiting Tasks and Database I/O at the same time.  This was due to an agent job blocking several other processes.  Though minor, as it cleared up on it’s own after about 30 seconds, this is a good example of things to look for when using Activity Monitor.

As mentioned above, there are 4 additional panes that can be expanded below the Overview pane.  I will briefly discuss the function and available features within each of these options.

The Processes pane displays all current sessions on the server.  You can right-click on each individual session and you will see a list of actions that can be performed.  These actions include viewing the details of the session, killing the SPID or initiating a trace using SQL Server Profiler.  If you’re not familiar with SQL Server Profiler you can learn more about it in my post on The Fundamentals Of Using SQL Server Profiler To Trace SQL Activity.

The most useful of these options is viewing the details of the session.  After right-clicking the session and selecting Details you will see a window pop up containing the last T-SQL command batch to be initiated by that session.

process_session_details

You are also given the ability to kill the session directly from this screen.  Another method for killing a particular session outside of Activity Monitor is to use the KILL command followed by the session ID as shown below.

The Resource Waits pane displays useful information pertaining to a thread which is waiting on various resources to become available.  These resources include key items such as Memory, Network I/O, CPU and Transactions.  Here, you can see the statistics for total Wait Time and Recent Wait Time by ms/sec alongside the Average Wait Count and Cumulative Wait Time.  This can help you determine which resource is being restricted and better narrow down the cause of any potential issues.

resource_waits

Fortunately there are no major issues to be seen in the above screenshot.

The Data File I/O pane displays disk level I/O information by way of displaying a list of files and their activity levels on the server.  Along with the Database and File Name, you can also see the Read and Write statistics displayed in MB/sec along with the total Response Time displayed in milliseconds.  If a database is performing poorly due to an I/O bottleneck, it should be obvious when reviewing the data on this pane.

data_i_o_pane

The above screenshot will give you an idea of the output displayed on the Data File I/O pane.

The final pane displays Recent Expensive Queries.  These are queries that have high resource consumption over the past 30 seconds.  Similar results can be found by querying the sys.dm_exec_requests and sys.dm_exec_query_stats Dynamics Management Views as this is what the Activity Monitor uses to display this information.

A couple useful features are available when right-clicking on an individual record within this pane.  By right-clicking you can select to Edit Query Text or Show Execution Plan.  This will allow you to dig deeper into the query to determine if there are any opportunities for tuning as well as modify the script on the fly.

recent_expensive_queries_pane

Activity Monitor within SQL Server is a must-use feature for any database administrator.  Knowing how to diagnose potential issues and resolving them before they become bottlenecks is vital to maintaining a functional SQL Server environment.

In addition to the built-in Activity Monitor, there are numerous third party software suites that can allow you to dig even further into the overall health of your server. I recommend checking out Idera’s SQL Diagnostic Manager as well as Solarwinds’ Database Performance Analyzer.  Both offer a high level of customization for monitoring multiple instances of SQL Server.