Skip to Content

Using custom SQL queries

PATROL for Microsoft SQL Server includes custom SQL queries that you can configure to monitor the health of a Microsoft SQL Server.

Success

Before you begin

  • Verify that the query syntax is correct and MS SQL Server login has valid permissions to run the query.
  • Queries that you run on the MS SQL Server might change/affect the MS SQL Server. Change can be defined as any create/update/remove operation.
  • Queries that you run on the MS SQL Server might cause a considerable amount of load on the MS SQL Server. We recommend you to use longer collection intervals when the query is large.
  • Verify that the PATROL for Microsoft SQL Server is not collecting the data that you want to gather by your custom query. A review of all monitor types and parameters (especially the Perfmon parameters) is recommended before configuring the custom query.
  • Even if you enable Monitor availability only configuration mode, PATROL for Microsoft SQL Server monitors all the configured custom queries.
  • Configuring multiple custom queries with a short Collection interval (min) might cause a high load on the SQL Server and also on the PATROL Agent.


The following KM attributes can be used to generate events:

  • Number of records – Displays the number of records (rows) returned by the query. 
  • Query status – Displays the query execution status whether it was successful or not.
  • Response time – Displays the response time the query took from start time till it returned the results.
  • Value – Displays the value of first row and first column only if it is numeric.

You can use the events generated by these attributes to determine how efficiently the SQL queries are handled in the Microsoft SQL Server.

For example, you can sample the number of locks in the Microsoft SQL Server by entering the following query:

SELECT count(*) from master..syslockinfo with (nolock)

By using this query, you can set a threshold for the Value attribute to identify an increase in the number of locks in the system.

Number of records

Set a threshold for this attribute to get a notification if the query returns a high number of records (rows) that exceed the threshold. By default, this attribute does not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. 

While configuring a policy in the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel. In the Custom SQL queries section.

Since PATROL for Microsoft SQL Server version 9.6.20, the query output is displayed as an annotation on the number of records metric.

Here is an example on how to sample the name of the Logins whose currently in disabled mode:

Custom SQL Query panel sample

image2018-11-9_12-15-21.png

Here is the complete query:

SELECT name FROM sys.server_principals with (nolock) where is_disabled = 1

The screenshots below show the output of the query with an annotation.

Number of records graph

image2018-11-9_12-16-13.png    

image2018-11-9_12-16-51.png

Suggested action

In case when the number is bigger than expected you should see annotation and if one of the Logins unexpectedly become disabled then in need to be enabled again.

Query status

Set a threshold for this attribute to get a notification if the query was successful or unsuccessful. By default, this attribute will not generate an alarm or warning event as the threshold is not set. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. 

To see more information about this attribute, see the Query status.

Query status graph

image2018-11-9_12-17-25.png

Suggested action

Check with the Microsoft SQL Server Management Studio tool (or other tool) and verify if the query syntax is correct and if  MS SQL Server login has correct permissions to run the query.

Response time

Set a threshold for this attribute to monitor the response time, in milliseconds, required by a query to retrieve data. By default, this attribute will not generate an alarm or warning event. For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy. To see more information about this attribute, see the Response time.

Response time graph

image2018-11-9_12-17-51.png

Suggested action

A high response time value can indicate delays occurring in the Microsoft SQL Server, check your Microsoft SQL Server performance.

Value

This attribute is used to return a numeric value. If the query result on the first row and column is a string which can be converted to a numeric value, the Value attribute displays the converted value, else the attribute will stay offline.

By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Value attribute.

Here is an example on how to set a threshold for the Value attribute to identify an increase in the number of sleeping sessions in the Microsoft SQL Server. 

SELECT count(*) from sys.dm_exec_sessions where status = 'sleeping'

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Value graph

image2018-11-9_12-18-8.png

Event generated by the KM

You can see the event in the TrueSight console in Monitoring > Events.

image2018-11-9_12-18-18.png

Suggested action

A higher value can indicate many sleeping sessions in the Microsoft SQL Server which can result in exhausting the max sessions limit.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC PATROL for Microsoft SQL Server 9.6