Azure SQL System Processes Test

This test reports details about the system processes running on the target Azure SQL database.

Target of the test : A Microsoft Azure SQL database

Agent deploying the test : A remote agent

Outputs of the test : One set of results for the Azure SQL database that is configured for monitoring

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Database Name	Specify the name of the Azure SQL database that is to be monitored.
User Name and Password	Against the User Name and Password parameters, specify the credentials of the user who is vested with DBOWNER rights to the configured Database Name.
Confirm Password	Confirm the specified Password by retyping it here.
SSL	If the Azure SQL database service being monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Domain	By default, none is displayed in this text box. If the ‘SQL server and Windows’ authentication has been enabled for the Azure SQL database being monitored, then the Domain parameter can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the Domain text box, specify the Windows domain in which the monitored database exists. Also, in such a case, the User Name and Password that you provide should be that of a 'domain user' with DBOWNER rights to the configured Database Name.
IS NTLMv2	In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, this flag is set to No, indicating that NTLMv2 is not enabled by default for the target Microsoft Azure SQL database. Set this flag to Yes if NTLMv2 is enabled for the target database.
Detailed Diagnosis	To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option. The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled: The eG manager license should allow the detailed diagnosis capability Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Total processes	The total number of SQL processes	Number	The value of this measure is the sum of the number of background, running, sleeping, rollback, and suspended processes.
Background processes	The total number of background processes run by the Azure SQL service rather than by a user process	Number	The detailed diagnosis of this measure, if enabled, provides the details pertaining to the background processes currently executing.
Running processes	The total number of running processes	Number	The detailed diagnosis of this measure, if enabled, provides details such as the ID of the running processes, the user executing each of the processes, the database on which every process is executing etc. Note that while the query used by the eG agent for collecting the metrics of this test will be counted as a Running process, the detailed diagnosis of this measure will not include this eG query.
Sleeping processes	The total number of sleeping processes	Number	The detailed diagnosis of this measure, if enabled, provides details such as the ID of the sleeping processes, the user executing each of the processes, the database on which every process is executing, the sleep status, sleep time etc.
Rollback processes	The total number of processes that were rolled back	Number	The detailed diagnosis of this measure, if enabled, reveals information such as the ID of the rolled back processes, the user executing each of the processes, the database on which every process is executing, etc.
Blocked processes	If a process attempts to access a resource that is already in use by another process, then such a process will be blocked until such time that the other process releases the resource. This measures indicates the total number of blocked processes.	Number	The detailed diagnosis of the Blocked processes measure, if enabled, reveals information such as the ID of the blocked processes, the user executing each of the processes, the database on which every process is executing, the waiting time of the blocked process, etc. These details aid the user in identifying the blocked processes, the processes that are blocking them (i.e. the process that currently holds a lock on the resource), and also the duration for which the processes have been blocked. If a process is found to hold a lock for too long a time, then such processes can be killed so as to free the resource for the corresponding blocked process.
Suspended processes	Indicates the number of processes that are currently suspended.	Number	Azure SQL marks a process as “suspended” when the process has made a request to a non-SQL process or resource and is awaiting a response. This happens a lot when you have slow disk drives; processes will be suspended while Azure SQL waits for the drive to return data or report back after committing. Ideally, the value of this measure should be low. The detailed diagnosis of this measure, if enabled, will provide the complete details of the suspended processes.
Rollback processes	Indicates the number of sessions initiated by this user in which transaction rollbacks are in progress.	Number	Ideally, the value of this measure should be low. If this value is very close to the Total processes value for a user, it indicates that many transactions executed by that user are being rolled back. This is a cause for concern, as rollbacks are expensive operations that need to be kept at a minimum; if not, processing overheads increase and the overall performance of the service deteriorates. The detailed diagnosis of this measure, if enabled, will provide the complete details of the user sessions with transaction rollbacks.
Dormant processes	Indicates the number of processes being reset by Azure SQL.	Number	Ideally, the value of this measure should be low. If this value is high, it indicates that many processes are being reset. The detailed diagnosis of this measure, if enabled, will provide the complete details of the reset processes.
Pending processes	Indicates the number of processes that are waiting for a worker thread to become available.	Number	A low value is desired for this measure. If the value of this measure is high, it indicates that many are unable to execute owing to the lack of worker threads. Use the detailed diagnosis of this measure to know which processes are waiting for worker threads.
Spinloop processes	Indicates the number of processes that are waiting for a spinlock to free.	Number	Spinlocks are lightweight synchronization primitives which are used to protect access to data structures. They are generally used when it is expected that access to a given data structure will need to be held for a very short period of time. When a thread attempting to acquire a spinlock is unable to obtain access it executes in a loop periodically checking to determine if the resource is available instead of immediately yielding. After some period of time a thread waiting on a spinlock will yield before it is able to acquire the resource in order to allow other threads running on the same CPU to execute. This is known as a backoff. The detailed diagnosis of this measure will reveal the processes that are waiting for spinlock to free.