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.
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.
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. |
|
Target | Microsoft.SQLServer.Linux.DBEngine | ||
Category | Alert | ||
Enabled | False | ||
Alert Generate | True | ||
Alert Severity | Warning | ||
Alert Priority | Normal | ||
Remotable | True | ||
Alert Message |
|
ID | Module Type | TypeId | RunAs |
---|---|---|---|
DS | DataSource | Microsoft.SQLServer.Linux.DataSource.LongRunningQueries | Default |
GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
<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>