Slow SQL queries with CallSiteHash

SlowQueries.sql

;

WITH QueryWithPositions
AS (
	SELECT qs.creation_time
		,qs.last_execution_time
		,qs.total_physical_reads
		,qs.total_logical_reads
		,qs.total_logical_writes
		,qs.execution_count
		,qs.total_worker_time
		,qs.total_elapsed_time
		,st.TEXT
		,qs.statement_start_offset
		,qs.statement_end_offset
		,PATINDEX('%-- #[0-9a-fA-F]%', st.TEXT) AS hash_pos
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
	)
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 AS avg_elapsed_time
	,CASE 
		WHEN hash_pos > 0
		THEN SUBSTRING(TEXT, hash_pos + 3, PATINDEX('%[ ' + CHAR(13) + CHAR(10) + ']%', SUBSTRING(TEXT, hash_pos + 3, 4000)) - 1)
		ELSE NULL
	 END AS hash_tag
	,TEXT
	,SUBSTRING(TEXT, (statement_start_offset / 2) + 1, (
			(
				CASE statement_end_offset
					WHEN - 1
						THEN DATALENGTH(TEXT)
					ELSE statement_end_offset
					END - statement_start_offset
				) / 2
			) + 1) AS statement_text
FROM QueryWithPositions
ORDER BY total_elapsed_time / execution_count DESC;