Thursday, May 20, 2010

How in MS SQL Server 2005 to find out what requests where carried out recently

How in MS SQL Server 2005 to find out what requests where carried out recently? This information can be obtained using function sys.dm_exec_query_stats. Function in tabular form displays statistics about cached queries (when the last time the query runs, how long lasted execution, etc.). For details, follow the link above.

Although the output of this function is limited to the cache and the function can not report statistics for any arbitrary period of time, but at least the last N query you will see. One NO. In this table, instead of the query text is displayed its handle. To obtain a copy of the request another one function can be used. It's name is sys.dm_exec_sql_text. As a parameter this function takes the handle of the query and displays a table with data about it.

To get a list of queries that were performed recently (in cache) to display their text and runtime, use the following query:
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

No comments: