Documentation forSQL Sentry

SQL Sentry Azure Synapse SQL Pool Overview

The Azure Synapse SQL Pool Performance Monitoring Solution

SQL Sentry Azure Synapse SQL Pool monitors the performance of your Azure Synapse SQL Pools (formerly Azure SQL Data Warehouses) so you can deliver accurate, business-critical information to your end users. SQL Sentry Azure Synapse SQL Pool provides detailed visibility into the queries, loads, backups, and restores of all your data. It includes features such as the Event Calendar and intelligent movement dashboard (shown below), allowing you to discover what's impacting your workload. 

Data Movement Dashboard

SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard

Using the SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard allows you to identify data movement activity quickly and easily. Zoom-in on key periods of activity, go back in time to any specified time, and Jump To other features within the product. The Data Movement Dashboard contains the following charts that provide an at a glance view of your environment:

Graph Description
Distribution Activity  Displays a graphical representation of the distribution activity for a selected metric across compute nodes within your environment in milliseconds. Each color on the graphs represents an individual compute node in your environment. 
Node Activity Displays a graphical representation of the node activity for a selected metric across the compute nodes within your environment. Each color on the graph represents an individual compute node in your environment. 
Data Movement CPU (ms) Displays a graphical representation of data movement CPU activity across the compute nodes within your environment in milliseconds. Each color on the graph represents an individual compute node in your environment.
Elapsed Time (ms) Displays a graphical representation of elapsed time of data movement across the compute nodes within your environment in milliseconds. Each color on the graph represents an individual compute node in your environment.
Rows Processed Displays a graphical representation of the rows processed across the compute nodes within your environment in milliseconds. Each color on the graph represents an individual compute node in your environment. 
MB Processed Displays a graphical representation of MB processed across the compute nodes within your environment. Each color on the graph represents an individual compute node in your environment.
MB/Sec Displays a graphical representation of MB processed per second across the compute nodes within your environment. Each color on the graph represents an individual compute node in your environment. 
Note:  Selecting a graph on the bottom pane automatically updates and displays the selected metric data on graphs in the top pane. For example, select the bottom pane graph Rows Processed to show detail data for Distribution Activity: Rows Processed and Node Activity: Rows Processed in the top pane graphs.  

SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard related graphs

Zooming in to Pinpoint Periods of Activity

Zoom-in to pinpoint time periods where activity is the highest in your Azure Data Warehouse environment.

Using Click and Drag

Zoom in to specific periods on a desired graph using Click and Drag by completing the following steps:

  1. Click and drag the cursor over a period of interest on a bottom pane graph.
    SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard use click and drag
  2. Release the cursor, and select Zoom In from the context menu.
    SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard Zoom In

Success: You have zoomed into a selected time period.

SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard Zoom In

Selecting a time period on one bottom pane graphs, selects the same time period across all graphs. This allows you to identify correlating issues within your environment.

SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard highlighted graphs

Using the Toolbar

Zoom in or Zoom out of a time period by using the Zoom in and Zoom out toolbar buttons.

Toolbar button Image
Zoom In SQL Sentry Azure Synapse SQL Pool toolbar Zoom In
Zoom Out SQL Sentry Azure Synapse SQL Pool toolbar Zoom Out

Navigating to a relevant time period

Review relevant historical data, or get real-time updates on the metrics within your Azure Data Warehouse environment with SQL Sentry Azure Synapse SQL Pool. 

Using the Toolbar

Navigate to a specific time period on the Data Movement Dashboard by completing the following steps:

  1. Select a start-date from the Start drop-down list on the toolbar, and then select a Start time from the drop-list. SQL Sentry Azure Synapse SQL Pool toolbar time navigation
  2. Select an end-date from the End drop-down list on the toolbar, and then select an end time from the drop-list. SQL Sentry Azure Synapse SQL Pool toolbar time navigation
  3. Select Go on the toolbar to display your desired time period. SQL Sentry Azure Synapse SQL Pool toolbar time navigation

Success: Your desired time frame displays in the Data Movement Dashboard.

SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard selected timeframe

Using Jump to

Easily toggle between other SentryOne diagnostic and optimization tools using the Jump To context menu item. Use Jump to by completing the following steps:

  1. Highlight the desired time frame in a bottom pane graph. SQL Sentry Azure Synapse SQL Pool Data Movement Dashboard highlight a graph
  2. Right click the highlighted section, and then select Jump to > ( Calendar, Distributed Queries, Or Loads/Backups/Restores)  from the context menu. SQL Sentry Azure Synapse SQL Pool Jump To

Distributed Queries Tab

View details of each Massively Parallel Processing (MPP) query. Filter, sort, and group historical information, including query step details.

SQL Sentry Azure Synapse SQL Pool Distributed Queries tab

Loads, Backups, and Restores Tab

View information about both in-process and historical events through the Loads, Backups, and Restores grid.

