DBCC PDW_SHOWEXECUTIONPLAN (Transact-SQL)

Článek
01/13/2023

Applies to: Azure Synapse Analytics Analytics Platform System (PDW)

Displays the SQL Server execution plan for a query running on a specific Azure Synapse Analytics or Analytics Platform System (PDW) 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 SQL Server 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.

Transact-SQL syntax conventions

Syntax

Syntax for Azure Synapse Analytics:

DBCC PDW_SHOWEXECUTIONPLAN ( distribution_id , spid )
[;]

Syntax for Analytics Platform System (PDW):

DBCC PDW_SHOWEXECUTIONPLAN ( pdw_node_id , spid )
[;]

Note

This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Arguments

distribution_id

Identifier for the distribution that is running the query plan. This is an integer and can't be NULL. Value must be between 1 and 60. Used when targeting Azure Synapse Analytics.

pdw_node_id

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

spid

Identifier for the SQL Server session that is running the query plan. This is an integer and can't be NULL.

Permissions

Requires CONTROL permission on Azure Synapse Analytics.

Requires VIEW SERVER STATE permission on the Appliance.

Examples: Azure Synapse Analytics

A. DBCC PDW_SHOWEXECUTIONPLAN basic syntax

The following sample query will return the sql_spid for each actively running distribution.

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];

If you are curious as to what distribution_id 1 was running in session 375, you would run the following command:

DBCC PDW_SHOWEXECUTIONPLAN (1, 375);

Examples: Analytics Platform System (PDW)

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 aren't 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);

Sdílet prostřednictvím