Track User Activity & SQL Queries Executed in Azure Synapse Dedicated SQL Pool | Azure Synapse Analytics Tutorial

Track User Activity & SQL Queries Executed in Azure Synapse Dedicated SQL Pool

📘 Overview

Azure Synapse Dedicated SQL Pool allows administrators to track query execution details and monitor user activity. This is essential for auditing, performance tuning, and identifying long-running or suspicious queries. In this guide, we show how to use built-in system views to analyze query history.

🔍 Use Case

• Identify long-running queries
• Track who executed which queries
• Audit query timing and duration

🛠️ T-SQL Example: Get Executed Queries and Duration

The query below pulls recent SQL execution data for the last 30 days using DMVs:

-- Get recent query execution details including duration
WITH cte AS (
    SELECT
        s.login_name         AS LoginUserName,
        r.submit_time        AS QuerySubmitTime,
        r.end_time           AS QueryEndTime,
        DATEDIFF(SECOND, r.submit_time, r.end_time) AS DurationSeconds,
        r.command            AS SQLCommand,
        r.status             AS QueryStatus
    FROM sys.dm_pdw_exec_requests r
    JOIN sys.dm_pdw_exec_sessions s
        ON r.session_id = s.session_id
    WHERE r.submit_time >= DATEADD(DAY, -30, GETDATE())
)
SELECT *
FROM cte
WHERE LoginUserName = 'System' -- Replace with specific username if needed
ORDER BY DurationSeconds DESC;

🔐 Fields Explained

• LoginUserName – User who submitted the query
• QuerySubmitTime – When the query started
• QueryEndTime – When the query completed
• DurationSeconds – Query duration in seconds
• SQLCommand – Command text submitted
• QueryStatus – Completion status (e.g., Completed, Failed)

📌 Tips for Monitoring

• Automate query logging using Synapse or Azure Monitor
• Alert on long durations or failed queries
• Use filters for specific login names or keywords in command

🧠 Best Practices

• Track and trend long-running queries monthly
• Use this query to identify candidate queries for optimization
• Implement role-based access and monitor sensitive query logs

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.