Documentation forSQL Sentry

SQL Sentry Advisory Conditions

Introduction

Advisory Conditions are a Performance Analysis feature that allows for customized alerting based on performance counter metrics, query results, WMI queries, duration of events, and state changes. Advisory Conditions can be applied globally or at specific targets and instances.

Downloading the Advisory Conditions Pack

When opening the Conditions List at the global level, you'll be prompted to download the latest Advisory Conditions Pack. This pack contains predefined Advisory Conditions that help illustrate the functionality that the Advisory Conditions feature has to offer. If you decide not to download the Advisory Conditions Pack at this point, access it through the Download Latest Advisory Conditions Pack option in the Tools menu. If you choose to disable the automatic checking for Advisory Conditions Packs, checking can be re-enabled in User Preferences in the Updates section.

SQL Sentry Download Latest Advisory Conditions Pack

After downloading the Advisory Conditions Pack, you'll be prompted to apply actions. Selecting Yes opens the Actions Selector to apply actions to your new Advisory Conditions.

Note:  Scheduled evaluations won't occur until an action is assigned to the Advisory Condition.

Building an Advisory Condition

Additional Information: See the Building SQL Sentry Advisory Conditions article for a tutorial style walk through of building a condition.

Create an Advisory Condition by selecting Create Advisory Condition in the Conditions List, or right-click on the All Targets (Global) node, group node, target node, or instance node in the Navigator pane, and then, select Add Advisory Condition. Owner object of the Advisory Condition is dependent on where the Advisory Condition is defined. The following sections explain all the options available when creating an Advisory Condition

Navigator Pane Add Advisory Condition

Defining an Advisory Condition

In the Navigator pane, there are Advisory Condition folders located at various levels. All preconfigured Advisory Conditions are created at the global level. The following chart outlines the various levels and which types of conditions can be configured at those levels.

Location in Hierarchy Advisory Condition Types Available
Root
Global
Site
Target
  • Amazon RDS for SQL Server
  • Analysis Services
  • APS
  • Azure SQL Database
  • SQL Sentry (or SentryOne)
  • SQL DW
  • SQL Server
  • Trintri
  • VMware Host
  • Windows
SQL Server Instance
  • Windows/SQL Server
SSAS Instance
  • Windows/SSAS
Note:  While SQL Server and SSAS Advisory Conditions can be defined higher in the hierarchy, defining them at the instance provides the added benefit of being context aware allowing you to utilize performance counter instances specific to that instance and execute SQL queries within the context of that instance.

Importing and Exporting Advisory Conditions

You can import and export your any of your saved advisory conditions from the Global or Instance level Conditions List. Open the Navigator Pane, expand the Advisory Conditions node at the Global or Instance level, and then select Conditions List to open the Conditions List for your Advisory Conditions.  

Importing an Advisory Condition

Import a saved Advisory Condition by completing the following steps:

  1. Open the Navigator pane and expand the Advisory Conditions node at the Global or desired Instance level. Select Conditions List to open the Conditions List.
  2. Select File > Import Condition to open the file selector window.
  3. Navigate to the desired .condition file, and then select Open to import your Advisory Condition. 
  4. Select Yes to open the Actions Selector and add Actions to your Advisory Condition or select No if you don't want to add any Actions.

Success: You have imported an Advisory Condition to SQL Sentry!

Exporting an Advisory Condition

Export a saved Advisory Condition by completing the following steps:

  1. Open the Navigator pane and expand the Advisory Conditions node at the Global or desired Instance level. Select Conditions List to open the Conditions List.
  2. Select the Advisory Condition you want to export.  Select File > Export Condition to open the Save As window.
  3. Select Save to export and save your Advisory Condition. 

Success: You have exported an SQL Sentry Advisory Condition!

Using the PowerShell Module to Import and Export Advisory Conditions

You can use the SQL Sentry PowerShell Module to import, export, and remove Advisory Conditions in your SQL Sentry environment. 

Additional Information: For more information about using the PowerShell Module, see the SQL Sentry PowerShell Module article.

Advisory Condition Types

Type is the first option presented when creating an Advisory Condition. The type determines where the condition is evaluated and which metrics are available.

