Export (0) Print
Expand All

Activity Monitor

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.

Waiting Tasks

The number of tasks that are waiting for processor, I/O, or memory resources.

Database I/O

The transfer rate, in megabytes per second, of data from memory to disk, disk to memory, or disk to disk.

Batch Requests/sec

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:

Refresh interval

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.

NoteNote

The update interval for Recent Expensive Queries is always 30 seconds.

Pause

Pauses Activity Monitor. This includes all Activity Monitor processes.

Resume

Restarts Activity Monitor after Pause is selected. This restarts all Activity Monitor processes.

Refresh

Obtains current Activity Monitor information before the next update interval.

This pane shows information for active user connections to the instance, and includes the following columns:

Session ID

Is a unique integer (int) that is assigned to each user connection when the connection is made.

User Process

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.

Login

The SQL Server login name under which the session is currently executing.

Database

The name of the database that is included in the connection properties of processes that are currently running.

Task State

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:

  • Background

  • Running

  • Suspended

Command

The kind of command that is being processed under the task.

Application

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.

Wait Type

The name of the last or current wait type.

Wait Resource

The name of the resource that is needed.

Blocked By

If there are blocking sessions, the ID of the session that is blocking the task.

Head Blocker

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.

Host Name

The name of the computer that made the connection to the instance of SQL Server.

Workload Group

The name of the Resource Governor workload group for the session. For more information, see Managing SQL Server Workloads with Resource Governor.

This pane shows information about waits for resources, and includes the following columns:

Wait Category

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.

This pane shows information about the database files for the databases that belong to the instance. This pane includes the following columns:

Database

The name of the database.

File Name

The name of the files that belong to the database.

MB/sec Read

Recent read activity, in megabytes per second, for the database file.

MB/sec Written

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.

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:

Query

The query statement that is being monitored.

Executions/min

The executions per minute for the query.

CPU (ms/sec)

The rate of CPU use by the query

Physical Reads/sec

The rate per second of physical reads by the query.

Logical Writes/sec

The rate per second of logical writes by the query.

Logical Reads/sec

The rate per second of logical reads by the query.

Average Duration (ms)

Average duration in milliseconds of running this query.

Plan Count

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft