The Azure Synapse SQL Pool Performance Monitoring Solution
DW Sentry 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. DW Sentry 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 DW Sentry 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:
|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.
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:
- Click and drag the cursor over a period of interest on a bottom pane graph.
- Release the cursor, and select Zoom In from the context menu.
Success: You have zoomed into a selected time period.
Note: 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.
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 DW Sentry.
Using the Toolbar
Navigate to a specific time period on the Data Movement Dashboard by completing the following steps:
- Select a start-date from the Start drop-down list on the toolbar, and then select a Start time from the drop-list.
- Select an end-date from the End drop-down list on the toolbar, and then select an end time from the drop-list.
- 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 SentryOne diagnostic and optimization tools using the Jump To context menu item. Use Jump to by completing the following steps:
- Highlight the desired time frame in a bottom pane graph.
- 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.
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 DW Sentry:
|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.
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.
Additional Information: For more information about the Event Calendar in SentryOne, see the Calendar and List views topic.
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:
- Expand the desired Azure SQL Data Warehouse instance in the Navigator pane (View > Navigator), and then select Conditions List.
- Select SQL DW from the Create Advisory Condition drop-down list.
- Enter a name, description, and appropriate recording metrics in their respective fields.
- Select Save on the toolbar to save and apply your Custom Advisory Condition.
Note: For more information about Advisory Conditions, see the Advisory Conditions topic.