Type Description
Amazon RDS Instance Examples:  See the Amazon RDS conditions on GitHub such as Amazon RDS - High CPU 80% or Amazon RDS - % Free Space - Disk.
Analysis Services This condition is evaluated at the SSAS instance level and has access to Windows and SSAS performance metrics.

Examples:  See the SSAS conditions on GitHub such as SSAS Formula Engine Query Pool Job Queuing or SSAS Sustained Cache Evictions.
APS Examples:  See the DataWarehouse conditions on GitHub such as APS Inaccurate Statistics or APS High Memory Utilization Detected.
Azure SQL Database This condition is evaluated at the Azure SQL Database level and has access to Azure SQL Database performance metrics.

Examples:  See the community-submitted Azure SQL DB conditions on GitHub such as Azure SQL Server Operation Failed or Azure SQL DB High Resource Utilization.
Azure Synapse Analytics Examples:  See the DataWarehouse conditions on GitHub such as Azure DW High Memory Utilization Detected or Azure DW Distribution Skew Exceeds 10%.
SQL Sentry (or SentryOne) This condition type is evaluated against the SQL Sentry database and is used to query the SQL Sentry database for information.

Examples:  See the 'Incorrect Compatibility Level or Page Verification Check conditions in your installation or the Advisory Conditions Pack article.
SQL Server This condition is evaluated at the SQL Server instance level and has access to Windows and SQL Server performance metrics.

Examples:  See the 'Cost Threshold of Parallelism' Changed or High Avg Wait Time per User Session conditions in your installation or the Advisory Conditions Pack article.
Tintri Examples:  See the Tintri High VM Throttle or Tintri High Datastore Throttle Latency conditions in your installation or the Advisory Conditions Pack article.
VMware Host This condition is evaluated at the VMware Host level and has access to vCenter performance metrics associated with a VMware Host.
Windows This condition is evaluated at the Target level and has access to Windows performance metrics.

Examples:  See the High Context Switches or VMware High Ballooning conditions in your installation or the Advisory Conditions Pack article.

Advisory Condition Options

Option Description
Default Evaluation Frequency

Defines how often the condition is evaluated. The evaluation frequency can be adjusted individually throughout the hierarchy.

Note:  Scheduled evaluations won't occur until an action is assigned to the Advisory Condition.

Note:  Query data is cached for one minute in the monitoring service before it is written to the SQL Sentry database. If you have a condition that uses T-SQL queries, and you evaluate more often than once per minute (e.g. a DefaultEvaluation Frequency of 30 Seconds), the result data will be retrieved from the cache and may not be current. Wait at least one minute to evaluate (e.g. a Default Evaluation Frequency of every 1 Minute or 5 Minutes) to obtain a new result.

Trigger Threshold Defines how long the condition must be true before the associated action is taken.

Additional Information: See this blog post for detailed instructions on using the trigger threshold.
Severity Defines the setting of severity that can be used as a sorting parameter in the Events Log.

Important:  Advisory Conditions with a severity of High or Critical can also cause the Advisory Conditions folder in the Navigator pane to turn red when true.
Evaluation Timeout Defines the limit on how long each evaluation can run before a timeout occurs.
Maximum Instance Count Defines the number of results returned when the Any instance option is used in used in the comparison (instead of Value).

Additional Information: See this blog post for detailed instructions on using Maximum Instance Count, Any, and returning a key/value pair in the results to know which instance in the set of results met the defined condition.
Color Defines the color of the duration line that appears on the Performance Analysis Dashboard.

Highlight on Dashboard Chart

Defines which chart(s) will show the duration line (in the selected color from the options) on the Performance Analysis Dashboard. See the Send to Alerting Channels section of the Actions article for an example of how this appears when a condition evaluates to true.

Supported Versions

When creating an Advisory Condition in the higher levels of the hierarchy, restrict certain conditions to run only on designated versions of Windows and/or SQL Server. For example, if you wanted to restrict the condition to only evaluate on servers with Windows Server 2008 R2 and newer, put 6.1 in the minimum field.

Defining an Advisory Condition

Add Advisory Condition Editor

Select one of the following logical operators:

Logical Operator Description
And All conditions must be true for an action to occur.
Or One or more condition(s) must be true for an action to occur.
Not And One or more condition(s) must be false for an action to occur.
Not Or All conditions must be false for an action to occur.

Select the plus icon, and then select a comparison type or an existing Advisory Condition. For more information about logical operators, see the Logical Choice article. 

Note:  When creating a multi-statement Advisory Condition while using And as a logical operator, be conscious of the order of the logical operators so you don’t short circuit the condition. With the And operator, if the first statement is false, the subsequent statements will be skipped.
Warning:  If an Advisory Condition is altered, it is altered in all other Advisory Conditions that are using it as an existing condition.

The following table shows which value retriever types are available for the various comparison types. 

Value Retriever Types Comparison Types
Performance Counter
  • Numeric Comparison
  • Expression Left Side
  • Expression Right Side

Note:  Performance Counter instances are only available at the appropriate instance context for Additional AC Type Restrictions. 

Performance Counter Baseline
  • Numeric Comparison
  • Expression Left Side
  • Expression Right Side

Note:  User-defined baselines must exist on the instance for Additional AC Type Restrictions. 

SQL Server Query
  • Numeric Comparison
  • String Comparison
  • Date/Time Comparison
  • Expression Left Side
  • Expression Right Side

Note:  SQL Server only for Additional AC Type Restrictions. 

SQL Sentry Database Query (formerly SentryOne Database Query or Repository Query)
  • Numeric Comparison
  • String Comparison
  • Date/Time Comparison
  • Additional AC Type Restrictions
  • Expression Left Side
  • Expression Right Side
SSAS Query
  • Numeric Comparison
  • Expression Left Side
  • Expression Right Side
Resource Exhaustion
  • Windows Advisory Condition Type
  • Date/Time Comparison
  • Expression Left Side
  • Expression Right Side
Azure SQL Database Query
  • Numeric Comparison
  • String Comparison
  • Date/Time Comparison
  • Expression Left Side
  • Expression Right Side

Note:  Azure SQL Database only for Additional AC Type Restrictions. 

VMware Host

Note:  VMware Host only. 

WMI Query
  • Numeric Comparison
  • String Comparison
  • Additional AC Type Restrictions
  • Expression Left Side
  • Expression Right Side
Explicit Value
  • Numeric Comparison
  • String Comparison
  • Date/Time Comparison
  • Additional AC Type Restrictions
  • Expression Left Side
  • Expression Right Side
Last Value
  • Numeric Comparison
  • String Comparison
  • Date/Time Comparison
  • Additional AC Type Restrictions
  • Expression Right Side
Expression
  • Numeric Comparison
  • Additional AC Type Restrictions
  • Expression Left Side
  • Expression Right Side
Duration
  • Numeric Comparison
  • Additional AC Type Restrictions
  • Expression Left Side
  • Expression Right Side
Existing Condition
  • Additional AC Type Restrictions

Value Retriever Types

Type Description
Performance Counter

Performance Counters are available based on the type of condition that's being created. Conditions being built at the target and instance level are context aware and have access to specific counter instances. When defined at the target level or lower, use baselines that are associated with Performance Counters.

Note:  SQL Sentry provides virtual Performance Counters for use in Advisory Conditions. These virtual counters are gathered using stored procedures and may cause more overhead than counters collected through PerfLib. Virtual Performance Counters are identified by the lack of a colon. For example, SQL Server Data File Size is a virtual counter.

Performance Counter (User)

The Performance Counter (User) option allows for the selection of any performance counter. After selecting this option, select Connect, enter a server name, and then select Connect again. After connecting to the server, select the category field to view a list of all of the Performance Counter categories on that server. After selecting a category, select the counter that's to be evaluated.

Note:  Connecting to a server is only necessary to provide a list of Performance Counter categories and counters. During evaluation, the counter is evaluated in the context of each monitored instance.

Performance Counter Baseline Performance Counter Baselines are available when the Advisory Condition is created at the target or instance level. A user defined baseline must exist at the instance level to be used.
SQL Server Query Enter the database that the query runs against in the first field. If the condition is being defined at the instance level, there's a button next to the field that provides a list of databases available for that instance. The next field is for the query. The query can be entered manually or can be selected from a list of queries using the Query Editor. If a query returns multiple results, the first result is used for the evaluation.
Azure SQL Database Query Enter the database that the query runs against in the first field. The next field is for the query. The query can be entered manually or can be selected from a list of queries using the Query Editor. If a query returns multiple results, the first result is used for the evaluation.
SQL Sentry Database Query (formerly SQL Sentry Database Query or Repository Query) Allows you to perform a query against the SQL Sentry database. See the Availability Replicas Hosted on Same Virtual Host, Availability Replicas With Disks in Same Datastore, and SQL Sentry Monitoring Service Offline conditions as examples.

When writing a SQL Sentry Database Query, use the variables @ComputerID and @ConnectionID in your query to dynamically adjust depending on the computer or connection that the Advisory Condition is evaluating against.

@ComputerID = the [ID] column in the [dbo].[Device] table
@ConnectionID = the [ID] column in the [dbo].[EventSourceConnection] table

The error message "The query string must contain a reference to @ComputerID or @ConnectionID" appears if a required reference is missing.

SQL Sentry Database Query usage example:

SELECT ServiceConnectionStatus FROM dbo.EventSourceConnection WHERE ID = @ConnectionID

SELECT ServiceConnectionStatus
FROM dbo.EventSourceConnection C
INNER JOIN dbo.Device D
   ON D.ID = C.DeviceID
WHERE D.ID = @ComputerID
Resource Exhaustion Allows you to perform a check against a Relative Date Value when using the Forecasting feature. For more information on enabling Disk Forecasting, see the Disk Space and Monitoring Service Settings articles.
SSAS Query Allows you to issue DMV queries in Advisory Conditions. DMV queries offer additional insight into SSAS metadata, and can be executed as MDX or DMX queries. Enter a query string in the provided field, or use the Query Editor to enter the query manually.
WMI Query Allows you to query WMI using WQL. For more information, see the Querying with WQL article.
Explicit Value Allows for an explicitly defined value.
Last Value This is a right side specific option that returns the previous evaluations value of the left side of the argument.
Expression Using the expression option allows for basic mathematical operation to be applied to other values. Operations include addition, subtraction, multiplication, and division. These mathematical operations can be performed against any of the other numeric value options.
Duration

This option returns the amount of time elapsed to acquire the specified value. Various time units are available, ranging from milliseconds to days.

Note:  When using any query retriever types in conjunction with the Any qualifier to compare multiple results, the query is required to return a key/value pair in order to identify which value met the defined condition. This also ensures that conditions using queries on both sides of the condition, or comparing the last value, automatically matches values with the same key.

Query Editor

When using a query retriever type like the SQL Server or SQL Sentry Database query, select the button to the right of the query input box to launch the Query Editor.

SQL Sentry Query Editor

The Query Editor is where queries can be edited and saved for future use in Advisory Conditions.

Button Description
New Opens the New Query window.
Test Opens an instance dialog window. Specify the server and database name that the query is to be executed against. The resulting value is displayed in the field to the right of the test button.
Delete Deletes the selected query.
Note:  Only queries that have been tested and have a value data type can be selected for use from the Query Editor.

Conditions List

The Conditions List is accessed from various locations within the hierarchy of the Navigator pane. The number in parentheses beside the Advisory Conditions folder indicates how many Advisory Conditions are defined at that level within the hierarchy. An exception to this is the Advisory Conditions folder located beneath the Contacts node in the Navigator pane. The number here indicates the total number of Advisory Conditions defined across the environment.

Note:  What's seen in the Conditions List depends on where the list is opened within the hierarchy. Wherever the list is opened, the list contains Advisory Conditions defined at that level, its parent's levels, and children’s levels.

Conditions Pane

The Conditions pane provides a list of all available conditions. This pane also displays the owner object, creation date, and last modified date of the condition as well as which user modified it. Disable an Advisory Condition by unchecking the checkbox in the Enabled column. Also add tags to assist with sorting and organizing the conditions.

Select Add Advisory Condition to open Windows, Windows/SQL Server, or Windows/SSAS. Select the condition type to open the Condition Editor. For more information about adding a condition, see the Condition Settings topics. 

SQL Sentry Advisory Condition Editor

Select Edit Advisory Condition to open the Condition Editor for the condition that is currently selected in the Conditions pane.

Note:  You cannot edit an Advisory Condition if it was created by SQL Sentry. You must make a copy of the condition, and then you can edit the copy. Consider disabling the original Advisory Condition if you do not want it to execute going forward.

The Show Events Log button takes you to the Events Log filtered by the Advisory Condition that is selected in the Conditions list. 

