How to Find Recent Executed Queries in SQL Server? – Interview Question of the Week #087

Interviews can surprise both sides. I was helping an organization hire when a candidate challenged the interviewer over a question about recently executed SQL Server queries. The interviewer expected a DMV query. The candidate knew it, but also knew its limit.

Can a DMV show every query that ran?

No. The following query is a useful look at recently executed statements with cached plans. It is a starting point for diagnosis, not a complete execution history.

SELECT TOP (50) txt.text AS BatchText,
       qs.execution_count,
       qs.creation_time,
       qs.last_execution_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY qs.last_execution_time DESC;

The DMV reports completed query executions whose plans remain in cache, not every currently running request. On SQL Server 2022 and later, the query generally requires VIEW SERVER PERFORMANCE STATE; older versions use VIEW SERVER STATE. The interviewer’s original script used the same sys.dm_exec_query_stats and sys.dm_exec_sql_text pair. The candidate’s objection was correct: the statistics belong to cached plans. A plan can be evicted, recompiled, or removed when SQL Server restarts. Its count begins with that cached entry, not at the birth of the database. A row here indicates useful recent activity; an absent row does not prove the query never ran.

Also notice that txt.text is the batch text. One batch can contain several statements, so use statement offsets or the execution plan when you need to attribute cost to a particular statement.

If you need a history that survives a plan leaving cache, set up the appropriate collection before the incident. Query Store, where enabled and retained, can help with statement history. An Extended Events session can record events chosen for a specific investigation. Neither should be described as a record of everything without checking its configuration and retention.

I agreed with the candidate. The DMV query was a good answer only after stating what it could and could not prove. That distinction matters more in a performance investigation than memorizing the view’s name.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.