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

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.

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.
   
2. Release the cursor, and select Zoom In from the context menu.
   

Success: You have zoomed into a selected time period.

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.

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
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.
2. Select an end-date from the End drop-down list on the toolbar, and then select an end time from the drop-list.
3. Select Go on the toolbar to display your desired time period.

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

Using Jump to

Easily toggle between other SQL Sentry 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.
2. Right click the highlighted section, and then select Jump to > ( Calendar, Distributed Queries, Or Loads/Backups/Restores)  from the context menu.

Distributed Queries Tab

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

Loads, Backups, and Restores Tab

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

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.

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.

 

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.

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

The Runtime Statistics graph can be displayed in History or Aggregate 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.
2. Select SQL DW from the Create Advisory Condition drop-down list.
3. Enter a name, description, and appropriate recording metrics in their respective fields.
4. Select Save on the toolbar to save and apply your Custom Advisory Condition.