Activity Monitor

Overview Pane

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.

  Note

  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.

Activity User Tasks Pane

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.

Resource Waits Pane

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.

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:

• 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.

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:

• 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.

See Also

Tasks

Concepts