Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Introduction:

In this article we will look how to use Dynamic Management Views (DMVs) to monitor your workload including investigating query execution in SQL pool.

Permissions:

To query the DMVs in this article, you need either VIEW DATABASE STATE or CONTROL permission. Usually, granting VIEW DATABASE STATE is the preferred permission as it is much more restrictive.

GRANT VIEW DATABASE STATE TO myuser;

Monitor connections:

All logins to your data warehouse are logged to sys.dm_pdw_exec_sessions. This DMV contains the last 10,000 logins.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

SELECT * FROM sys.dm_pdw_nodes_exec_connections;

 

The request_id uniquely identifies each query and is the primary key for this DMV. The request_id is assigned sequentially for each new query and is prefixed with QID, which stands for query ID. Querying this DMV for a given session_id shows all queries for a given logon.

Investigate Query Execution

If your queries are taking up lot of time to execute and still runnning. In such cases you can use the following steps to collect data and narrow down the issue.

---Monitor running queries
Select * from sys.dm_pdw_exec_requests WHERE STATUS IN ('Running','Suspended') order by 1 desc

 -- Find the longest running queries
SELECT * FROM sys.dm_pdw_exec_requests ORDER BY total_elapsed_time DESC;

Save the Request ID of the query.

Check if the query is waiting for resources.

-- Find waiting tasks for your session.
-- Replace request_id with value from Step 1.

 SELECT waits.session_id,waits.request_id, requests.command, requests.status, requests.start_time, waits.type, waits.object_type,waits.object_name, waits.state FROM  sys.dm_pdw_waits waits JOIN sys.dm_pdw_exec_requests requests  ON waits.request_id=requests.request_id WHERE waits.request_id = 'QIDxxxxx' ORDER BY waits.object_name, waits.object_type, waits.state;

 The results of the above query will show you the wait state of your request.

• If the query is waiting on resources from another query, then the state will be AcquireResources.
• If the query has all the required resources and is not waiting, then the state will be Granted. In this case, proceed to look at the query steps.

Find the longest running step of the query

Use the Request ID to retrieve a list of all the distributed query steps. Find the long-running step by looking at the total elapsed time.

--Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.
SELECT * FROM sys.dm_pdw_request_steps WHERE request_id = 'QIDxxxxx' ORDER BY step_index;

Save the Step Index of the long-running step.

Check the operation_type column of the long-running query step:

• Proceed with Step 4a for SQL operations: OnOperation, RemoteOperation, ReturnOperation.
• Proceed with Step 4b for Data Movement operations: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

STEP 4a: Find the execution progress of a SQL Step

Use the Request ID and the Step Index to retrieve information about the SQL Server query distribution as a part of the SQL Step in the query. Save the Distribution ID and SPID.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.
 SELECT * FROM sys.dm_pdw_sql_requests WHERE request_id = 'QIDxxxxx' AND step_index = 2;

Use the following query to retrieve the SQL Server execution plan for the SQL Step on a particular node.

-- Find the SQL Server execution plan for a query running on a specific SQL Data Warehouse Compute or Control node.
-- Replace distribution_id and spid with values from previous query.

 DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Find the execution progress of a DMS Step

Use the Request ID and the Step Index to retrieve information about the Data Movement Step running on each distribution.

-- Find the information about all the workers completing a Data Movement Step. 
-- Replace request_id and step_index with values from Step 1 and 3.


SELECT * FROM sys.dm_pdw_dms_workers WHERE request_id = 'QIDxxxxx' AND step_index = 2;

•  Check the total_elapsed_time column to see if a particular distribution is taking significantly longer than others for data movement.
• For the long-running distribution, check the rows_processed column to see if the number of rows being moved from that distribution is significantly larger than others. This shows that your query has data skew.

Investigate Data Skew

 — Find data skew for a distributed table

DBCC PDW_SHOWSPACEUSED("dbo.<tablename>");

Click here for more information from Microsoft.