Monat: Juni 2017

Optimizing Performance of Azure SQL Data Warehouse with SentryOne

There is constant talk about big data; endless marketing decks, whitepapers, and blog posts about how fast data is multiplying on-premise and in the cloud. In many cases, familiar database technologies, such as Symmetric Multi-Processing (SMP) or “scale-up” architecture, can no longer process growing data sets fast enough for businesses to make timely decisions. Massively Parallel Processing (MPP) or “scale-out” architecture is quickly becoming the preferred alternative proven capable of handling larger (or massive) data sets.

Azure SQL Data Warehouse, or simply Azure SQL DW, allows companies to use MPP to take advantage of significant performance gains crunching large data sets without the investment and overhead of maintaining on-premise hardware and software. Simply provision an Azure SQL DW instance, and you gain access to all the advantages of MPP without the commitments of purchasing and maintaining the infrastructure associated with it. That noted, because Azure is a pay-as-you-go solution, it’s important to ensure you are as efficient as possible with those resources to get the most value from this platform-as-a-service (PaaS).

SentryOne (formerly SQL Sentry) has always provided solutions to data professionals to monitor, diagnose, and optimize performance for SQL Server and the Microsoft Data Platform. SentryOne DW Sentry is the essential solution which provides critical performance insight for Azure SQL DW.

SentryOne DW Sentry provides unequaled visibility into one of the most expensive steps in MPP query execution: data movement. Additionally, you can monitor and be notified regarding any loads, backups, and restores of your data. Explore activity in an Outlook-style calendar view, or generate your own customized alerts based on thresholds and other logic such as excessive queuing and suspended queries due to lock contention or exhaustion of concurrency slots.

Data Movement Dashboard

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

DW Sentry, like all SentryOne solutions, provides the ability to zoom-in on key periods of activity, go back in time to a relevant time period, and the ability to jump-to other SentryOne diagnostic and optimization features.

Distributed Queries

Query concepts are different in MPP architecture: every query must be deconstructed into smaller pieces and run against individual distributed compute resources in the system. DW Sentry collects and displays details of each MPP query, allowing DW Sentry to show all historical information in a method that allows for filtering, sorting, grouping, and other historical analysis. It also provides the distributed query step details so this information can be reviewed along with the primary query request information.

DW Sentry also provides alerting surrounding query and load performance indicators, allowing user notifications of performance issues.

Loads/ Backups/ Restores

For Azure SQL DW, we are tracking loads processes from SSIS packages so load performance can be tracked over time, providing a display like the distributed query collection.

Event Manager Calendar

One of the most interesting DW Sentry features for Azure SQL DW monitoring is one of the original components found in all SentryOne solutions, the Event Calendar. In MPP systems, concurrency is an important aspect of performance; the Event Calendar graphically displays all activity that is occurring at a given point in time to promote quick diagnosis of potential resource constraint issues.

Same SentryOne Client, Same SentryOne Monitoring Service

As with all SentryOne products, you don't have to change anything with your monitoring footprint to monitor on premise targets, such as SQL Servers, Analysis Services, Hyper-V hosts and guests, or APS appliances along with cloud solutions like Azure SQL DB and Azure SQL DW. You can monitor all of it with a single implementation, and view all the information in any SentryOne client. You can also easily trial and deploy the complete suite of products from the Azure Marketplace.

SentryOne continues to improve their Microsoft Data Platform performance optimization solutions, so watch for future announcements and further improvements. Keep growing your "big data" with the confidence that SentryOne will help you Monitor, Diagnose and Optimize your platform.

Sound interesting to you? Download a trial of SentryOne which includes DW Sentry that will allow you to monitor Azure SQL DW. The trial also includes our full suite of solutions built to elevate performance across the entire Microsoft Data Platform.
Quelle: Azure