DW Sentry Overview

Download: For more information about DW Sentry pricing or downloading a trial, see the DW Sentry product page.

The Azure SQL Data Warehouse Performance Monitoring Solution

With DW Sentry, accelerate your return on investment by increasing your data platform speed and efficiency. Ensuring optimal performance of your Azure SQL Data Warehouses means you can deliver accurate, business-critical information to your end users. DW Sentry gives your data team logically presented, actionable metrics to view the entire data warehouse environment so they can quickly identify bottlenecks that might cause delays.

DW Sentry provides detailed visibility into the queries, loads, backups, and restores of all your data. DW Sentry includes features such as the Event Calendar and intelligent movement Dashboard, allowing you to know what factors are impacting your workload. DW Sentry helps you get more value from your cloud-based data warehouse investment.

Data Movement Dashboard

DW Sentry 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:

GraphDescriptionImage
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. DW Sentry Distribution Activity graph
Node ActivityDisplays a graphical representation of the node activity for a selected metric across the compute nodes within your environment in milliseconds. Each color on the graph represents an individual compute node in your environment. DW Sentry Node Activity graph
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.DW Sentry Data Movement CPU (ms) graph
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.DW Sentry Elapsed Time (ms) graph
Rows ProcessedDisplays 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. DW Sentry Rows Processed graph
MB ProcessedDisplays 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.DW Sentry MB Processed graph
MB/SecDisplays 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. DW Sentry MB/Sec graph


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.  

DW Sentry Data Movement Dashboard related graphsData Movement CPU example
DW Sentry Data Movement Dashboard related graphsRows Processed example

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.
    DW Sentry Data Movement Dashboard use click and drag
  2. Release the cursor, and select Zoom In from the context menu.
    DW Sentry Data Movement Dashboard Zoom In

Success: You have zoomed into a selected time period.

DW Sentry Data Movement Dashboard Zoom In

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.

DW Sentry 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 buttonImage
Zoom InDW Sentry toolbar Zoom In
Zoom OutDW Sentry 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 DW Sentry. 

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 and an end date from the End drop-list.
    DW Sentry toolbar time navigation
  2. Enter a desired time frame in the Start and End time boxes.
    DW Sentry toolbar time navigation
  3. Select Go on the toolbar to display your desired time period.
    DW Sentry toolbar time navigation

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

DW Sentry 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.
    DW Sentry 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.
    DW Sentry Jump To

Distributed Queries Tab

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

DW Sentry Distributed Queries tab

Loads, Backups, and Restores Tab

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

DW Sentry 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 DW Sentry:

Advisory ConditionDescription
Outdated StatisticsOutdated 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 SkewIf 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 RequestsRequests 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 TimesIf 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 MemoryMemory 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 RequestsThis 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.

DW Sentry Event Calendar view For more information about the Event Calendar in SentryOne, 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.

DW Sentry navigate to runtime stats

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

DW Sentry navigate to runtime statsThe Runtime Statistics graph can be displayed in History or Aggregate view.

DW Sentry Runtime Statistics History view
History View
DW Sentry Runtime Statistics Aggregate view
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.
    DW Sentry select Conditions Lists in the Navigator
  2. Select SQL DW from the Create Advisory Condition drop-down list.
    DW Sentry Create Advisory Condition SQL DW
  3. Enter a name, description, and appropriate recording metrics in their respective fields.
    DW Sentry Custom Advisory Condition
  4. Select Save on the toolbar to save and apply your Custom Advisory Condition.
    DW Sentry Save Custom Advisory Condition

Note:  For more information about Advisory Conditions, see the Advisory Conditions topic.