SQL Sentry Events Log from Conditions List

The SQL Sentry ABC Icon row allows you to search in each row. SQL Sentry Conditions List Search

The following is a list of important columns to remember: 

Column Description
Type The target type applicable to the condition.
Object Actual name of the object where the condition was created (may be Global).
Object Type Specifies what the object is (e.g. Global, SQL Server, etc.).
Enable Allows you to enable or disable the Advisory Condition in each row.
Tags Used to organize the Advisory Conditions by keywords.

Context Menu

SQL Sentry Conditions List Context Menu Options

Task Description
Edit Allows for editing of the selected Advisory Condition.
Show Events Log Opens the Events Log filtered by the selected Advisory Condition.
Snooze > This Condition/All Objects Disables actions and alerting on the selected Advisory Condition for the selected period.
Snooze > All Conditions/All Objects Disables actions and alerting on all Advisory Conditions for the selected period.
Unsnooze > This Condition/All Objects Re-enables actions and alerting on the Advisory Condition.
Unsnooze > All Conditions/All Objects Re-enables actions and alerting for all Advisory Conditions.
Clone Creates a copy of the selected Advisory Condition with a new name with the owner object being in the context of the Conditions List.
Export Exports the selected Advisory Condition to a .condition file.
Import Imports an Advisory Condition from a .condition file.
Disable Disables the selected Advisory Condition.
Delete Deletes the selected Advisory Condition.

Evaluation Status Pane

The Evaluation Status pane displays the last evaluation for the selected condition. This pane shows the object that was evaluated, the result of the evaluation, the duration and time of the evaluation, and information pertaining to any errors that may have occurred. The bottom of the pane provides a count of the objects that were evaluated. Selecting an evaluation in this pane displays the results of that evaluation in the Evaluation Results pane. 

Evaluate Selected and Evaluate All

The Evaluate Selected button performs an evaluation of the Advisory Condition against the selected object in the Evaluation Status pane. The Evaluate All button performs an evaluation of the Advisory Condition against all legal targets.

Evaluate Selected and Evaluate All Options

With Logging

The Evaluate Selected and Evaluate All options have a drop-down menu which contains a With Logging option. The With Logging option generates an entry in the SQL Sentry Events Log if the condition doesn't have the Log to Alerting Channels action assigned to it.

Evaluation Status Evaluate Selected With Logging

To view events generated by the With Logging option, select Show Events Log from the Conditions List.

Conditions List Show Events Log

The Events Log displays entries for the conditions that evaluated to true when With Logging was selected for the evaluation.

SQL Sentry Events Log

Note:  The With Logging option is not the same as the Log to Windows Event Log Action. See the Actions article to learn more about logging to the Windows Event Log.

Context Menu

Conditions List Evaluation Status Context Menu Options

Task Description
Snooze This Condition/This Object Disables actions and alerting for the selected Advisory Condition on the object for the selected amount of time. 
Jump To Performance Analysis Dashboard Jumps to the time frame of the Advisory Condition event.
Show Events Log Opens the Events Log filtered by the selected Advisory Condition.

Evaluation Results Pane

The Evaluation Results pane, located at the bottom, shows the evaluation step results of an Advisory Condition along with any values retrieved through Performance Counters or queries. Hover over any of the values to open a tooltip with additional information like the duration of retrieving that value, the non-rounded value, error messages, and Performance Counter instance names.SQL Sentry Conditions List Evaluation Pane Results

Note

  • If you use the logical operator Any (instead of Value), the number of values that can be listed is tied to the Maximum Instance Count value (which can be up to 100).
  • Using the Any option requires two columns in the query selection to return a key/value pair in the list of results.
Additional Information: For a detailed explanation and example of using the Maximum Instance Count, Any, and two columns for a key/value pair, see this blog post.
Note:  Query data is cached for one minute in the monitoring service before it is written to the SQL Sentry database. If you have a condition that uses T-SQL queries, and you evaluate more often than once per minute (e.g. a DefaultEvaluation Frequency of 30 Seconds), the result data will be retrieved from the cache and may not be current. Wait at least one minute to evaluate (e.g. a Default Evaluation Frequency of every 1 Minute or 5 Minutes) to obtain a new result.

Events Log

Additional Information: For information about the Events Log, see the Condition Events Log article.