How to Get all Recent SQL Calls to Database
To get recent SQL calls to a database and their duration, call this SQL.
Replace '500' with how many results you would like.
SELECT TOP 500 *
FROM(SELECT last_execution_time LastExecuted,
COALESCE(OBJECT_NAME(s2.objectid),'(SQL)') AS [ProcedureName],
total_elapsed_time / execution_count/1000000 AvgElapsedSeconds, -- avg per call
last_elapsed_time/1000000 LastElapsedSeconds, -- duration of last call
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS SqlText
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
--WHERE last_execution_time <= cast('2013-01-01 12:00:00.000' as DateTime) -- optional search by date
ORDER BY LastExecuted DESC
Some more detailed information about all of the columns available in 'dm_exec_query_stats' is available at: http://msdn.microsoft.com/en-us/library/ms189741.aspx
They also have more examples of good queries, such as how to get the top 5 queries using up CPU time:
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
Created By: amos 3/27/2013 5:01:47 PM
Updated: 3/27/2013 5:14:20 PM
|
|