Azure SQL DW Performance: CTAS/Partition Switching VS UPDATE/DELETE

DML operations can be resource intensive and harmful to CCI indexes in Azure SQL Data Warehouse. The main performance benefit comes from UPDATE & DELETE operations being fully logged and CTAS is minimally logged. There is also advantages to recreating the CCI index VS modifying it with a DML statement. The preferred method is to…


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 Monitoring solutions

APS features an out of the box monitoring portal which allows users to review APS components health state as well as monitor resources consumption and workload executions. We’re talking about the Admin Console. You can check out more on how to Monitor the appliance using Admin Console from the official documentation page. Amongst the monitoring…