Azure SQL Data Warehouse SQL Operations Studio Extension Available

In may the engineering team for Azure SQL Data Warehouse announced integration with SQL Operations Studio. Now they have released a preview of an extension that contains pre built dashboards to monitor you Data Warehouse instances! Today you can monitor data skew, ColumnStore index health, and system resource usage. We expect more scenarios to be…


APS AU7 Performance Enhancements

Appliance Update 7 (AU7) for Microsoft’s Analytic Platform System (APS) brings with it a couple of much anticipated performance enhancements. The first is through new functionality by enabling AUTO UPDATE and AUTO CREATE statistics for appliance level stats objects. We always had auto stats on the compute nodes for the local SQL instances to utilize,…


Performance: Evaluate Data Skew

This topic applies to both Azure SQL Data Warehouse and Analytic Platform System Data skew occurs when one distribution has more data than others.  When data is inserted into a distributed table, each row will be assigned and sent to a distribution for storage.  The distribution a row is sent to is decided by applying…


Performance: Investigate Inconsistent Query Execution Times

This post applies to both APS and Azure SQL DW   There is a reasonable expectation that if a query is executed in a controlled environment multiple times it will have minimal variance in total execution time when no changes are made to the system, data or query.  When a variance is experienced it should…


APS / PDW Best Practices: Linked Server: OpenQuery VS EXEC

Many users utilize Linked Server functionality in order to issue queries to PDW using SSMS.  This is completely supported and a practical way of processing.  However it is important to pay attention to how you are issuing the queries through PDW.  There are two popular methods, either using OPENQUERY or EXEC.   The latter is the…


PDW Useful Queries: Check for existence of user temporary tables

User temporary tables in PDW/APS are treated slightly different than temporary tables in SQL Server.  They are still only viewable in the context of the session which created them, however the existence can have other impact to other sessions.  The existence of a temporary table in PDW is tracked as a distributed transaction across the…


Locking Behaviour with PDW Backups

You may notice your daily ETL jobs or queries running slower than usual or in a queued state when a PDW backup is running. By design, a Backup operation in PDW requires an Exclusive Update (XU) on the database in order toexecute. This is implemented using a standard Lock Manager in PDW engine and the behaviour…


APS/PDW Query Performance: Lock Status

A common scenario customers report is queries which ran quickly yesterday, are taking longer today.  Today I want to cover one aspect that may be causing this behavior: locks.   Most appliances out there have a combination of ETL, ELT, and ad hoc queries.  It is possible one of those ad hoc queries is blocking the…

6