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