Boost SQL Pool Performance: Troubleshooting Slow Queries in Azure Synapse Analytics

Table of Contents

This article outlines a structured approach to identifying and resolving common performance bottlenecks affecting queries executed on a dedicated SQL pool within Azure Synapse Analytics. Slow query performance can significantly impact data processing efficiency and user experience. By following these steps, you can systematically investigate the lifecycle of a problematic query, pinpoint where time is being consumed, and apply appropriate mitigation strategies.

The process begins by gathering essential telemetry related to the slow query. This telemetry provides insights into the query’s execution plan, resource consumption, and wait states. Understanding these factors is crucial for diagnosing the root cause of performance degradation. The final step involves analyzing the collected data and applying targeted solutions based on the identified issue, ranging from code optimization to configuration adjustments.

Step 1: Identify the request_id (also known as QID)¶

The unique identifier for a query execution, known as the request_id or QID, is the starting point for any performance investigation. This identifier allows you to track the specific instance of the slow query through the system views. Obtaining the correct request_id is essential for accessing detailed execution metrics and wait statistics.

To find the request_id, you can query the sys.dm_pdw_exec_requests Dynamic Management View (DMV). This DMV provides information about all requests currently or recently processed by the dedicated SQL pool. You can filter and order the results to quickly locate the queries of interest, especially those that are still running or have taken a long time to complete.

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed', 'Failed', 'Cancelled')
      AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

This query shows requests that are currently in progress, ordered by the time they were submitted. Filtering by session_id <> session_id() excludes the query you are running to monitor. You can optionally filter by label if you’ve used the OPTION(LABEL='<YourLabel>') hint in your queries, which is a good practice for easier identification.

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

This second query helps identify completed queries that took the longest to execute. Sorting by total_elapsed_time DESC places the slowest queries at the top of the result set. By examining the results from these queries, you can pinpoint the specific slow query instance you want to troubleshoot and note down its corresponding request_id.

When looking for the specific slow query, consider using the label option during query submission. This makes filtering the sys.dm_pdw_exec_requests DMV much simpler and more precise. Also, be mindful that filtering by resource_allocation_percentage IS NOT NULL might exclude queries waiting for resources, so use it cautiously.

Step 2: Determine Where the Query is Taking Time¶

Once you have the request_id, the next step is to understand the query’s execution plan and identify which specific step is consuming the most time. Dedicated SQL pools execute queries in a distributed manner, breaking them down into multiple steps and distributing work across compute nodes and distributions. Analyzing these steps is key to finding the bottleneck.

The sys.dm_pdw_request_steps DMV provides details about each step of a distributed query plan. By querying this DMV for the target request_id, you can see the sequence of operations performed, their status, start and end times, and estimated/actual row counts. This allows you to identify the step with the longest duration, which is likely the source of the performance issue.

Run the following script, replacing <request_id> with the value obtained in Step 1. You can set @ShowActiveOnly to 0 to see the full plan or 1 to focus only on currently running steps. Pay close attention to the StepIndex, Phase, and Description columns for the slow step.

DECLARE @QID AS VARCHAR (16) = '<request_id>', @ShowActiveOnly AS BIT = 1;
-- Retrieve session_id of QID

