Monitoring with SQL Server Enterprise Manager

Administering SQL Server

previous page next page
Administering SQL Server

Monitoring with SQL Server Enterprise Manager

Use SQL Server Enterprise Manager to view the following information about current Microsoft® SQL Server™ activity:

  • Current user connections and locks.

  • Process number, status, locks, and commands that active users are running.

  • Objects that are locked, and the kinds of locks that are present.

If you are a system administrator, you can view additional information about a selected process, send a message to a user who is connected currently to an instance of SQL Server, or terminate a selected process.

Use the current activity window in SQL Server Enterprise Manager to perform ad hoc monitoring of an instance of SQL Server. This allows you to determine, at a glance, the volume and general types of activity on the system, for example:

  • Current blocked and blocking transactions.

  • Currently connected users on an instance of SQL Server and the last statement executed.

  • Locks that are in effect.

SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.

Here are icons and descriptions of the icons in the current activity window.

Icon Description
Current Activity gives process and lock information at a designated time. This information is a snapshot taken every time you open or refresh Current Activity. The time of the snapshot is displayed in the left pane. Current Activity provides information about the processes (connections) running, the locks a certain connection is holding or trying to acquire, and the current and waiting locks on databases and tables.
Process Info provides information about the current connections and activity in a system. A connection can be in three states:  running, sleeping, or background. The database context is also displayed. There are some server processes, which are started before the master database is brought online, that have no database context.
Running process that is waiting for a lock or user input.
Sleeping process that is waiting for a lock or user input.
Background process that wakes up periodically to execute work. SPID 2 (Lock Monitor), 3 (Lazy Writer) and 6 are background processes.
Process (SPID) that is blocking one or more connections.
Process (SPID) that is blocked by another connection.
Process that is not blocking or being blocked.
Process that is not blocking or being blocked.
Table lock. If an index is involved, the index name is listed in the index column. The resource locator of the locked part is displayed in the resource column.
Database lock.

Here are descriptions of the process information in the Current Activity window.

Item Description
Process ID SQL Server Process ID.
Context ID Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
User ID of the user who executed the command.
Database Database currently being used by the process.
Status Status of the process (for example, running, sleeping, runnable, and background).
Open Transactions Number of open transactions for the process.
Command Command currently being executed.
Application Name of the application program being used by the process.
Wait Time Current wait time in milliseconds. When the process is not waiting, the wait time is zero.
Wait Type Indicates the name of the last or current wait type.
Wait Resources Textual representation of a lock resource.
CPU Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.
Physical IO Cumulative disk reads and writes for the process.
Memory Usage Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
Login Time Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.
Last Batch Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which SQL Server startup occurred is displayed.
Host Name of the workstation.
Network Library Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. For more information, see Client and Server Net-Libraries.
Network Address Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.
Blocked By Process ID (SPID) of a blocking process.
Blocking Process ID (SPID) of processes that are blocked.

Here are descriptions of the lock information in the Current Activity window.

Item Type Description
spid spid Server process ID of the current user process.
ecid ecid Execution context ID. Represents the ID of a given thread associated with a specific spid.
Lock type RID Row identifier. Used to lock a single row individually within a table.
  KEY Key; a row lock within an index. Used to protect key ranges in serializable transactions.
  PAG Data or index page.
  EXT Contiguous group of eight data pages or index pages.
  TAB Entire table, including all data and indexes.
  DB Database.
Lock mode Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
  Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and then potentially updating resources later.
  Exclusive (X) Used for data modification operations, such as UPDATE, INSERT, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
  Intent Used to establish a lock hierarchy.
  Schema Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability (Sch-S) and schema modification (Sch-M).
  Bulk update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
  RangeS_S Shared range, shared resource lock; serializable range scan.
  RangeS_U Shared range, update resource lock; serializable update scan.
  RangeI_N Insert range, null resource lock. Used to test ranges before inserting a new key into an index.
  RangeX_X Exclusive range, exclusive resource lock. Used when updating a key in a range.
Status GRANT Lock was obtained.
  WAIT Lock is blocked by another process.
  CNVT Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.
Owner Owner The lock owner: xact (transaction), sess (session), or curs (cursor).
Index Index The index associated with the resource. If the index is clustered, you see the table name instead.
Resource RID Row identifier of the locked row within the table. The row is identified by a fileid:page:rid combination, where rid is the row identifier on the page.
  KEY Hexadecimal number used internally by SQL Server.
  PAG Page number. The page is identified by a fileid:page combination, where fileid is the fileid in the sysfiles table, and page is the logical page number within that file.
  EXT First page number in the extent being locked. The page is identified by a fileid:page combination.
  TAB No information is provided because the ObjId column already contains the object ID of the table.
  DB No information is provided because the dbid column already contains the database ID of the database.

To view current server activity

Transact-SQL

previous page start next page