sql Slow Running Queries

	Declare @ElapsedSecondsThreshold decimal(18,2)
	
	Set @ElapsedSecondsThreshold = 1.0--anything over 1 second
	--Set @ElapsedSecondsThreshold = 0.1--anything over 100 milliseconds

	SELECT
		creation_time 
		,last_execution_time
		,total_physical_reads
		,total_logical_reads 
		,total_logical_writes
		, execution_count
		, total_worker_time
		, total_elapsed_time
		, (total_elapsed_time / execution_count/1000000.) as avg_elapsed_secs
		,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) st
	WHERE
		(total_elapsed_time / execution_count/1000000.) >= @ElapsedSecondsThreshold
		And st.text Not Like '%delete from dbo.tbl_log%'--excluding log purges
		And last_execution_time > DATEADD(hour, -12, GETDATE())
	ORDER BY total_elapsed_time / execution_count DESC;
Identify all slow queries taking over x seconds to execute.

Updated: Tuesday 26th June 2018, 09:02am

There are 0 comments

Leave a comment of your own

Comments are currently closed.