Page 177 - DCAP508_DATABASE_ADMINISTRATION

Unit 12: Tools and Techniques for Monitoring Performance

Here are descriptions of the process information in the Current Activity window. Notes
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.
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.
Contd.....

LOVELY PROFESSIONAL UNIVERSITY 171