Finding The Last Queries Run On A SQL Instance

Hey everyone! Last SQL Something post for the year 2013! :-)

Pretty happy with how the year went blog-wise (not as many posts per month as I'd hoped but still I managed to stick with it).

Anyhoo, enough about that and on to the post! Today we are looking at how to view the last few queries that ran on your databases.

This post came about because a co-worker asked if it was possible to run a query to see past queries (because he wanted to see which databases were still being used out of a group of old databases). I had a couple answers for him:

1. Use SQL Server Profiler.
2. Use this query (click here) to view queries that are currently running.

The 'problem' with 1) was that he thought it might be a bit much to set up (he was not very used to SQL Server). Personally this might still be the best option.

The 'problem' with 2) was any queries that might be hitting the databases in question would be sporadic. You would have to be pretty lucky to 'catch' a running query.


I was intrigued by the question, so I turned to our old friend Google. I found that a pretty popular query seems to be:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

And it is pretty good. You can see times and query text for past queries that were run.

I was curious though if I can see what DB the query is being run from/on.

After some digging I found this:

SELECT top 50 DB_NAME(st.dbid) AS DatabaseName,
e.connection_id AS ConnectionID,
s.session_id AS SessionID,
s.login_name AS LoginName,
s.last_request_end_time AS RequestTime,
s.cpu_time AS RunTime,
text AS QueryText
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections e
ON s.session_id = e.session_id
CROSS APPLY sys.dm_exec_sql_text(e.most_recent_sql_handle) st
ORDER BY last_request_end_time DESC

This query is pretty nice as it gives a lot of extra info through the use of sys.dm_exec_sessions (login name, run time, etc) but just as importantly it also states the DB the query is using as its initial DB. Personally I prefer this one out of the two.

Note that because both queries use sys.dm_exec_sql_text, you can use the DB_NAME function in both. However I find that it does not seem to be as accurate if you use it in the first query (I believe it might be the way the join takes place in the first but I will look into that and add an edit later on).

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)