How to get queries executed in Synapse SQL?

Question

I need to find all the query executed on my DB in last 1Hr in Synapse workspace with SQL pool. Will apply my logic on top of the result set.

What's the right table/view to look for?

1. sys.dm_exec_requests
2. sys.dm_exec_requests_history
3. sys.dm_exec_query_stats

Answer 1

I have never used sys.dm_exec_requests_history. I have seen references here about using it to see the details about the data processing amount. This catalog view only exists in the master database whereas dm_pdw_exec_requests is the dedicated SQL Pool. You cannot join between two catalog views in the different databases in the synapse.

You can use the below query and save the result in a table. Set up your retention on that table to go back point in time to see what was running. How frequently you do the collection is something I cannot suggest. It depends on your workload and can you afford to lose any data between the pool.

--Create table where you want to save the query history
CREATE TABLE [dbo].[QueryCollection]
(
    [capturedDT] datetime,
    [login_name] [nvarchar](128) NULL,
    [session_id] [nvarchar](32) NULL,
    [status] [nvarchar](32) NULL,
    [rsc_pct] [decimal](5, 2) NULL,
    [command] [nvarchar](4000) NULL,
    [submit_time] [datetime] NULL,
    [start_time] [datetime] NULL,
    [total_elapsed_time] [int] NULL,
    [resource_class] [nvarchar](20) NULL,
    [importance] [nvarchar](128) NULL,
    [group_name] [sysname] NULL,
    [classifier_name] [sysname] NULL,
    [queue_wait] [int] NULL
)
--Schedule this at an interval that works for you
INSERT INTO dbo.QueryCollection
SELECT 
  CURRENT_TIMESTAMP,
  s.login_name,
  r.session_id,
  r.status, 
  r.resource_allocation_percentage AS rsc_pct,
  r.command,
  CONVERT(DATETIME, SWITCHOFFSET(r.submit_time, DATEPART(TZOFFSET,r.submit_time AT TIME ZONE 'Eastern Standard Time'))) AS submit_time,
  CONVERT(DATETIME, SWITCHOFFSET(r.start_time, DATEPART(TZOFFSET,r.start_time AT TIME ZONE 'Eastern Standard Time'))) AS start_time,
  r.total_elapsed_time,
  r.resource_class,
  r.importance,
  r.group_name,
  r.classifier_name,
  DATEDIFF(SECOND,r.submit_time,r.start_time) AS queue_wait
FROM sys.dm_pdw_exec_requests r
INNER JOIN sys.dm_pdw_exec_sessions s
  ON r.session_id = s.session_id
WHERE r.status NOT IN ('Completed','Failed','Cancelled')
  AND r.session_id <> session_id();
--Purge data older than seven days
DELETE FROM dbo.QueryCollection
WHERE capturedDT < GETDATE()- 7;

Answer 2

You can use sys.dm_exec_requests DMV coupled with the sys.dm_exec_sql_text function to get the actual query text:

Returns the text of the SQL batch that is identified by the specified sql_handle.

You need to use the sql_handle, statement_start_offset, and statement_end_offset to get the appropriate query text:

[statement_start_offset] Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.

Alternatively you can try using Adam Machanic's sp_WhoIsActive which does the work of the aforementioned DMVs for you. (I'm not sure if it's compatible with Synapse.)

But in either case you would need a mechanism to continuously log the results, if you wanted to look back over the past hour, as those queries only return what is currently active on the server.

I've never heard of sys.dm_exec_requests_history and I see very little mention of it anywhere, so I can't advise about it, but it sounded promising if it is available to you.

Answer 3

--Show everything and everyone with time corrections
SELECT 
  CURRENT_TIMESTAMP,
  s.login_name,
  r.session_id,
  r.status, 
  r.resource_allocation_percentage AS rsc_pct,
  r.command,
  CONVERT(DATETIME, SWITCHOFFSET(r.submit_time, DATEPART(TZOFFSET,r.submit_time AT TIME ZONE 'Pacific Standard Time'))) AS submit_time,
  CONVERT(DATETIME, SWITCHOFFSET(r.start_time, DATEPART(TZOFFSET,r.start_time AT TIME ZONE 'Pacific Standard Time'))) AS start_time,
  r.total_elapsed_time,
  Cast((r.total_elapsed_time/1000)/60 as decimal(18,2)) as elapsed_time_minutes,
  r.resource_class,
  r.importance,
  r.group_name,
  r.classifier_name,
  DATEDIFF(SECOND,r.submit_time,r.start_time) AS queue_wait
FROM sys.dm_pdw_exec_requests r
INNER JOIN sys.dm_pdw_exec_sessions s
  ON r.session_id = s.session_id
--WHERE r.status NOT IN ('Completed','Failed','Cancelled')
Where r.session_id <> session_id()
Order by Submit_time desc