SQL Engine Test

The SQL Engine test reports statistics related to the Microsoft SQL server engine.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every Microsoft SQL server monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.
  6. USER –If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

  7. password - The password of the specified user
  8. confirm password - Confirm the password by retyping it.
  9. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN 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 managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server.
  10. isntlmv2 - 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, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.
  11. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
  12. use sp monitor - By default, this flag is set to Yes, indicating that this test uses the sp_monitor stored procedure (by default) to pull out the required metrics from the target server. This stored procedure mandates the Sysadmin role - i.e., you should configure the test with the credentials of a user with the Sysadmin role, if you want the test to use the sp_monitor. Moreover, even if the required privileges are granted to the test, in some environments, the sp_monitor procedure may result in errors. Administrators of high-security Windows environments may not want to expose the credentials of their Sysadmin users. Neither would they want error-prone stored procedures to execute in their environment. In such environments therefore, you can use queries to extract the desired metrics from the Microsoft SQL server, instead of the sp_monitor procedure. To enable the use of queries, set this flag to No.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Cpu usage:

The percentage of time for which the server’s CPU was engaged in processing requests to the server

Percent

A high value of this measure indicates a heavy load on the server. If this value comes close to 100%, it could indicate a probable delay in the processing of subsequent requests to the server. The detailed diagnosis measures associated with the Background processes measure of the MsSqlSysProcesses test will help you identify the processes that are consuming excessive CPU resources.

I/O usage:

The percentage of time for which the server was engaged in performing input/output operations

Percent

 

CPU idle time:

The percentage of time for which the server was idle

Percent

A low value of this measure is indicative of high CPU utilization.

Packets received:

The rate at which input packets were read by the SQL server

Pkts/Sec

This measure is an indicator of the traffic to the server.

Packets sent:

The rate at which output packets were read by the SQL server

Pkts/Sec

This measure is an indicator of traffic from the server.

Packet errors:

The rate at which packet errors occurred

Errors/Sec

Ideally, this value should be 0.

Disk reads:

The rate of disk reads performed by the Microsoft SQL server

Reads/Sec

The value of this measure should be kept at a minimum, as disk reads are expensive operations. Ideally, data reads should be performed from the server cache and not directly from the disk. To ensure effective cache usage, allocate adequate memory to the Microsoft SQL server.

Disk writes:

The rate of disk writes performed by the Microsoft SQL server

Writes/Sec

The value of this measure should be kept at a minimum, as disk writes are expensive operations. Ideally, data should be written to the data cache and not directly to the disk. To ensure effective cache usage, allocate adequate memory to the Microsoft SQL server.

Disk I/O errors:

The rate of errors encountered by the Microsoft SQL server while reading and writing

Errors/Sec

Disk read/write errors are normally caused by the following reasons:

  • Semaphore contention
  • Excessive disk space consumption

 

Note:

Sometimes, this test may suddenly stop reporting values for the CPU usage, I/O usage, and CPU idle time measures. In such a scenario, search the agent error log for the "msg 8115 Arithmetic overflow error". This is a Microsoft error, which occurs if the Microsoft SQL server being monitored is not restarted for 49 consecutive days or more. For more details about this error, refer to the following links:

https://docs.microsoft.com/en-us/sql/t-sql/functions/cpu-busy-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/functions/io-busy-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

If you find the "msg 8115 Arithmetic overflow error"in the agent error log, then you may want to consider restarting the target server during maintenance.