SQL Sentry v9.0: Monitoring APS and Azure SQL Data Warehouse

David Benoit

Published On: November 4, 2015

Categories: Azure, Monitoring, Performance Analysis 0

Massively Parallel Processing. Three words you might not normally consider using in the same sentence, but with systems like Microsoft's Analytics Platform System (APS), and the recent introduction of Azure SQL Data Warehouse (SQL DW), those days may be over!

As most are aware, there is an ever increasing amount of data being generated and stored both on premises and in the cloud, hence all this talk lately about "big data." In some cases, the standard tools that we have become familiar with, such as SQL Server and SQL Server Analysis Services, aren't able to process these big data sets fast enough for businesses to use the information to make timely decisions. Enter massively parallel processing for larger data sets.

SQL Sentry has always focused on helping the data professional by providing the tools that are necessary to find, and troubleshoot, performance problems on SQL Server database related platforms. We are very excited about the release of version 9.0 that includes Performance Advisor and Event Manager for Analytics Platform System and Azure SQL Data Warehouse!

The features included in this release are focused on providing the initial information necessary to identify and start diagnosis of query / process performance issues.

Data Movement Dashboard

Data movement is a natural part of how these systems operate, but there are times where heavy data movement can be an indication of poorly designed queries, or incorrectly distributed tables. The data movement dashboard is designed to allow the user to quickly identify data movement activity, and zoom-in to pinpoint those time periods where activity was the highest. It additionally highlights when you have activity that is unbalanced across the compute nodes, and the associated distributions.

Data Movement Dashboard

As with all standard SQL Sentry client features, we provide the ability to zoom-in on key periods of activity, go back in time to any specified time period, and the ability to jump-to other features within the product.

Distributed Queries

The idea of a query changes when working with massively parallel systems, as every query needs to be broken down to smaller pieces, and then run against the individual "compute" resources on the system. Our collection of that activity parallels our standard SQL Server query collection theme and display, allowing Performance Advisor to show all historical information in a method that allows for filtering, sorting, grouping, and other historical analysis. We also provide the distributed query step details so that this information can be reviewed along with the primary query request information.

Distributed Queries

Health Alerts And General Alerting Features

For those that are familiar with the APS appliance, they know that system health is a key piece of information that needs to be watched. We have provided a collection of health alerts that can be viewed by any period of time, and most importantly, can be used to generate alerts so that personnel can be immediately notified of any appliance health issues.

General Conditions

As you can see in the screen shot, we also provide alerting around query and load performance indicators, allowing the user to get notified of performance issues.

Loads / Backups / Restores

APS has a rich loading feature with the DWLoader application, and Performance Advisor collects this performance information historically, providing a display similar to our distributed query collection. For Azure SQL DW, we are tracking loads processes from SSIS packages, so that load performance can be tracked over time.

Event Manager Calendar

As we've talked with people about our product, one of the most interesting features with our monitoring for APS and Azure SQL DW has been one of our original components, the Event Manager calendar. With these systems concurrency is an important aspect of performance, and with the calendar, the user is able to see all activity that is occurring at a given point in time in a graphical format that allows for quick diagnosis of potential resource constraint issues.

Event Manager Calendar

Same Client, Same Monitoring Service

Another really great aspect of how we developed this solution is that you don't have to change anything with your monitoring footprint in order to monitor the on premises appliance, or the Azure solution. You can monitor them both with your current SQL Sentry monitoring service, and view all the information in your current SQL Sentry client -- all of the tools and processes that you know, now providing more coverage for those systems that you need to support your "big data" sets.

We are going to continue to improve on what we provide in our monitoring solutions, so keep an eye out for other future information in this space. We hope that you will keep growing your "big data solutions" and know that, along the way, SQL Sentry will be there with you.

Sound interesting to you? Download a trial of Performance Advisor and Event Manager, and then contact Scott Allison (sallison@sentryone.com) for a license key that will allow the trial to monitor Analytics Platform System and Azure SQL Data Warehouse.

David (@SQLTentmaker) was a Product Manager at SQL Sentry, where he provided design specifications for products that focus on platforms for data warehousing and analytics, both on-prem and in the cloud. He has a background as a production database administrator, and brings that passion to his product manager role.