Activity Monitor in SQL Server

The primary function of the Activity Monitor is to allow the DBA to quickly view any potential performance issues in the server, network or the database.  If any contentions are occurring between SPIDs, this is where the DBA can kill the process

     Viewing the Activity Monitor Dashboard you will notice four graphs which can help identity any abnormal activity in the Database.  Note that the refresh rate for each graph is 10 seconds; however it can be changed by right clicking on the graph

There are two basic ways to launch Activity Monitor. The first is to right-click the server in Object Explorer:

The other common way to launch it is from the Toolbar:

Here is what he Activity monitor looks like:

This dashboard also contain four panes where you can view more detailed information about each process inside the server

Processor Time:

The Processes pane gives information on what processes are running and what resources are being utilized etc.  You can right click this and view the Kill, Profiler, and Detail options

Waiting Tasks:

The number of tasks that are waiting for processor, I/O, or memory resources.  The Resource Waits pane details the processes waiting for other resources on the server

Database I/O: (Input/Output)

Information on data and log files for both system and user defined databases. Provides information on such things as the transfer rate in MB/Sec, data from memory to disk, disk to memory, or disk to disk. This allow the DBA to quickly view what is causing I/O contention

Batch Requests/sec:

This pane show the number of batches being received,

Expensive queries running, and Find the Most Time Consuming Code in your SQL Server Database. If you right click this pane, you can view the execution plan option

The various panes info

The Processes pane shows the information about the currently running processes on the SQL databases, who runs them, and from which application.  Hover over each header to show tool tip

Session ID – or SPID is a unique value assigned by Database Engine to every user connection.

User Process – 1 for user processes, 0 for system processes.

Task State – the task state, blank for tasks in the runnable and sleeping state

PENDING: Waiting for a worker thread.
RUNNABLE: Runnable, but waiting to receive a quantum.
RUNNING: Currently running on the scheduler.
SUSPENDED: Has a worker, but is waiting for an event.
DONE: Completed.
SPINLOOP: Stuck in a spinlock.

  • Wait Time (ms) – how long in milliseconds the task is waiting for a resource.
  • Wait Type – the last/current wait type
  • Wait Resource – the resource the connection is waiting for
  • Blocked By – the ID of the session that is blocking the task.
  • Head Blocker – the session that causes the first blocking condition in a blocking chain
  • Memory Use (KB) – the memory used by the task.
  • MB/sec Read – shows recent read activity for the database file
  • MB/sec Written – shows recent write activity for the database file
  • Response Time (ms) – average response time for recent read-and-write activity

The Recent Expensive Queries pane

Expensive queries are the queries that use much resources – memory, disk, and network

  • Executions/min – the number of executions per minute, since the last recompilation.
  • CPU (ms/sec) – the CPU rate used, since the last recompilation.
  • Physical Reads/sec, Logical Writes/sec, and Logical Reads/ Average
  • Duration (ms) – average time that the query runs

The list of Processes is more interesting. If you right-click any session, you get these options:

The Details link will show you the last command executed on that connection. Take note that this doesn’t mean it’s still running. You can also kill the process (obviously carefully), and you can connect SQL Server Profiler to the server and filter the session immediately, to see what it’s doing.

The columns are filterable.

They show you a list of values currently in that column, plus an All, and a choice of Blanks (rows with no value in this column) or NonBlanks (rows with anything in this column). They start as All.

For a simple example of using this though, we could pick sessions that have any type of command running, by choosing Task State of RUNNING.

One that I often use this view for is to look for blocking issues. Every process that’s blocked by another process will tell you that. Generally, what I’m looking for is the head of a blocking chain ie: who’s the main culprit that’s blocking everyone.

For that, I look for a value of 1 in the Head Blocker column. Unfortunately, the way it’s designed, you can’t select that value until there is a row with that value.

The Application Name, Database Name, and Login can all be pretty useful as well.

The Resource Waits section is only mildly interesting.

The information there is at a bit of a coarse level to be really useful to me. Note that on this system, Buffer I/O is top of the list, but the cumulative wait time (since the server restarted) is small. Over time, if the system has been up for a long time, you can start to get a feel for the main waits in here, but be aware that there are a lot of values that can appear in here, without actually being an issue.

The Data File I/O list is a little more interesting:

This will show you how busy each data and log file is, for all databases. I generally sort it by Response Time (ms) descending. The value here is then basically the latency for the I/O on that file. In this example, it’s 8 milliseconds. That’s ok.

The Recent Expensive Queries list is interesting. The information is available from the system DMVs but this puts some useful data in an easy to get location: