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


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


Creating an Active Directory Trust With APS

It is my assumption that almost all companies with an APS will want to connect it to their Active Directory so that they can use integrated authentication. What I will detail in this article is hopefully everything you need to know or consider when creating this trust. I highly recommend involving your Active Directory, DNS,…

0

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…


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…


Deciphering APS / PDW Execution Plans

PDW introduces an additional level of execution plans.  These are commonly referred to as ‘explain plans’ as they are obtained by prefacing the SQL statement with the term ‘EXPLAIN’.  When you do this, the query passes through all binding, authorization, PDW parsing, and PDW optimization as it would during typical execution.  The optimizer will use the metadata and statistics…


APS / PDW DMVs Explained

In this post we are going to discuss different types of DMV’s in PDW and where the data comes from.   The concept of DMV’s is not new and is taken straight out of SMP SQL Server.  In the appliance though, you don’t need information from just a single instance.  You need it from multiple…