Azure SQL DBA Tools: 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.

Sessions

-- Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions WHERE status <> 'Closed' AND session_id <> session_id()

SELECT session_id()

-- Kill a connection (use single quotes around the SID)
KILL '[SID...]'

Monitor Active Queries

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

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

Top 10 Longest-Running Queries

SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC

Distributed Query Plans

-- 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 distribution run times for a specific SQL step
SELECT * FROM sys.dm_pdw_sql_requests WHERE request_id = 'QID####' AND step_index = 2

-- Retrieve the SQL Server execution plan for a specific distribution
DBCC PDW_SHOWEXECUTIONPLAN(1, 78)

-- Retrieve information about all workers in a Data Movement Step
SELECT * FROM sys.dm_pdw_dms_workers WHERE request_id = 'QID####' AND step_index = 2

Datazip provides expert Azure Synapse and SQL Server managed services. Call 1-833-DATAZIP or visit www.SQLBrainBox.com for our monitoring tool.