How To Find Last Few Executed Queries in SQL Server?

As a DBA, you may often need to check the last few executed queries in an SQL Server or against a specific database. Using a DMV (Dynamaic Management Views) is one among the easiest way to find the recent executed queries. Of course, using DMV is not 100% reliable, but it will give you a quick insight about the queries executed in the recent past.

Here I’ll list few of the DMV statements which will be helpful to find the historical SQL queries for different scenarios.

Finding Last Few Executed Queries Against All The Databases In SQL Server

SELECT 
	txt.TEXT AS [SQL Statement],
	qs.EXECUTION_COUNT [No. Times Executed],
	qs.LAST_EXECUTION_TIME AS [Last Time Executed], 
	DB_NAME(txt.dbid) AS [Database]
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

Against A Specific Database

To filter the above query for a specific database use the DBID column in the view DM_EXEC_SQL_TEXT. Below is a sample statement. Beware, the DBID column will not always having the data. There is a possibility the column DBID has null value and you may miss some recently executed statements against the database.

SELECT 
	txt.TEXT AS [SQL Statement],
	qs.EXECUTION_COUNT [No. Times Executed],
	qs.LAST_EXECUTION_TIME AS [Last Time Executed], 
	DB_NAME(txt.dbid) AS [Database]
FROM 
	SYS.DM_EXEC_QUERY_STATS AS qs
	CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
WHERE 
	txt.dbid = DB_ID('WideWorldImporters')
ORDER BY qs.LAST_EXECUTION_TIME DESC

Drawbacks

1. The sys.dm_exec_query_stats DMV works based on the query plan in the cache. Once the plan is removed from the cache by any means, the view will not return the query. So this method is unreliable.
2. The dbid column in the sysdm_exec_sql_text may contain null value. So using it to filer the queries executed against a specific database in not reliable.

Other Options

• One option to find the last few executed queries is by using server-side trace for a short time and collect the SQL statements executed.
• The best and reliable technique is to use the Extended Events.

Reference:

• Details about the dmv in Microsoft docs.