DECLARE @session_id AS VARCHAR (16) = (SELECT session_id
                                       FROM sys.dm_pdw_exec_requests
                                       WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
      AND ([type] LIKE 'Shared-%'
           OR [type] IN ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
      AND [state] = 'Queued'
GROUP BY session_id
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id,
       @QID AS request_id,
       -1 AS [StepIndex],
       'Compilation' AS [Phase],
       'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on ' + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
       waiting.request_time AS [StartTime],\n       GETDATE() AS [EndTime],
       DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS [Duration],
       NULL AS [Status],
       NULL AS [EstimatedRowCount],
       NULL AS [ActualRowCount],
       COALESCE (blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits AS waiting
     INNER JOIN
     sys.dm_pdw_waits AS blocking
     ON waiting.object_type = blocking.object_type
        AND waiting.object_name = blocking.object_name
     INNER JOIN
     sys.dm_pdw_exec_requests AS blocking_exec_request
     ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id
      AND waiting.state = 'Queued'
      AND blocking.state = 'Granted'
      AND waiting.type != 'Shared'
-- Request Steps
UNION ALL
SELECT @session_id AS session_id,
       @QID AS request_id,
       step_index AS [StepIndex],
       'Execution' AS [Phase],\n       operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime],
       end_time AS [EndTime],
       total_elapsed_time / 1000.0 AS [Duration],
       [status] AS [Status],\n       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
      AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

This script combines information from sys.dm_pdw_waits and sys.dm_pdw_request_steps to give a comprehensive view. It first checks for compilation or resource allocation waits before listing the execution steps. The output helps you pinpoint if the delay is happening before execution begins (Compilation phase) or during the execution of a specific operation (Execution phase). Note the StepIndex, Phase, and Description of the step that has the longest duration or is currently running unexpectedly long.

Step 3: Review Step Details¶

Once a problematic step is identified from Step 2, you need to delve deeper into its execution across the various distributions. A single step in the distributed plan is executed concurrently by workers across the compute nodes and distributions. Poor performance in one or more distributions can significantly impact the overall step duration, as the next step cannot begin until all distributions complete the current one.

The sys.dm_pdw_sql_requests and sys.dm_pdw_dms_workers DMVs provide details about the work performed by individual distributions for a given request step. By querying these DMVs, you can examine metrics like execution time, rows processed, and crucially, wait types at the distribution level. This helps identify if the issue is localized to specific distributions, perhaps due to data skew or resource contention.

Run the following script, replacing <request_id> and <StepIndex> with the values identified in the previous steps. Set @ShowActiveOnly to 0 to see all distributions for the step or 1 to focus on currently running ones. Examine the total_elapsed_time and wait_type values for each distribution.

DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)\n           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))\n      AND sr.step_index = @StepIndex
ORDER BY distribution_id;

This query joins the distribution-level DMV information with sys.dm_pdw_nodes_exec_requests to pull in wait statistics (wait_type, wait_time). A significant difference in total_elapsed_time across distributions within the same step often indicates data skew. High wait_time values or specific wait_type entries can point to resource contention, locking, or I/O issues within those distributions. Note down the wait_type values that are most prominent in the slowest distributions.

Step 4: Diagnose and Mitigate¶

Based on the information gathered in the previous steps, you can now diagnose the specific cause of the slow query and apply appropriate mitigation strategies. The issues typically fall into compilation phase problems, execution phase problems, or specific wait type issues.

Compilation Phase Issues¶

Problems in the compilation phase mean the query is taking a long time to generate an execution plan before any data processing begins. This can be identified if the query status is ‘Running’ in Step 1, but Step 2 shows a long duration before the first execution step, or if Step 2 explicitly indicates a blocking condition related to compilation or resource allocation.

Here are some common causes and mitigations for compilation phase delays:

Blocked: Compilation Concurrency

This happens when many queries are submitted simultaneously, creating a backlog for the compilation process. Dedicated SQL pools have limits on how many queries can be compiled at once. While rare, this can lead to queuing before execution even begins.

• Mitigation: Reduce the rate of query submission. Batch queries or space out complex submissions to avoid overwhelming the compilation queue.

Blocked: Resource Allocation

This occurs when a query is waiting for necessary resources, primarily memory, before it can start executing. Resource allocation depends on the resource class or workload group assigned to the user running the query, the overall system resource availability, and workload management settings like importance.

• Mitigations:
  • Wait for blocking queries to release resources.
  • Evaluate and potentially adjust the resource class assigned to the user or query to ensure sufficient memory allocation. Higher resource classes grant more memory but reduce overall concurrency.
  • Consider using workload groups and classifiers to manage resource allocation and importance for different types of queries.
  • If a blocking query is unintended or stuck, use the KILL command to terminate it, freeing up resources.