SQL Sentry Azure Synapse SQL Pool Loads/Backups/Restores tab

Additional Features

Intelligent alerting

Save time by using Advisory Conditions that notify you about overall data system health, distributed query performance thresholds, as well as load and backup performance.

The following is a list of the most common issues that impact the performance of an Azure SQL Data Warehouse and an associated advisory condition in SQL Sentry Azure Synapse SQL Pool:

Advisory Condition Description
Outdated Statistics Outdated statistics can be a root cause of performance issues in Azure DW because updating statistics is a manual process on these platforms, and if statistics are up to date on the individual compute nodes, they may not be caught up on the control node. This advisory condition provides an effective way to determine if statistics are out of date by comparing row count estimates between the control node and the compute nodes for each table in a database. By default, this condition evaluates to true if there's at least 20 percent discrepancy on any table. Note:  When configuring this advisory condition for your environment, be sure to import at the individual target level, not All Targets. Update the database name to the database you wish to evaluate. This condition only evaluates row counts and not cardinality. It doesn’t consider whether statistics exist on more than one column, nor for which column(s) the statistics exist.
Data Distribution Skew If the data isn’t evenly distributed across compute nodes, you’re likely to demand more resources from some nodes rather than others, and not get the most benefit out of the MPP architecture. This advisory condition allows you to detect data skew of your data at rest, before it impacts your query performance. By default, this condition checks row counts for each distribution for each table, and alerts you if it detects a 10 percent or more difference between the smallest and largest distributions for a given table.

Note:  As with the Outdated Statistics advisory condition, update the Database name to the database you wish to evaluate.

Suspended Requests Requests can utilize more than one concurrency slot based on their resource class. This advisory condition checks for any active requests in suspended status to alert you to concurrency slot saturation. This is only true for active requests in this state. After they execute, they show a normal completed status.

Note:  This advisory condition is global and only needs to be set up once under All Targets, because it queries the SentryOne database directly instead of any DMV’s on the target.

High Request Queue Times If locking and other mechanisms are excessive, you may notice a high percentage of the Total Elapsed Time for the request was spent in queue as represented in Queue Duration. This advisory condition queries the SentryOne database every 10 minutes and evaluates to true if any completed or running queries in the last 10 minutes have a total queueing time greater than one minute (60000 ms). It reports up to 10 device name + request ID combinations by default. This condition is global and only needs to be set up once.
Low Available Memory Memory can be a common root cause for slow performance and out-of-memory issues on any SQL Server and Microsoft’s MPP platforms are no exception. In Azure, consider scaling your data warehouse if you find SQL Server memory usage reaching its limits during query execution.
Long Running Requests This advisory condition, Distributed Queries: Duration Threshold Max, should be configured to detect and alert on any long-running requests.
Download: Download the aforementioned advisory conditions to use in your environment here.
Note:  Advisory Condition markers are set by default.

Event Calendar

See activities in a calendar format to quickly identify potential resource constraint problems in massively parallel processing (MPP) systems, where concurrency is an important aspect of performance.

SQL Sentry Azure Synapse SQL Pool Event Calendar view 

Additional Information: For more information about the Event Calendar in SQL Sentry, see the Calendar and List views topic.

Runtime Statistics

View Runtime Statistics graphs for Loader Backup Runs through the Navigator pane (View > Navigator). Expand your desired SQL DW environment in the Navigator, expand the Event Manager node, and then expand the Loader Backup Runs node to display the Backup, Load, and Restore nodes.

SQL Sentry Azure Synapse SQL Pool navigate to runtime stats

Select and Expand the desired node, and then select Runtime stats > Open to display the Runtime Statistics graph.

SQL Sentry Azure Synapse SQL Pool navigate to runtime stats

The Runtime Statistics graph can be displayed in History or Aggregate view.

SQL Sentry Azure Synapse SQL Pool Runtime Statistics History view

Creating Custom Advisory Conditions

Custom Advisory Conditions allow you to tailor and specify metrics for customized alerting based on performance counter metrics, query results, and duration of events. Create Custom Advisory Conditions for your Azure SQL Data Warehouse environment by completing the following steps:

  1. Expand the desired Azure SQL Data Warehouse instance in the Navigator pane (View > Navigator), and then select Conditions List. SQL Sentry Azure Synapse SQL Pool select Conditions Lists in the Navigator
  2. Select SQL DW from the Create Advisory Condition drop-down list. SQL Sentry Azure Synapse SQL Pool Create Advisory Condition SQL DW
  3. Enter a name, description, and appropriate recording metrics in their respective fields. SQL Sentry Azure Synapse SQL Pool Custom Advisory Condition
  4. Select Save on the toolbar to save and apply your Custom Advisory Condition. SQL Sentry Azure Synapse SQL Pool Save Custom Advisory Condition
Note:  For more information about Advisory Conditions, see the Advisory Conditions topic.