Friday, December 18, 2015

Get all the queries that executed in the last 10 seconds...


There are sometimes when you wanted to capture the most recent queries that has hit your database but you don't want to run a heavy duty tool like a profiler or whatever.

DMVs can help you here.

As long as the query is in the cache, you can recover the SQL text for that query.

This query will help you for that

select st.text, qs.last_execution_time
 from msdb.sys.dm_exec_query_Stats qs cross apply msdb.sys.dm_exec_sql_text (qs.sql_handle) st
where qs.last_execution_time > dateadd(ss,-10,getdate())
order by qs.last_execution_time

Note:
dateadd(ss,-10,getdate())  -- This means , All of the queries executed in the last 10 seconds. If you want to get everything in the last one minute, change the condition to this dateadd(mi,-1,getdate())