Complex Query or Older JOIN Syntax

The query optimizer might struggle to find an efficient plan for queries with a very high number of joins, subqueries, or non-ANSI-92 style joins (e.g., joins in the WHERE clause). This can lead to excessive compilation time as the optimizer explores numerous plan possibilities.

• Mitigations:
  • Rewrite queries using explicit ANSI-92 JOIN syntax (INNER JOIN, LEFT JOIN, etc., with ON clauses). This syntax is generally preferred by the optimizer.
  • For specific complex queries, consider adding query hints like OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). FORCE ORDER forces the optimizer to join tables in the order specified in the FROM clause, which can sometimes help, although it should be used cautiously. FORCE_LEGACY_CARDINALITY_ESTIMATION uses an older cardinality estimation model, which might be better for specific query patterns.
  • Break down extremely complex queries into smaller, more manageable steps using temporary tables or Common Table Expressions (CTEs).

Long-running DROP TABLE or TRUNCATE TABLE

While these operations are typically fast, dedicated SQL pools defer the actual storage cleanup to a background process for performance. If many DROP TABLE or TRUNCATE TABLE operations occur in rapid succession, the background cleanup process can fall behind. This can lead to metadata contention, causing subsequent DROP or TRUNCATE statements to slow down significantly as they wait for cleanup to complete.

• Mitigation: Schedule a maintenance window where workloads are paused. Run DBCC SHRINKDATABASE on the database. This command forces the immediate cleanup of orphaned space from dropped or truncated objects, alleviating the metadata bottleneck.

Unhealthy CCIs (Generally)

Clustered Columnstore Indexes (CCIs) are fundamental for performance in dedicated SQL pools. An “unhealthy” CCI has a high percentage of deleted rows or many small open rowgroups. This poor health requires the query optimizer to process extra metadata and potentially deal with less efficient data structures, increasing compilation time.

• Mitigation: Regularly assess and improve the health of your CCIs. This typically involves rebuilding or reorganizing the indexes, which consolidates small rowgroups and removes logically deleted rows. Tools and scripts are available to help identify unhealthy indexes.

Delay from Auto-Create Statistics

By default, AUTO_CREATE_STATISTICS is enabled in dedicated SQL pools. This feature automatically creates statistics on columns used in predicates or joins if necessary. While beneficial for plan quality, the first execution of a query requiring new statistics will incur the overhead of creating them. This can make the initial run significantly slower than subsequent runs.

• Mitigation: If a query consistently triggers auto-creation of statistics and its first execution is slow, manually create the necessary statistics beforehand using CREATE STATISTICS. Identify the columns involved in joins, filters, and GROUP BY clauses that lack statistics.

Auto-Create Statistics Timeouts

The auto-creation process has a default timeout of 5 minutes. If generating statistics for a large table or complex set of columns takes longer than this, the process is aborted to allow the query to proceed. The query then runs without the beneficial statistics, potentially resulting in a very poor execution plan and slow performance.

• Mitigation: Similar to the previous point, manually create statistics for the relevant tables and columns using CREATE STATISTICS. Monitor the duration of these manual creation processes to ensure they complete successfully. If they also take a long time, consider sampling data or creating statistics on a subset of columns.

Execution Phase Issues¶

Execution phase problems occur when the query plan has been generated, but one or more steps during data processing are slow. This is typically identified by a long duration for a specific StepIndex in Step 2. Further analysis in Step 3 helps determine if the issue is localized to specific distributions or affects all distributions uniformly.

Here are common causes and mitigations for execution phase delays:

Inaccurate Estimates

The query optimizer relies heavily on statistics to estimate the number of rows that will be processed at each step. If statistics are outdated or missing, the optimizer might make poor decisions, such as choosing an inefficient join strategy (like a Broadcast when a Shuffle would be better) or allocating insufficient resources. A significant discrepancy between EstimatedRowCount and ActualRowCount in Step 2 is a strong indicator of inaccurate statistics.

