SQL Server Monitor: Manage Azure Synapse Dedicated SQL Pool

    Aug 30, 2024Simon Dang
    SQL Server Monitor: Manage Azure Synapse Dedicated SQL Pool

    Azure Synapse | Dedicated SQL Pool | Data Warehouse

    Here are some of the query tools that can be used to gain better insight into the current activities of your Azure Synapse, Dedicated SQL Pool, or Data Warehouse. Whichever terminology you use, it all points to an Analysis Data Warehouse database in Azure.

    Insight Query Tools

    Sessions

    -- Active Connections
    SELECT *
    FROM sys.dm_pdw_exec_sessions
    WHERE status <> 'Closed'
      AND session_id <> session_id();
    
    SELECT session_id()
    
    -- Kill Connection, remember to use single quotes
    KILL '[SID...]'

    Monitor

    -- Monitor active queries
    SELECT *
    FROM sys.dm_pdw_exec_requests
    WHERE status NOT IN ('Completed', 'Failed', 'Cancelled')
    ORDER BY submit_time DESC;

    Blocking

    -- Collect the top blocking
    SELECT TOP 500
        waiting.request_id AS WaitingRequestId,
        waiting.object_type AS LockRequestType,
        waiting.object_name AS ObjectLockRequestName,
        waiting.request_time AS ObjectLockRequestTime,
        blocking.session_id AS BlockingSessionId,
        blocking.request_id AS BlockingRequestId
    FROM sys.dm_pdw_waits waiting
    INNER JOIN sys.dm_pdw_waits blocking
        ON waiting.object_type = blocking.object_type
        AND waiting.object_name = blocking.object_name
    WHERE waiting.state = 'Queued'
        AND blocking.state = 'Granted'
    ORDER BY ObjectLockRequestTime ASC;

    Longest Running

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

    Distributed Queries

    -- Find the distributed query plan steps for a specific query.
    SELECT *
    FROM sys.dm_pdw_request_steps
    WHERE request_id = 'QID####'
    ORDER BY step_index;
    -- Retrieve the distribution run times for a specific SQL step.
    -- Substitute request_id and step_index with the respective values.
    SELECT *
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = 'QID####'
      AND step_index = 2;
    -- Retrieve the SQL Server execution plan for a query
    -- executing on a designated SQL pool or control node.
    -- Replace distribution_id and spid with the values obtained.
    DBCC PDW_SHOWEXECUTIONPLAN(1, 78);
    -- Retrieve information about all workers involved
    -- in a Data Movement Step.
    SELECT *
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = 'QID####'
      AND step_index = 2;
    -- Find the SQL Server estimated plan for a query
    -- executing on a designated SQL pool Compute or Control node.
    DBCC PDW_SHOWEXECUTIONPLAN(55, 238);
    -- Find queries
    -- 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.state,
           waits.object_type,
           waits.object_name
    FROM sys.dm_pdw_waits AS waits
    JOIN sys.dm_pdw_exec_requests AS requests
      ON waits.request_id = requests.request_id
    WHERE waits.request_id = 'QID####'
    ORDER BY waits.object_name, waits.object_type, waits.state;

    TempDB

    -- Monitor tempdb
    SELECT
        sr.request_id,
        ssu.session_id,
        ssu.pdw_node_id,
        sr.command,
        sr.total_elapsed_time,
        exs.login_name AS 'LoginName',
        DB_NAME(ssu.database_id) AS 'DatabaseName',
        (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
        (ssu.user_objects_alloc_page_count * 8)
          AS 'Space Allocated For User Objects (in KB)',
        (ssu.user_objects_dealloc_page_count * 8)
          AS 'Space Deallocated For User Objects (in KB)',
        (ssu.internal_objects_alloc_page_count * 8)
          AS 'Space Allocated For Internal Objects (in KB)',
        (ssu.internal_objects_dealloc_page_count * 8)
          AS 'Space Deallocated For Internal Objects (in KB)',
        CASE es.is_user_process
            WHEN 1 THEN 'User Session'
            WHEN 0 THEN 'System Session'
        END AS 'SessionType',
        es.row_count AS 'RowCount'
    FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
        INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es
            ON ssu.session_id = es.session_id
            AND ssu.pdw_node_id = es.pdw_node_id
        INNER JOIN sys.dm_pdw_nodes_exec_connections AS er
            ON ssu.session_id = er.session_id
            AND ssu.pdw_node_id = er.pdw_node_id
        INNER JOIN sys.dm_pdw_exec_requests AS sr
            ON ssu.session_id = sr.session_id
        INNER JOIN sys.dm_pdw_exec_sessions AS exs
            ON ssu.session_id = exs.session_id
    WHERE DB_NAME(ssu.database_id) = 'tempdb'
        AND es.session_id <> es.session_id
    ORDER BY sr.request_id;

    Monitor Azure Synapse with SQL Brainbox

    Simplify your Azure SQL monitoring workflow. SQL Brainbox is a comprehensive SQL Server monitor that provides real-time dashboards for all your database instances, including Azure environments. Download SQL Brainbox for free to monitor your SQL Servers from a single pane of glass.

    © 2026 Crystal Enterprise, LLC. All rights reserved.