Tools for 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 used, 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 from Steps 1 and 3.

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 from the preceding query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

-- Retrieve information about all workers involved in a Data Movement Step.
-- Substitute request_id and step_index with the values obtained from Steps 1 and 3.

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.
-- Replace distribution_id and spid with the respective values obtained from the preceding query.

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 microsoft.vw_sql_requests AS sr
        ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr
        ON exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs
        ON exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Memory Consumption

-- Memory consumption

SELECT
  pc1.cntr_value AS Curr_Mem_KB,
  pc1.cntr_value / 1024.0 AS Curr_Mem_MB,
  pc1.cntr_value / 1048576.0 AS Curr_Mem_GB,
  pc2.cntr_value AS Max_Mem_KB,
  pc2.cntr_value / 1024.0 AS Max_Mem_MB,
  pc2.cntr_value / 1048576.0 AS Max_Mem_GB,
  pc1.cntr_value * 100.0 / pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
  sys.dm_pdw_nodes_os_performance_counters AS pc1 -- pc1: current memory
JOIN
  sys.dm_pdw_nodes_os_performance_counters AS pc2 -- pc2: total memory allowed for this SQL instance
ON
  pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
  pc1.counter_name = 'Total Server Memory (KB)'
  AND pc2.counter_name = 'Target Server Memory (KB)'

Transaction Log Size

-- Transaction log size

SELECT
  instance_name AS distribution_db,
  cntr_value / 1048576.0 AS log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
  instance_name LIKE 'Distribution_%'
  AND counter_name = 'Log File(s) Used Size (KB)'

Miscellaneous

-- Monitor rollback

SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END) AS 'TransactionCount',
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

-- To track bytes and files

SELECT
    r.command,
    s.request_id,
    r.status,
    COUNT(DISTINCT input_name) AS nbr_files,
    SUM(s.bytes_processed) / 1024 / 1024 / 1024 AS gb_processed
FROM
    sys.dm_pdw_exec_requests r
    INNER JOIN sys.dm_pdw_dms_external_work s
        ON r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files DESC,
    gb_processed DESC;

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

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command, waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS Blocking_Time_sec
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
       INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
       ON blocking.request_id = blocking_exec_request.request_id
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;