Top 10 most expensive queries

daniel.brinkdaniel.brink Posts: 4
edited June 15, 2016 2:32AM in SQL Monitor Previous Versions
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

Comments

  • 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
    
Sign In or Register to comment.