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


PDW Execution Plans: Why are you shuffling my data?!

One of the objectives when architecting a database on a PDW system is to do so with the goal of  minimizing  data movement.  Some data movement is unavailable.  It is important to be able to read an execution plan to be able to determine why a shuffle move, or any data movement for that matter,…


Loading performance – DWLoader, distribution keys and source files

There are several ways to load data in PDW: SSIS, DWLoader client, and with the latest release BCP also(*) In this post I will discuss a specific performance scenario when loading data with DWLoader. You can refer to .CHM for all details on DWLoader client, parameters, syntax, remarks… The first thing to check when looking…


APS AU4 Feature Review: Introducing Round Robin Distribution

This is our second post covering the new features and improvements included in the SQL APS PDW AU4 release.  Starting with AU4, tables in PDW now have a third distribution option.  In addition to the familiar Hash distribution and replicated formats, AU4 introduces the Round Robin distribution type.  This distribution type was first introduced in…


APS AU4 Feature Review: 1.5x data return rate improvement

The Microsoft engineering team has officially signed off on the latest APS update, Appliance Update 4. In this post we will choose one of the features from AU4 and discuss in more detail what the improvement is and what it means for PDW clients. One such improvement that has raised the most question is labeled as “1.5x…