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.
|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.|
Here are descriptions of the process information in the Current Activity window.
|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.
|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.|
|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
To view the last command batch for a connection
To view the current locks
To send a message to a currently connected user
To terminate a process