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

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.