MS SQL: Show recent queries

Modified on Mon, 24 Nov at 5:51 PM

Option 1 using Plan Cache

SELECT TOP 50
    dest.text AS [Query Text],
    deqs.last_execution_time AS [Last Run Time],
    deqs.execution_count AS [Execution Count],
    (deqs.total_worker_time / deqs.execution_count) / 1000.0 AS [Avg CPU Time (ms)],
    (deqs.total_elapsed_time / deqs.execution_count) / 1000.0 AS [Avg Duration (ms)]
FROM 
    sys.dm_exec_query_stats AS deqs
CROSS APPLY 
    sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE 
    dest.dbid = DB_ID() -- Filters to the current database only
ORDER BY 
    deqs.last_execution_time DESC;

Option 2 using Query Store (SQL 2016+)

First you need to have the Query Store enabled:

ALTER DATABASE [YourDB] SET QUERY_STORE = ON

Then run the queries you want to trace, and then you can see the history using:

SELECT TOP 50
    qt.query_sql_text AS [Query Text],
    rs.last_execution_time AS [Last Run Time (UTC)],
    rs.count_executions AS [Execution Count],
    rs.avg_duration / 1000.0 AS [Avg Duration (ms)],
    rs.avg_cpu_time / 1000.0 AS [Avg CPU (ms)]
FROM 
    sys.query_store_query_text AS qt
JOIN 
    sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
JOIN 
    sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN 
    sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE 
    rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE()) -- Last 1 hour only
ORDER BY 
    rs.last_execution_time DESC;

You can disable the Query Store using:

ALTER DATABASE [YourDB] SET QUERY_STORE = OFF