Top 10 most expensive queries

daniel.brink

Hi guys,

I need some help please. I have to build and distribute an daily SSRS report showing the top 10 queries from the previous day, by execution count, total duration etc. The current RedGate provided SSRS samples (and scripts they contain) do not cover this topic. I am aware that querying the RedGate SQL Monitor database directly isn't supported and anything I build against it might break in the future, that is perfectly acceptable.

I can see the data I need in [Cluster_SqlServer_TopQueries_UnstableSamples] and figured out how to query it via your indexes and FK's. The problem is that my results do not match those shown on SQL Monitor itself. I am probably querying the tables wrong or might be using the [Id] incorrectly to get to the stored procedure name via the SqlHandle.

Does anyone perhaps have a sample script I could use as a reference?

Thank you
Daniel

daniel.brink

crickets

daniel.brink

Here is my code, but don't use it as is. I don't fully agree with the results as I do not see a number of procedures that I know should be there.

use RedGateMonitor
go

set transaction isolation level read uncommitted

select
	object_name(p.objectid, p.[dbid]) as [object],

	--calculate the difference between the latest sample and yesterday's sample
	l._ExecutionCount - f._ExecutionCount as ExecutionCount,
	l._ExecutionTime - f._ExecutionTime as ExecutionTime,
	l._LogicalReads - f._LogicalReads as LogicalReads,
	l._LogicalWrites - f._LogicalWrites as LogicalWrites,
	l._PhysicalReads - f._PhysicalReads as PhysicalReads,
	l._WorkerTime - f._WorkerTime as WorkerTime
	

from	
	(
		--get the latest sample
		select 
			s.Id,			
			max(s.CollectionDate) as LastCollectionDate
		from [data].[Cluster_SqlServer_TopQueries_StableSamples] as s 
		group by s.Id
	) as ld

	
	inner join(
		--get the latest sample for each ID for yesterday
		select 
			s.Id, 
			max(s.CollectionDate) as FirstCollectionDate		
		from [data].[Cluster_SqlServer_TopQueries_StableSamples] as s 
		where s.CollectionDate < RedGateMonitor.utils.DateTimeToTicks(cast(cast(getutcdate() as date) as datetime))
		group by s.Id
	) as fd
	on ld.Id = fd.Id
	
	--using the CollectionDate, get the latest sample data
	inner join data.Cluster_SqlServer_TopQueries_UnstableSamples as l 
	on ld.Id = l.Id
		and ld.LastCollectionDate = l.CollectionDate

	--using the CollectionDate, get the last sample taken yesterday
	inner join data.Cluster_SqlServer_TopQueries_UnstableSamples as f 
	on fd.Id = f.Id
		and fd.FirstCollectionDate = f.CollectionDate

	--get the sqlhandle	
	inner join data.Cluster_SqlServer_TopQueries_Keys as k 
	on ld.Id = k.Id
		and k._DatabaseName not in ('master', 'msdb', 'RedGateMonitor', 'tempdb', 'model', 'ReportServer', 'ReportServerTempDB')

	--use the sql handle to lookup the stored procedure name
	cross apply sys.dm_exec_sql_text(isnull(convert(varbinary(64), k._SqlHandle, 1), 0x)) as p


where 
	object_name(p.objectid, p.[dbid]) is not null
	and l._ExecutionCount - f._ExecutionCount != 0


order by [object], ExecutionCount