question

maddy-5927 avatar image
0 Votes"
maddy-5927 asked PRADEEPCHEEKATLA-MSFT commented

How to write queries for synapse on log analytics

Hi Team,

I want to find the long running queries in synapse and want to pin its results to dashboard.

I have sql query for this one

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

But i want to run this in log analytics workspace so i can pin the results to dashboard.

I have checked in my log analytics workspace but i couldn't find any table there which has logs of synapse.

Please let me know how this can be done.

Thanks & Regards,
MSD



azure-synapse-analyticsazure-monitor
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered maddy-5927 commented

Make sure you have configurated your synapse pool to push these logs to a Log Analytics workspace. With your pool open in the portal, go to Diagnostic Settings and add make sure you are sending your SQL Requests to your Log Analytics workspace. The sys.dm_pdw_exec_requests view in your SQL pool correlates with the SynapseSqlPoolExecRequests table when sent to Log Analytics.

69915-image.png



image.png (119.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SamaraSoucy-MSFT

I have diagnostics setting enabled for my synapse with all above logs..

Can u provide me with query to run which can give me results of long running queries ..

Thanks & Regards,
Rohit

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @maddy-5927,

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.

69975-image.png

Step2: Navigate to your Log Analytics workspace where you can do the following:

  • Analyze logs using log queries

  • Pin query results to a dashboard

70101-image.png

69976-image.png

70053-azsqlpool-longrunningquery.gif

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;

70102-image.png


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

70111-image.png

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.



image.png (61.7 KiB)
image.png (189.0 KiB)
image.png (1.5 KiB)
image.png (127.6 KiB)
image.png (125.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @maddy-5927,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·