• Mitigation: Regularly update statistics on all tables and columns involved in queries, especially after significant data loading or modification. Use UPDATE STATISTICS or CREATE STATISTICS with appropriate sampling rates. You can assess statistics accuracy using tools or queries to compare the histogram distribution with the actual data distribution.

Uncached Replicated Tables

Replicated tables are small dimension tables copied to each compute node for fast local joins. However, after data modifications (DML operations), the copies on the compute nodes become stale and need to be “warmed” or refreshed. If queries access a replicated table that hasn’t been warmed, the system incurs the cost of distributing the changes or even potentially falling back to a less efficient join strategy, leading to performance degradation.

• Mitigations: After batch loading or significant DML operations on replicated tables, explicitly trigger the cache warm-up process. If a replicated table undergoes frequent, small DML operations, consider changing its distribution strategy to ROUND_ROBIN or HASH instead, as maintaining the replicated cache might become a bottleneck.

Mismatched Data Type/Size

Joining columns in different tables that have different data types or sizes can force the system to perform implicit conversions or data movements. For example, joining an INT column to a BIGINT column or a VARCHAR(10) to a VARCHAR(100) can introduce overhead and prevent optimal join strategies. This can lead to unnecessary data shuffling and increased processing time.

• Mitigation: Ensure that join columns across tables have identical data types and sizes. If necessary, recreate or modify tables to standardize column definitions. Explicitly casting columns in queries can sometimes work around this, but changing the underlying schema is usually the better long-term solution.

Ad Hoc External Table Queries

Azure Synapse Analytics dedicated SQL pools are optimized for querying data stored within the pool (on distributed tables and CCIs). While querying external tables directly is possible (using PolyBase or CETAS), it’s primarily intended for data loading (CREATE EXTERNAL TABLE AS SELECT). Ad hoc queries against external tables can be subject to external factors like network latency to storage, storage account throttling, or competition for resources within the storage account. Performance can be variable and often slower than querying data already loaded.

• Mitigation: For analytical queries requiring consistent performance, load the data from external storage into the dedicated SQL pool first using COPY INTO or CREATE EXTERNAL TABLE AS SELECT followed by querying the internal table. This leverages the distributed architecture and optimized storage format of Synapse Analytics.

Data Skew (Stored)

Data skew occurs when data in a distributed table is not evenly distributed across the 60 distributions. This is determined by the chosen distribution column. If one or a few distributions hold significantly more data than others, the processing for those distributions takes much longer. Since each step of the distributed plan must wait for the longest-running distribution, the overall query execution time is dictated by the most skewed distributions. This appears in Step 3 as a few distributions having significantly higher total_elapsed_time.

• Mitigation: Analyze your data and choose a better distribution column for the skewed table. An ideal distribution column is one that is frequently used in joins or filters and has a high number of unique values with data evenly spread across those values. DBCC PDW_SHOWSPACEUSED('<table_name>'); can help visualize stored data skew.

In-Flight Data Skew

Unlike stored data skew, in-flight skew isn’t about how data is stored on disk, but how it gets distributed during query execution. Certain operations, particularly ShuffleMoveOperations (redistributing data across distributions), can sometimes produce skewed output for subsequent steps, even if the source tables were not skewed. This can happen due to specific join conditions, filtering, or grouping that results in an unequal distribution of rows being sent to the next processing step. This also appears as high total_elapsed_time in a few distributions in Step 3, but the underlying table might not show significant stored skew.

• Mitigations:
  • Ensure statistics are up-to-date and accurate, especially on join and filter columns involved in shuffle operations. Accurate statistics help the optimizer choose better data movement strategies.
  • If grouping, try ordering the columns in the GROUP BY clause with the higher-cardinality column first.
  • Create multi-column statistics if joins or filters involve multiple columns.
  • Consider using the OPTION(FORCE_ORDER) hint if you suspect the join order is leading to skewed intermediate results (use cautiously).
  • Refactor the query logic. Sometimes restructuring joins or applying filters earlier can prevent in-flight skew.

Wait Type Issues¶

