Hello @@maddy ,
Here are the different ways to find top 10 queries longest running queries in Synapse:
Method1: Monitor workload - Azure portal using Log Analytics
Step1: Configure diagnostic settings to emit logs from your SQL pool. Logs consist of telemetry views equivalent to the most commonly used performance troubleshooting DMVs.
Step2: Navigate to your Log Analytics workspace where you can do the following:
- Analyze logs using log queries
- Pin query results to a dashboard
Method2: Monitor your Azure Synapse Analytics dedicated SQL pool workload using DMVs
Step1: Open Azure Synapse Studio and create a sql script.
Step2: Run the top 10 queries longest running queries in Synapse.
-- Find top 10 queries longest running queries SELECT TOP 10 * FROM sys.dm_pdw_exec_requests ORDER BY total_elapsed_time DESC;
Method3: Monitor your Azure Synapse Analytics dedicated SQL pool workload using Azure Portal
Step1: Go to Azure Synapse Analytics workspace => Analytics Pools => SQL Pools
Step2: Select the SQL Pool => Under Monitoring section => Query activity
Hope this helps. Do let us know if you any further queries.
Please don’t forget to
Accept Answer and
Up-Vote wherever the information provided helps you, this can be beneficial to other community members.