How can we help you today? How can we help you today?
daniel.brink
Activity overview Badges (0) Posts (1) Comments (2)

Activity overview

Latest activity by 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 / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. 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
crickets [image] / comments
  1. Community
  2. SQL Monitor Previous Versions
  3. Top 10 most expensive queries
crickets
0 votes
Top 10 most expensive queries
  1. Community
  2. 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 p...
1 follower 2 comments 0 votes
Total activity
4
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1