2

I am bit new to Azure synapse dedicated SQL POOL ,I am trying to find List of SQL queries executed by users on Dedicated SQL POOL in Last seven days with user name.

I have tried using DMV sys.dm_pdw_exec_requests & sys.dm_pdw_exec_sessions but not able to fetch the correct results.

I am expecting result in below format

LoginUserName Query_Submit_time , QueryEndTime Actual_SQL_Query Query_Status

Any help appreciated here

Gourav Joshi
  • 39
  • 6

1 Answers1

3

Azure Synapse Analytics (formerly SQL Data Warehouse) provides a set of dynamic management views (DMVs) that give you insights into the queries and loads that have been run. To get the list of SQL queries executed by users on the Dedicated SQL Pool in the last seven days, you can join sys.dm_pdw_exec_requests and sys.dm_pdw_exec_sessions.

Here's a query that should give you the desired result:

    SELECT 
    s.login_name AS LoginUserName,
    r.start_time AS Query_Submit_time,
    r.end_time AS QueryEndTime,
    r.command AS Actual_SQL_Query,
    r.status AS Query_Status
FROM 
    sys.dm_pdw_exec_requests r
JOIN 
    sys.dm_pdw_exec_sessions s
ON 
    r.session_id = s.session_id
WHERE 
    r.start_time >= DATEADD(DAY, -7, GETDATE()) -- Filter for the last seven days
ORDER BY 
    r.start_time DESC;

This query joins the two DMVs on the session_id and filters the results to only show queries from the last seven days. The result will be in the format you specified: LoginUserName, Query_Submit_time, QueryEndTime, Actual_SQL_Query, and Query_Status.

aaronksalmon
  • 124
  • 7