SQL Server - Session Performance Metrics

The following performance metrics are displayed for the SQL Server that is being monitored.

Connection Statistics

Parameters	Description
Connection Time	Displays the time taken to get connected to the SQL database server.
Active Connections	Displays the number of current active connections to SQL database server.
Logins/Min	Displays the total number of logins started per minute in the SQL database server.
Logouts/Min	Displays the total number of logouts started per minute in the SQL database server.

Go To Top

SQL Statistics

Parameters	Description
Batch Requests/Min	Displays the number of SQL batch requests received by SQL server.
SQL Compilations/Min	Displays the number of SQL compilations.
SQL Recompilations/Min	Displays the number of SQL re-compiles.
AutoParams/Min	Displays the number of auto-parameterization attempts.
Safe Auto-params/Min	Displays the number of failed auto-parameterizations.
Unsafe Auto-params/Min	Displays the number of unsafe auto-parameterizations.

Go To Top

SQL Session Details

The Sessions Lock Details metric displays the "Session" details by default. You can also view "Session Lock Details" and "Session Block Details" by selecting from the pull-down menu.

Parameters	Description
PID	Displays the SQL server session ID running in SQL Server.
Status	Displays the status of the process in SQL Server. The possible values are: Dormant : SQL Server session that hasn't done any work in more than an hour. Running : SQL Server session running one or more batches. A session can run multiple batches when Multiple Active Result Sets (MARS) is enabled. Background : SQL Server session running a background task, such as deadlock detection. Rollback : SQL Server session which has a transaction rollback in process. Pending : SQL Server session waiting for a worker thread to become available. Runnable : The task in SQL Server session which is in the runnable queue of a scheduler while waiting to get a time quantum. Spinloop : The task in SQL Server session waiting for a spinlock (Spinlock is a lock where the thread waits in a loop, repeatedly checking until the lock becomes available) to become free. Suspended : SQL Server session is waiting for an event, such as I/O, to complete. For more information on Session Status metric, click here.
User Name	The login name used for executing the process.
Host	The host machine name from where the session has been established from.
Program	The application in SQL Server which has established the session.
Memory Usage	The number of pages in the procedure cache that are currently allocated to this process.
CPU Time (ms)	The cumulative CPU time for the process running in the SQL Server.
I/O	The cumulative Disk reads and writes for the process running in the SQL Server.
Blocked	Displays the ID of the session that is blocking the request. If this column is 0, then the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). If value is -2, then the blocking resource is owned by an orphaned distributed transaction. If value is-3, then the blocking resource is owned by a deferred recovery transaction. If value is -4, then the Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
DB Name	Displays the name of the database currently being used by the process.
Command Name	Displays the command that is being currently executed.
Query	Displays the query for the selected database session.
Kill	Allows you to kill the session of the selected database.

Go To Top

SQL Host Details

Parameters	Description
Host Connected to the SQL	Displays the hosts name connected to the SQL server.
No. of Connection	Displays the number of connections each host has to the SQL server.
Memory Usage	Displays the total memory usage by each host connected to the SQL server.
CPU Time (ms)	Displays the total CPU time (in milliseconds) of each host connected to the SQL server.
I/O	The cumulative Disk reads and writes for the process running in the SQL Server.

Go To Top

SQL Session Lock Details

Parameters	Description
PID	Displays the SQL Server sessions ID.
Status	Displays the status of the process in SQL Server. The possible values are: Dormant : SQL Server session that hasn't done any work in more than an hour. Running : SQL Server session running one or more batches. A session can run multiple batches when Multiple Active Result Sets (MARS) is enabled. Background : SQL Server session running a background task, such as deadlock detection. Rollback : SQL Server session which has a transaction rollback in process. Pending : SQL Server session waiting for a worker thread to become available. Runnable : The task in SQL Server session which is in the runnable queue of a scheduler while waiting to get a time quantum. Spinloop : The task in SQL Server session waiting for a spinlock (Spinlock is a lock where the thread waits in a loop, repeatedly checking until the lock becomes available) to become free. Suspended : SQL Server session is waiting for an event, such as I/O, to complete.
Lock Type	Displays the type of Lock.
User Name	The SQL login name used for executing the process in SQL Server.
DB Name	Displays the database currently being used by the process in SQL Server.
Object ID	Displays the Object ID associated the resource.
Host	The host machine on which the SQL Server is running.
Program	The application in SQL Server which has established the session.
Command Name	Displays the command that is being currently executed.
CPU Time (ms)	The cumulative CPU time for the process running in the SQL Server.
I/O	The cumulative Disk reads and writes for the process running in the SQL Server.
Memory Usage	The number of pages in the procedure cache that are currently allocated to this process.

Go To Top

SQL Session Blocked Lock Details

Parameters	Description
Holding ID	Displays the Blocked sessions ID.
Status	Displays the status of the process in SQL Server. The possible values are: Dormant : SQL Server session that hasn't done any work in more than an hour. Running : SQL Server session running one or more batches. A session can run multiple batches when Multiple Active Result Sets (MARS) is enabled. Background : SQL Server session running a background task, such as deadlock detection. Rollback : SQL Server session which has a transaction rollback in process. Pending : SQL Server session waiting for a worker thread to become available. Runnable : The task in SQL Server session which is in the runnable queue of a scheduler while waiting to get a time quantum. Spinloop : The task in SQL Server session waiting for a spinlock (Spinlock is a lock where the thread waits in a loop, repeatedly checking until the lock becomes available) to become free. Suspended : SQL Server session is waiting for an event, such as I/O, to complete.
Lock Type	Displays the type of Lock.
Hold User	Displays the user the Lock.
Wait User	Displays the blocked user waiting for the Lock.
DB Name	Displays the N ame of the database being used by the process.
Object ID	Displays the Object ID associated with the resource.
Hold Host	Displays the host machine holding the Lock.
Wait Host	Displays the Blocked Host waiting the Lock.
Host Command	Displays the Command being executed by the process holding the Lock.
Wait Command	Displays the Command to be executed by the process waiting for the Lock.
Memory Usage	The number of pages in the procedure cache that are currently allocated to this process.

Go To Top