create table #t ( query_hash varbinary(64), query_plan_hash varbinary(64), num_cache_entries bigint, execution_count bigint, min_creation_time datetime, max_last_execution_time datetime, total_worker_time bigint, min_worker_time bigint, max_worker_time bigint, avg_worker_time float ) go insert into #t select top 10 query_hash, query_plan_hash, count(*) as num_cache_entries, sum(execution_count) as execution_count, min(creation_time) as min_creation_time, max(last_execution_time) as max_last_execution_time, sum(total_worker_time) as total_worker_time, min(min_worker_time) as min_worker_time, max(max_worker_time) as max_worker_time, sum(total_worker_time) / sum(execution_count) as avg_worker_time from sys.dm_exec_query_stats group by query_hash, query_plan_hash order by total_worker_time desc; declare @query_hash varbinary(64), @query_plan_hash varbinary(64) declare @query_num int = 1 declare c1 cursor for select query_hash, query_plan_hash from #t open c1 fetch next from c1 into @query_hash, @query_plan_hash; while @@fetch_status = 0 begin select top 1 @query_num as query_rank, s.*, p.*, replace (replace (substring (st.[text], s.statement_start_offset/2 + 1, case when s.statement_end_offset = -1 then len (convert(nvarchar(max), st.[text])) else s.statement_end_offset/2 - s.statement_start_offset/2 + 1 end), char(13), ' '), char(10), ' ') as sample_statement_text from sys.dm_exec_query_stats s cross apply sys.dm_exec_query_plan(s.plan_handle) as p cross apply sys.dm_exec_sql_text(s.sql_handle) as st where s.query_hash = @query_hash and s.query_plan_hash = @query_plan_hash; fetch next from c1 into @query_hash, @query_plan_hash; select @query_num = @query_num + 1; end deallocate c1 go drop table #t go
|