Step 3 provides wait_type information at the distribution level. If the common issues above don’t explain the bottleneck, analyzing the dominant wait types can point to lower-level resource contention or system issues. Wait types indicate what a query (or a distribution worker) is waiting on to proceed (e.g., waiting for a lock, I/O completion, or CPU).

You can find explanations and categories of wait types in SQL Server documentation (Synapse Analytics uses a compatible engine). The wait_time column indicates how long the query spent waiting on that specific type. Focus on the wait types with the highest total wait time in the problematic distributions.

Here’s a summary of some relevant wait type categories and their potential mitigations in Synapse Analytics:

Wait Category	Common Causes & Mitigations
Compilation	Related to query compilation and optimization. Causes: Complex queries, outdated statistics, metadata contention. Mitigations: Update statistics, simplify queries, use query hints cautiously, investigate metadata health (e.g., DBCC SHRINKDATABASE), potentially analyze execution plans.
Lock, Worker Thread	Related to locking conflicts and resource contention for worker threads. Causes: Concurrent transactions modifying the same data, long-running transactions, excessive small DML operations on CCIs. Mitigations: Reduce concurrency, shorten transaction duration, batch DML operations, consider rowstore indexes for frequently updated tables.
Buffer IO, Other Disk IO, Tran Log IO	Related to reading/writing data from/to disk (storage). Causes: Unhealthy CCIs (segment quality, deleted rows), outdated statistics leading to excessive data movement, high overall I/O workload for the current DWU level. Mitigations: Improve CCI health (rebuild indexes), update statistics, increase DWU or resource class.
CPU, Parallelism	Related to CPU usage and management of parallel execution. Causes: Inefficient query plans (e.g., unnecessary data movement), complex calculations or transformations in the query, insufficient CPU resources for the workload, workload management settings limiting CPU. Mitigations: Optimize queries (reduce transformations), improve statistics, implement workload isolation, increase DWU or resource class.
Network IO	Related to data transfer between nodes or to the client. Causes: Large result sets returned to the client, excessive data movement between distributions (ShuffleMove, BroadcastMove), network congestion within the fabric. Mitigations: Reduce returned data size, optimize data movement steps (check distribution, statistics), reduce concurrency of operations returning large results, consider scaling DWU up/down as a potential network reset (though investigate other causes first).

For Compilation wait types, if standard mitigations like updating statistics and rebuilding indexes don’t help, analyzing the distribution-level execution plans (<ShowPlanXML> output) using SQL Server Management Studio (SSMS) can be beneficial. Compare the plan of a slow distribution to a fast one for the same step to find structural differences or warnings.

For Lock/Worker Thread issues, consider the impact of frequent INSERT/UPDATE/DELETE operations on Columnstore indexes. Batching these operations is highly recommended. Sometimes, switching frequently modified small tables to a rowstore index can improve concurrency.

I/O related waits often stem from inefficient data access patterns. Improving CCI health and ensuring accurate statistics are critical for minimizing disk I/O. If the overall I/O load is simply too high for the configured DWU, scaling up compute resources is a direct solution.

CPU and Parallelism waits can be caused by suboptimal query plans or insufficient processing power. Workload isolation can help prioritize critical queries for CPU resources. Query optimization and sufficient compute scale are key.

Network I/O waits, especially during RETURN operations, might indicate that too much data is being sent back to the client application. Optimize the application to retrieve only necessary data. Data movement steps can cause significant network traffic; ensure your distribution strategy and statistics are optimized to minimize costly shuffles or broadcasts.

Troubleshooting slow queries is an iterative process. After applying a mitigation, re-run the query and use the steps outlined here again to see if the performance has improved and if the bottleneck has shifted to a different step or wait type.

We hope this guide helps you diagnose and resolve slow query performance in your Azure Synapse Analytics dedicated SQL pool. Understanding the distributed execution and leveraging the available DMVs is crucial for effective tuning.

Do you have experiences troubleshooting slow queries in Synapse Analytics? Share your tips or challenges in the comments below!