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 objects on the CTL node to perform these steps, just like it would during actual execution. Once these items are complete, the execution plan will be returned in XML format and the query will NOT be executed.  An explain can be used as a workaround for try_parse in many cases because of this. The output will be the PDW execution plan, detailing what steps will be taken to complete the query across the appliance.  The explain plan will include where temporary tables will be created and how data will moved within the appliance.  When investigating a performance issue with a specific query, reviewing the PDW explain plan is where you should start.  In this post I will cover the anatomy of an explain plan and how to read the plan. 

 

 USE AdventureWorksPDW2012;
 EXPLAIN 
 SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome, 
 CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales, 
 G.StateProvinceName, T.SalesTerritoryGroup
 FROM dbo.DimGeography AS G
 JOIN dbo.DimSalesTerritory AS T
 ON G.SalesTerritoryKey = T.SalesTerritoryKey
 JOIN dbo.DimCustomer AS C
 ON G.GeographyKey = C.GeographyKey
 JOIN dbo.FactInternetSales AS FIS
 ON C.CustomerKey = FIS.CustomerKey
 WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
 AND Gender = 'F'
 GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
 ORDER BY AVG(YearlyIncome) DESC;
 GO

 

Estimated VS Actual Plans

 

To generate the PDW execution plan, the PDW engine uses the stats objects and metadata on the CTL node.  The only way these objects change is by user initiated operations.  The optimizer is performing the same operations when an EXPLAIN is issued as it would if the query itself was issued. Execution is simply deemed complete once the PDW plan has been generated.  Because of this, as long as the table structure and stats are not changed, the estimated and actual query plans are one in the same. 

 

 

Plan Structure

 

Explain plans will be returned in XML format.  At the top of every explain plan, will be the user query that was executed.  This will be between the <sql> <\sql>tags. 

 

Example output from the query above:

 

 

 1 <?xml version="1.0" encoding="utf-8"?>
 
 2 <dsql_query>
 
 3 <sql>SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome,
 
 4 CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales,
 
 5 G.StateProvinceName, T.SalesTerritoryGroup
 
 6 FROM dbo.DimGeography AS G
 
 7 JOIN dbo.DimSalesTerritory AS T
 
 8 ON G.SalesTerritoryKey = T.SalesTerritoryKey
 
 9 JOIN dbo.DimCustomer AS C
 
 10 ON G.GeographyKey = C.GeographyKey
 
 11 JOIN dbo.FactInternetSales AS FIS
 
 12 ON C.CustomerKey = FIS.CustomerKey
 
 13 WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
 
 14 AND Gender = 'F'
 
 15 GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
 
 16 ORDER BY AVG(YearlyIncome) DESC</sql>

 ...
 

 

 

The very next line after the closing tag will show the total cost of the plan as well as the total number of the operations. 17 <dsql_operations total_cost="0.926237696" total_number_operations="9">

 

 

After this is the meat of the execution plan. Each operation will be wrapped in the tags <dsql_operation operation_type="operation type"> and </dsql_operation>

 

The first line containing the operation type is an important one.  This will tell you what the step is going to be doing.  It could be "RND_ID" which will generate a random ID to be used for a temporary table, which is instantaneous and can typically be dismissed, or it could be "SHUFFLE MOVE" that is about to move millions of records and is expected to run for hours. 

 

The following line within these tags will vary depending on the type of the operation.  If it is doing an "ON" operation, which will execute a query without any data movement, the next line will be :

 

 22 <location permanent="True/False" distribution="location query will be executed" />

 

The states if it is a user temporary table (#table) and where the statement will be executed.  It possible for it to be executed on the CTL node, a subset of CMP nodes, all CMP nodes etc. 

 

If the operation type is some sort of a data movement, such as a "SHUFFLE MOVE" you will see the following as the next line instead

 

 49 <operation_cost cost="cost of the individual step" accumulative_cost="cost of the plan up to this point" average_rowsize="average row size of data to be moved" output_rows="output rows expected based on aggregrated stats objects on CTL node" />

 

If you are looking for the most expensive operation, you will simply look for the highest operation cost on this line.  It is also important to review the output_rows.  If this number is significantly different than actual, it most likely means you are missing statistics or the statistics are out of date.  You should ensure all join and predicate columns have stats and they are current.  Inaccuracies here may cause inefficient plans, such as replicating a table that we believe is empty but actually has 10M rows.  In a later post, I will cover how to get the actual row count for comparisons from the admin console or DMV's. 

 

The step will also have the actual SQL to be executed, if applicable.  The tag containing the statement varies depending on the type of operation. For shuffle moves you will see <source_statement>, for ON operations you will see <sql_operation type="statement">.   In the explain plan the SQL statements will reference the TMP_ID that was previously generated.  During execution, this ID will be changed to a GUID. If the operation type was some sort of data movement, the next tag in the operation will indicate the shuffle column or destination table.  The shuffle column is what the data in the destination will be distributed on. 

 

Please refer to the CHM for your version in the section labeled "EXPLAIN (Sql Server PDW)" for a more complete list of operations types and execution plan definitions. 

 

In future post I will cover how to use this information to identify issues and possible performance implications of the different steps.  Stay Tuned!