Using DMVs to identify long running queries within a stored procedure

SELECT TOP 10 Min(query_stats.statement_text)                                                      AS statement_text,
( Sum(query_stats.total_elapsed_time) / Sum(query_stats.execution_count) ) / 1000000 AS avg_elapsed_time_seconds,
Sum(query_stats.total_elapsed_time) / 1000000                                        AS total_elapsed_time seconds,
Max(query_stats.total_elapsed_time) / 1000000                                        AS max_elapsed_time_seconds,
Sum(query_stats.execution_count)                                                     run_count,
query_stats.query_hash                                                               AS query_hash
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
WHERE  st.objectid = Object_id(‘stored_procedure’)) AS query_stats
GROUP  BY query_stats.query_hash
ORDER  BY 2 DESC;

GO

 

Source: https://msdn.microsoft.com/en-us/library/ms189741%28v=sql.105%29.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s