SQL User Processes Test
This test reports the number and state of sessions of each user who is currently connected to the Microsoft SQL server. Using the metrics reported by this test, administrators can promptly isolate idle sessions and suspended sessions, which are a drain on a server’s resources.
Target of the test : A Microsoft SQL server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for each user currently connected to the Microsoft SQL server monitored.
|
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Total processes: |
Indicates the total number of sessions currently open on the server for this user. |
Number |
|
Running processes: |
Indicates the number of sessions of this user that are currently active. |
Number |
The detailed diagnosis of this measure, if enabled, will provide the complete details of the active sessions of a particular user. Using this information, you can understand how each of the connections were made - i.e., using which program - and from where - i.e., from which host. |
Sleeping processes: |
Indicates the number of sessions initiated by this user that are currently idle. |
Number |
Ideally, the value of this measure should be low. A high value is indicative of a large number of idle sessions, which in turn causes the unnecessary consumption of critical server resources. Idle sessions also unnecessarily lock connections from the connection pool, thereby denying other users access to the server for performing important tasks. The detailed diagnosis of this measure, if enabled, will provide the complete details of the idle sessions of a particular user. Using this information, you can understand how each of the idle connections were made - i.e., using which program - and from where - i.e., from which host. |
Suspended processes: |
Indicates the number of sessions initiated by this user that are currently suspended. |
Number |
A session can switch to a suspended state if one/more processes triggered in that session could not continue executing; a possible reason for this could be that the processes are waiting for blocked rows or a blocked table to be released. The detailed diagnosis of this measure, if enabled, will provide the complete details of the suspended sessions of a particular user. |
Background processes: |
Indicates the number of background processes currently running for this user. |
Number |
The detailed diagnosis of this measure, if enabled, provides the details pertaining to the background processes currently executing. |
The detailed diagnosis of the Sleeping processes measure, if enabled, will provide the complete details of the idle sessions of a particular user. Using this information, you can understand how each of the idle connections were made - i.e., using which program - and from where - i.e., from which host.
Figure 1 : The detailed diagnosis of the Sleeping processes measure