How do I obtain information using Activity Monitor?

Document ID : KB000053930
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

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:

Solution

Figure 1

  • Overview
    Shows graphical displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second).


  • Active User Tasks
    Shows information for active user connections to SQL Server Database Engine.


  • Resource Waits
    Shows wait state information.


  • Data File I/O
    Shows I/O information for database data and log files.


  • Recent Expensive Queries
    Shows information about the most expensive queries.

When you expand a pane, the Activity Monitor queries the instance for information. When you collapse a pane, the Activity Monitor stops all the activities related to querying for that pane. You can also expand one or more panes at the same time to view different kinds of activities on the instance.
You can customize the display of the columns that are included in Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries panes in the following ways:

  • Click the column heading and drag it to another location in the heading ribbon to rearrange the order of the columns.


  • Click the column name to sort on columns.


  • Click the drop-down arrow in the column heading, and then select a value to filter on one or more columns.

You must have VIEW SERVER STATE permissions to view the Activity Monitor in SQL Server 2005 and SQL Server 2008.