Megosztás a következőn keresztül:

  • Cikk

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.

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.