Home > Management Views and Functions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Recently Executed Queries – Using DMVs/DMFs

Recently Executed Queries – Using DMVs/DMFs


To find the queries that were executed recently using the sys.dm_exec_query_stats DMV.

 

SELECT sql_handle, creation_time, last_execution_time
FROM   sys.dm_exec_query_stats
WHERE  last_execution_time > '2011-05-06 06:45:00.000'

 

The above statement will return the performance statistics for cached query plans. sys.dm_exec_query_stats contains the sql handle of the statement which was executed. To retrieve the actual query text, you need to use a DMF – sys.dm_exec_sql_text as follows:

 

sys.dm_exec_sql_text(sql_handle)

 

This functions takes the sql/plan hadle as input and returns the sql text. CROSS APPLY can be utilized add query text to the output:

 

SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY      STATS.last_execution_time DESC

 

image

 

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.