Tuesday, March 20, 2012

Find out recently run queries

With the help of a DMV (Dynamic Management Views) and a table valued function we can list the required result.


  • sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle.
  • sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
dmStats.last_execution_time as 'Last Executed Time',
dmText.text as 'Executed Query'
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc

No comments:

Post a Comment