Monitoring SQL  «Prev 

Monitoring with SQL Server - Enterprise Manager

Use SQL Server Enterprise Manager to view the following information about current Microsoft SQL Server activity:
  1. Current user connections and locks.
  2. Process number, status, locks, and commands that active users are running.
  3. 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:
  1. Current blocked and blocking transactions.
  2. Currently connected users on an instance of SQL Server and the last statement executed.
  3. Locks that are in effect.
SQL Server activity can be monitored using the sp_who and sp_lock system stored procedures.

  1. Spid: The system process identifier,
  2. User: The user that is connected,
  3. Database: the database that the user is in,
  4. Status: The status of the process

  1. Open Transaction: The number of nested transactions that the connection has open,
  2. Command: A high level representation of the last command that ran,
  3. Application: The name of the application that is connecting to SQL Server (This column only has a value if the application sets it).

  1. Wait Time: The length of time the connection has been waiting (if the connection is not waiting this is a zero.)
  2. Wait Type: identifies what type of resource the process was waiting for (Some of the common classes of waits are locks, latches, or SQL Server internal resources.)
  3. Wait Resource: Identifies the resource that the connection is waiting for,
  4. CPU: Cumulative CPU time for the process of all statements executed with the set statistics time option turned on

  1. Physical IO: The total number of reads and writes performed by the process,
  2. Memory Usage: The number of procedure cache pages allocated to this process (If the number is negative, the process is freeing pages belonging to another process.)
  3. Login Time: The date and time that the connection was made.

  1. Last Batch - the date and time that the connection performed an RPC or an execute statement,
  2. Host: The name of the computer that the client is connecting from

  1. Network Library: Identifies the netlib dll used to connect to the server ,
  2. Network Address: Identifies the client's network interface card,
  3. Blocked By : The SPID of the process that is blocking the current process.

Blocking: The number of processes waiting for this processes resources