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
/ comments
- Community
- SQL Monitor Previous Versions
- Top 10 most expensive queries
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 lev...
0 votes
