Azure Synapse Performance DMV queries

Question

in Synapse Analytics dedicated SQL pool, can you point me to some links/posts, or share a DMV query that shows , execution time, CPU time, logicaleads, physicalreads, MAXDOP of a SQL query.

I have seen (https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor) , but these are generic to point us in the direction, but I am looking for more specific queries for troubleshoot. 

I worked in SQL Server before but I can see the DMVs/DMFs are not same.

Much appreciate your help.

Thank you,

Answer 1

Hello @HSever ,

Welcome to the MS Q&A platform.
Azure synapse uses a different architecture than Azure SQL Database, so some features that are available in Azure SQL Database are not available in Azure Synapse Analytics. This includes some of the dynamic management views.

I don't see any DMVs that give the logical reads, physical reads, and MaxDOP values in Synapse analytics.

But there are other DMVs where you can monitor your workload using DMVs. The below query gives the memory usage on the dedicated SQL pool.

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 
	-- pc1: current memory 
	sys.dm_pdw_nodes_os_performance_counters AS pc1 
	-- pc2: total memory allowed for this SQL instance 
	JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2 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)'

Memory grant per distribution

SELECT  [session_id]
,       [type]
,       [object_type]
,       [object_name]
,       [request_id]
,       [request_time]
,       [acquire_time]
,       DATEDIFF(ms,[request_time],[acquire_time])  AS acquire_duration_ms
,       [concurrency_slots_used]                    AS concurrency_slots_reserved
,       [resource_class]
,       [wait_id]                                   AS queue_position
FROM    sys.dm_pdw_resource_waits
WHERE    [session_id] <> SESSION_ID()

You can download the Synapse_toolkit from this Github to monitor your synapse dedicated SQL pools.

Also, there are other TSQL_queries, PowerBI reports, Log analytics queries in the toolbox (in the above GitHub URL) to monitor and manage your workspace.

I hope this helps. Please let me know if you have any further queries.