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