How to create user-defined restore points on Azure SQL Data Warehouse

Microsoft announced user-defined restore point on Azure SQL Data Warehouse. This feature enables you to manually trigger snapshots to create restore points of your data warehouse before and after large modifications. This capability ensures that restore points are logically consistent which provides additional data protection in case of any workload interruptions or user errors for…

0

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…


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,…


SET IMPLICIT_TRANSACTIONS Behavior On Azure SQL Data Warehouse and APS

Working with transactions in Azure SQL Data Warehouse (ADW) and the Analytics Platform System (APS, aka PDW) is a bit different than one would expect. Though most of us tend to operate under the default behavior with IMPLICIT_TRANSACTIONS OFF, developers that interact with ADW/APS using other languages may find transaction handling using their language constructs…

0

Azure Resource Manager (ARM) and Azure SQL Data Warehouse

Having worked with many customers doing ARM deployments, I’ve found it is not always clear how to create a good template when deploying an Azure SQL Data Warehouse. Here is an example implementation created for testing: { “$schema”: “https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#”, “contentVersion”: “1.0.0.0”, “parameters”: { “serverName”: { “type”: “string”, “minLength”: 1 }, “serverAdminLogin”: { “type”: “string”, “minLength”:…

0

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…