Use Activity Monitor to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.
Activity Monitor is a tabbed document window that has the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. When any pane is expanded, Activity Monitor is querying the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can also expand one or more panes at the same time to view different kinds of activity on the instance.
For the columns that are included in the Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries panes, you can customize the display in the following ways:
To rearrange the order of the columns, click the column heading and drag it to another location in the heading ribbon.
To sort a column, click the column name.
To filter on one or more columns, click the drop-down arrow in the column heading, and then select a value.
To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, a user must have VIEW SERVER STATE permission.
To view the Activity Monitor on a SQL Server 2000 server, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role.
To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.
This pane shows the following graphical displays of instance information:
% Processor Time
The percentage of elapsed time that the processor spends to execute non-idle threads for the instance across all CPUs.
The number of tasks that are waiting for processor, I/O, or memory resources.
The transfer rate, in megabytes per second, of data from memory to disk, disk to memory, or disk to disk.
The number of SQL Server batches that are received by the instance.
In addition to providing instance information, this pane also provides access to Activity Monitor options. By right-clicking Overview, you can select from the following options:
Lets you to set the update interval for Activity Monitor. This option configures the frequency in which Activity Monitor queries the instance for new information. When the update interval is set to less than 10 seconds, the time used to run these queries can affect server performance.
The update interval for Recent Expensive Queries is always 30 seconds.
Pauses Activity Monitor. This includes all Activity Monitor processes.
Restarts Activity Monitor after Pause is selected. This restarts all Activity Monitor processes.
Obtains current Activity Monitor information before the next update interval.
Activity User Tasks Pane
This pane shows information for active user connections to the instance, and includes the following columns:
Is a unique integer (int) that is assigned to each user connection when the connection is made.
Displays 0 for a system process and 1 for a user process. By default, the filter setting for this column is 1. This displays only user processes.
The SQL Server login name under which the session is currently executing.
The name of the database that is included in the connection properties of processes that are currently running.
The state of the task. For tasks in a runnable or sleeping state, the task state is blank. Otherwise, this can be one of the following values:
The kind of command that is being processed under the task.
The name of the application program that created the connection.
Wait Time (ms)
The time, in milliseconds, in which this task is waiting for a resource. When the task is not waiting, the wait time is 0.
The name of the last or current wait type.
The name of the resource that is needed.
If there are blocking sessions, the ID of the session that is blocking the task.
If there are blocking sessions, identifies the session that causes the first blocking condition. A value of 1 represents a head blocker for other sessions.
Memory Use (KB)
The amount of memory, in kilobytes, that is being used by the task.
The name of the computer that made the connection to the instance of SQL Server.
The name of the Resource Governor workload group for the session. For more information, see Managing SQL Server Workloads with Resource Governor.
Resource Waits Pane
This pane shows information about waits for resources, and includes the following columns:
The categories that accumulate wait type statistics. The individual wait types are shown in the Active User Tasks pane. For more information, see sys.dm_os_wait_stats (Transact-SQL).
Wait Time (ms/sec)
The wait time in milliseconds per second for all tasks that are waiting for one or more resources in the wait category since the last update interval.
Recent Wait Time (ms/sec)
The weighted average wait time in milliseconds per second for all tasks that are waiting for one or more resources in the wait category since the last update interval.
Average Waiter Count
The number of tasks that are waiting for one or more resources in the wait category at a typical moment during the last sample interval.
Cumulative Wait Time (sec)
The total amount of time in seconds that tasks have waited for one or more resources in the wait category since SQL Server was last started on the instance, or since DBCC SQLPERF was ran on the instance.
Data File I/O Pane
This pane shows information about the database files for the databases that belong to the instance. This pane includes the following columns:
The name of the database.
The name of the files that belong to the database.
Recent read activity, in megabytes per second, for the database file.
Recent write activity, in megabytes per second, for the database file.
Response Time (ms)
Average response time, in milliseconds, of recent read-and-write activity to the database file.
Recent Expensive Queries Pane
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. This pane includes the following columns:
The query statement that is being monitored.
The executions per minute for the query.
The rate of CPU use by the query
The rate per second of physical reads by the query.
The rate per second of logical writes by the query.
The rate per second of logical reads by the query.
Average Duration (ms)
Average duration in milliseconds of running this query.
The number of cached query plans for this query. A large number might indicate a need for explicit query parameterization. For more information, see Specifying Query Parameterization Behavior by Using Plan Guides.