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

2 Answers 2


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
  r.resource_allocation_percentage AS rsc_pct,
  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,
  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;
  • dm_pdw_exec_requests will give results which is getting executed in system at the given point. dm_exec_requests_history gives history data as well. So that I can avoid persisting. But the target DB details are not there in this view. If we can map it to the DB using the transaction ID, that will simplify the problem. Mar 28, 2022 at 14:25
  • How to persist data? Mar 28, 2022 at 17:11
  • Updated answer to answer both of your comments. Mar 28, 2022 at 18:10
  • How do we set the retention for this table?
    – Gayani
    Aug 28, 2022 at 23:09
  • @Gayani see the bottom two lines of code, purging data from the tables based on the retention you prefer. Sep 20, 2022 at 10:08

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.