A common SQL Server question is how to identify recent statements that have been executed. To retrieve this information in versions of SQL Server prior to 2005 required having a SQL Server trace running or using a Third Party tool and there was no ‘easy’ way to retrieve this information retrospectively. SQL Server 2005 introduced Dynamic Management Views (DMV’s) and Functions that can be queried to return internal data about the state of SQL Server. By using the DMV’s in SQL Server 2005 you can now easily identify the recent statements that have been executed and also compare the current duration versus an earlier execution of the statement in order to assist with performance tuning. The following query illustrates how to retrieve the recently executed statements and all of the query statistics associated with the execution:
SELECT qs.*, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.last_execution_time DESC
