MSSQL on Linux: Long Running Queries Alert Rule

Microsoft.SQLServer.Linux.AlertRule.LongRunningQueries (Rule)

This rule throws an alert if the execution time of any of running SQL queries has exceeded the specified threshold (in seconds). Note that this rule is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

This rule checks whether the execution time of any of the running SQL queries has exceeded the specified threshold (in seconds). Note that this rule is disabled by default. Use overrides to enable it when necessary.

Note that this rule doesn't provide the texts of executing queries due to security reasons.

The rule supports alert customization filtering with the following overrides:

Application Exclude List - for excluding the query with application name
Database Exclude List - for excluding the query with database name
Query Exclude List - for excluding the query with custom query text

These overrides support wildcards and can be used for excluding long running queries with the application name, database name, or the query text itself with comma-separated values. For example, use conditions like ‘*_test’ to exclude queries that end with _test, ‘Test*’ to exclude queries that start with Test, or ‘*test*’ condition to exclude queries that have a test entry in any part of the query text.

If an element should contain an asterisk (*) that is not a wildcard, double quote ("), or backslash (\), the element must be escaped with a backslash ’\’. For example, use conditions like ’Query\*3’ to exclude queries that have ’Query*3’ in the query text, use conditions like ’\\path\\to\\’ to exclude queries that have ’\path\to\’ in the query text, or ’"GO, WITH"’ condition to exclude queries that have a "GO, WITH" entry with a comma inside of the query text. Overrides with exclude lists can be used simultaneously.

For more information about the rule exclude list examples, see Long running queries monitoring.

Overridable Parameters

Name	Description	Default Value
Enabled	Enables or disables the workflow.	No
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	900
Priority	Defines alert priority.	1
Severity	Defines alert severity.	1
Synchronization Time	The synchronization time specified by using a 24-hour format. Can be omitted.
Threshold (seconds)	Threshold that defines the amount of time (in seconds) that is acceptable for a SQL query to be executing. Every time this threshold is exceeded, an alert is thrown.	60
Top number of queries	Threshold that defines the top number of queries with a long execution time will be shown in the alert.	5
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event if it cannot access the database during the specified period.	15
Application Exclude List	A comma-separated list of application names that must be excluded from monitoring. Wildcards are supported. For example, use conditions like ‘_app’ to exclude application names that end with _app, ‘App’ to exclude application names that start with App, or ‘app’ condition to exclude application names that have an app entry in any part of the name. For more examples, see SQL Server Operations Guide.
Database Exclude List	A comma-separated list of database names that must be excluded from monitoring. Wildcards are supported. For example, use conditions like ‘_test’ to exclude database names that end with _test, ‘Test’ to exclude database names that start with Test, or ‘test’ condition to exclude database names that have a test entry in any part of the name. For more examples, see SQL Server Operations Guide.
Query Exclude List	A comma-separated list of queries that must be excluded from monitoring. Wildcards are supported. For example, use conditions like ‘_query’ to exclude queries that end with _test, ‘Query’ to exclude queries that start with Query, or ‘query’ condition to exclude queries that have a query entry in any part of the query text. For more examples, see SQL Server Operations Guide.

Element properties:

Target

Microsoft.SQLServer.Linux.DBEngine

Category

Alert

Enabled

False

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Remotable

True

Alert Message

MSSQL on Linux: Query execution duration exceeded the threshold

The longest execution time of a query on SQL Server instance "{1}", computer "{0}" has exceeded the specified threshold. See the "Alert Context" tab for more details.

Member Modules:

ID	Module Type	TypeId	RunAs
DS	DataSource	Microsoft.SQLServer.Linux.DataSource.LongRunningQueries	Default
GenerateAlert	WriteAction	System.Health.GenerateAlert	Default

Source Code:

<Rule ID="Microsoft.SQLServer.Linux.AlertRule.LongRunningQueries" Target="SqlDiscL!Microsoft.SQLServer.Linux.DBEngine" Enabled="false">

  <Category>Alert</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Microsoft.SQLServer.Linux.DataSource.LongRunningQueries">

      <MachineName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

      <NetbiosComputerName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

      <InstanceName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

      <Threshold>60</Threshold>

      <TopQueries>5</TopQueries>

      <ExcludedDBs/>

      <ExcludedQueries/>

      <ExcludedApps/>

      <ConnectionString>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

      <InstanceVersion>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

      <InstanceEdition>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

      <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

      <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

      <TimeoutSeconds>300</TimeoutSeconds>

      <IntervalSeconds>900</IntervalSeconds>

      <SyncTime/>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="GenerateAlert" TypeID="Health!System.Health.GenerateAlert">

      <Priority>1</Priority>

      <Severity>1</Severity>

      <AlertMessageId>$MPElement[Name="Microsoft.SQLServer.Linux.AlertRule.LongRunningQueries.AlertMessage"]$</AlertMessageId>

      <AlertParameters>

        <AlertParameter1>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>

        <AlertParameter2>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>

        <AlertParameter3>$Data/Property[@Name='MaxDurationSec']$</AlertParameter3>

        <AlertParameter4>$Data/Property[@Name='RequestList']$</AlertParameter4>

      </AlertParameters>

      <Suppression>

        <SuppressionValue/>

      </Suppression>

    </WriteAction>

  </WriteActions>

</Rule>