Skip to content

dbcc-pdw-showexecutionplan-transact-sql.md

Latest commit

 

History

History
97 lines (72 loc) · 4.37 KB

dbcc-pdw-showexecutionplan-transact-sql.md

File metadata and controls

97 lines (72 loc) · 4.37 KB
title DBCC PDW_SHOWEXECUTIONPLAN (Transact-SQL)
ms.custom
ms.date 07/16/2017
ms.prod sql
ms.technology data-warehouse
ms.prod_service sql-data-warehouse, pdw
ms.reviewer
ms.topic language-reference
dev_langs
TSQL
author pmasl
ms.author umajay
monikerRange >= aps-pdw-2016 || = azure-sqldw-latest || = sqlallproducts-allversions

DBCC PDW_SHOWEXECUTIONPLAN (Transact-SQL)

[!INCLUDEapplies-to-version/asa-pdw]

Displays the [!INCLUDEssNoVersion] execution plan for a query running on a specific [!INCLUDEssSDW] or [!INCLUDEssPDW] Compute node or Control node. Use this to troubleshoot query performance problems while queries are running on the Compute nodes and Control node.

Once query performance problems are understood for SMP [!INCLUDEssNoVersion] queries running on the Compute nodes, there are several ways to improve performance. Possible ways to improve query performance on the Compute nodes include creating multi-column statistics, creating nonclustered indexes, or using query hints.

Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

Syntax

Syntax for Azure SQL Data Warehouse:

DBCC PDW_SHOWEXECUTIONPLAN ( distribution_id, spid )  
[;]

Syntax Azure Parallel Data Warehouse:

DBCC PDW_SHOWEXECUTIONPLAN ( pdw_node_id, spid )  
[;]

Arguments

distribution_id
Identifier for the distribution that is running the query plan. This is an integer and cannot be NULL. Used when targeting [!INCLUDEssSDW].

pdw_node_id
Identifier for the node that is running the query plan. This is an integer and cannot be NULL. Used when targeting an Appliance.

spid
Identifier for the [!INCLUDEssNoVersion] session that is running the query plan. This is an integer and cannot be NULL.

Permissions

Requires CONTROL permission on [!INCLUDEssSDW].

Requires VIEW-SERVER-STATE permission on the Appliance.

Examples: [!INCLUDEssSDW]

A. DBCC PDW_SHOWEXECUTIONPLAN Basic Syntax

When running on a [!INCLUDEssSDW] instance, modify the above query to also select the distribution_id.

SELECT [sql_spid], [pdw_node_id], [request_id], [dms_step_index], [type], [start_time], [end_time], [status], [distribution_id]  
FROM sys.dm_pdw_dms_workers   
WHERE [status] <> 'StepComplete' and [status] <> 'StepError'  
order by request_id, [dms_step_index];

This will return the spid for each actively running distribution. If you were curious as to what distribution 1 was running in session 375, you would run the following command.

DBCC PDW_SHOWEXECUTIONPLAN ( 1, 375 );

Examples: [!INCLUDEssPDW]

B. DBCC PDW_SHOWEXECUTIONPLAN Basic Syntax

The query that is running too long is either running a DMS query plan operation or a SQL query plan operation.

If the query is running a DMS query plan operation, you can use the following query to retrieve a list of the node IDs and session IDs for steps that are not complete.

SELECT [sql_spid], [pdw_node_id], [request_id], [dms_step_index], [type], [start_time], [end_time], [status]   
FROM sys.dm_pdw_dms_workers   
WHERE [status] <> 'StepComplete' and [status] <> 'StepError'  
AND pdw_node_id = 201001   
order by request_id, [dms_step_index], [distribution_id];

Based on the results of the preceding query, use the sql_spid and pdw_node_id as parameters to DBCC PDW_SHOWEXECUTIONPLAN. For example, the following command shows the execution plan for pdw_node_id 201001 and sql_spid 375.

DBCC PDW_SHOWEXECUTIONPLAN ( 201001, 375 );

See also