Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
This article covers guidance on how to identify queries that are running beyond the query_execution_timeout_sec value configured for the workload group.
Azure Synapse Analytics provides the ability to create workload groups for isolating resources and classify workloads to the appropriate workload group. As part of the workload group definition, query_execution_timeout_sec can be configured to set the maximum execution time, in seconds, allowed before the query is canceled. However, to prevent the return of partial results, queries will not be canceled once they have reached the return phase of execution.
If these queries should be stopped, you can manually kill the session, associated with the query. This article covers guidance on identifying these queries.
To find queries that are running longer than the configured execution timeout and are in the return step phase:
Alternatively, a combined query is provided below that finds all requests, in the return step phase, that are running longer than the set max execution time for the workload group to which they are classified.
Once the queries have been identified, they can manually be terminated with the KILL command.
To view the execution timeout configured for a workload group in the Azure portal:
To view workload groups using T-SQL, connect to the dedicated SQL Pool using SQL Server Management Studio (SSMS) and issue following query:
SELECT * FROM sys.workload_management_workload_groups;
For more information, see sys.workload_management_workload_groups.
To view queries running longer than the configured execution timeout, using the timeout value from the workload group above, issue the following query:
DECLARE @GROUP_NAME varchar(128);
DECLARE @TIMEOUT_VALUE_MS INT;
SET @GROUP_NAME = '<group_name>';
SET @TIMEOUT_VALUE_MS = '<execution_timeout_ms>';
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE group_name = @GROUP_NAME AND status = 'Running' AND total_elapsed_time > @TIMEOUT_VALUE_MS
For more information, see sys.dm_pdw_exec_requests.
To check if the query is in the return operation step using the request ID from the prior step, issue the following query:
DECLARE @REQUEST_ID varchar(20);
SET @REQUEST_ID = '<request_id>';
SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = @REQUEST_ID AND status = 'Running' AND operation_type = 'ReturnOperation'
ORDER BY step_index;
For more information, see sys.dm_pdw_request_steps.
To find all the requests that are in the return step phase and are running longer than their workload group’s configured execution timeout, issue the following query:
SELECT DISTINCT ExecRequests.request_id, ExecRequests.session_id, ExecRequests.total_elapsed_time,
ExecRequests.group_name, (WorkloadGroups.query_execution_timeout_sec * 1000) AS GroupQueryExecutionTimeoutMs
FROM sys.dm_pdw_exec_requests AS ExecRequests
JOIN sys.workload_management_workload_groups AS WorkloadGroups ON WorkloadGroups.name = ExecRequests.group_name
JOIN sys.dm_pdw_request_steps AS RequestSteps ON ExecRequests.request_id = RequestSteps.request_id
WHERE ExecRequests.status = 'Running' AND ExecRequests.total_elapsed_time > (WorkloadGroups.query_execution_timeout_sec * 1000)
AND RequestSteps.status = 'Running' AND RequestSteps.operation_type = 'ReturnOperation'
If you would manually like to terminate these queries, you can use the KILL command for the session(s) identified above.
KILL '<session-id>'
For more information, see KILL (Transact SQL).
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today