Un-official, but seems to mactch up nicely.
Red-Gate SQL Monitor
--// REPLICATE RED-GATE SQLMONITOR's --// "Top 10 expensive queries" --#region DECLARATIONS SET NOCOUNT ON DECLARE @LOCAL_STOP_TIME DATETIME , @LOCAL_START_TIME DATETIME , @LOCAL_STOP_TIME_TICKS BIGINT , @LOCAL_START_TIME_TICKS BIGINT , @TIME_SPAN_MINUTES INT , @LOCAL_OFFSET INT , @SQL_SERVER_NAME VARCHAR(200) , @DSQL NVARCHAR(MAX) , @SORT_BY NVARCHAR(50) , @TOP_X INT , @PRINT_DSQL BIT --// RED-GATE VARs left as-is , @Cluster_Name NVARCHAR(450) , @Cluster_SqlServer_Name NVARCHAR(450) , @MinDate BIGINT , @MaxDate BIGINT , @MaxSamples INT --#endregion SELECT @LOCAL_OFFSET = 7 --// PST WEST SYDE , @TOP_X = 100 , @SQL_SERVER_NAME = 'BOBSWORLD' , @SORT_BY = '[Execution Count] DESC' --, @SORT_BY = '[Execution Duration (ms)] DESC' --, @SORT_BY = '[Logical Reads] DESC' --, @SORT_BY = '[Logical Writes] DESC' --, @SORT_BY = '[Physical Reads] DESC' --, @SORT_BY = '[CPU time (ms)]' , @TIME_SPAN_MINUTES = 5 , @LOCAL_STOP_TIME = GETDATE() , @PRINT_DSQL = 0 --#region GREEN CURTAIN SELECT @LOCAL_START_TIME = DATEADD(Minute, -1 * @TIME_SPAN_MINUTES, @LOCAL_STOP_TIME) , @LOCAL_STOP_TIME_TICKS = utils.DateTimeToTicks(DATEADD(HOUR, @LOCAL_OFFSET, @LOCAL_STOP_TIME)) , @LOCAL_START_TIME_TICKS = utils.DateTimeToTicks(DATEADD(HOUR, @LOCAL_OFFSET, @LOCAL_START_TIME)) --// RED-GATE VARs left as-is SELECT @Cluster_Name = @SQL_SERVER_NAME , @Cluster_SqlServer_Name = N'' , @MinDate = @LOCAL_START_TIME_TICKS , @MaxDate = @LOCAL_STOP_TIME_TICKS , @MaxSamples = 50; --#endregion --#region @DSQL: DECLARE TABLE VARABLES SELECT @DSQL = ' DECLARE @tmpM TABLE ( Id BIGINT , IdCollectionDate BIGINT , Cluster_Name NVARCHAR(450) , Cluster_SqlServer_Name NVARCHAR(450) , Cluster_SqlServer_TopQueries_DatabaseName NVARCHAR(450) , Cluster_SqlServer_TopQueries_PlanHandle NVARCHAR(450) , Cluster_SqlServer_TopQueries_StatementEnd BIGINT , Cluster_SqlServer_TopQueries_StatementStart BIGINT , CollectionDate BIGINT , Cluster_SqlServer_TopQueries_CreateDate BIGINT) DECLARE @tmpC TABLE ( Id BIGINT , IdCollectionDate BIGINT , Cluster_Name NVARCHAR(450) , Cluster_SqlServer_Name NVARCHAR(450) , Cluster_SqlServer_TopQueries_DatabaseName NVARCHAR(450) , Cluster_SqlServer_TopQueries_PlanHandle NVARCHAR(450) , Cluster_SqlServer_TopQueries_StatementEnd BIGINT , Cluster_SqlServer_TopQueries_StatementStart BIGINT , CollectionDate BIGINT , Cluster_SqlServer_TopQueries_ExecutionCount BIGINT , Cluster_SqlServer_TopQueries_ExecutionTime BIGINT , Cluster_SqlServer_TopQueries_LogicalReads BIGINT , Cluster_SqlServer_TopQueries_LogicalWrites BIGINT , Cluster_SqlServer_TopQueries_PhysicalReads BIGINT , Cluster_SqlServer_TopQueries_WorkerTime BIGINT) DECLARE @tmpD TABLE ( Id BIGINT , IdCollectionDate BIGINT , Cluster_Name NVARCHAR(450) , Cluster_SqlServer_Name NVARCHAR(450) , Cluster_SqlServer_TopQueries_DatabaseName NVARCHAR(450) , Cluster_SqlServer_TopQueries_PlanHandle NVARCHAR(450) , Cluster_SqlServer_TopQueries_StatementEnd BIGINT , Cluster_SqlServer_TopQueries_StatementStart BIGINT , CollectionDate BIGINT , Cluster_SqlServer_TopQueries_QueryText NVARCHAR(MAX)) ' --#endregion --#region @DSQL: LOAD @tmpM w/ Master Pointer(s) SELECT @DSQL = @DSQL + ' INSERT INTO @tmpM SELECT [Id] , [IdCollectionDate] , [Cluster_Name] , [Cluster_SqlServer_Name] , [Cluster_SqlServer_TopQueries_DatabaseName] , [Cluster_SqlServer_TopQueries_PlanHandle] , [Cluster_SqlServer_TopQueries_StatementEnd] , [Cluster_SqlServer_TopQueries_StatementStart] , [CollectionDate] , [Cluster_SqlServer_TopQueries_CreateDate] FROM [data].[Cluster_SqlServer_TopQueries_StableSamples_DateRange](@MinDate, @MaxDate, @MaxSamples) WHERE [Cluster_Name] = @Cluster_Name AND [Cluster_SqlServer_Name] = @Cluster_SqlServer_Name ' --#endregion --#region @DSQL: LOAD @tmpC w/ Child Pointer(s) SELECT @DSQL = @DSQL + ' INSERT INTO @tmpC SELECT [Id] , [IdCollectionDate] , [Cluster_Name] , [Cluster_SqlServer_Name] , [Cluster_SqlServer_TopQueries_DatabaseName] , [Cluster_SqlServer_TopQueries_PlanHandle] , [Cluster_SqlServer_TopQueries_StatementEnd] , [Cluster_SqlServer_TopQueries_StatementStart] , [CollectionDate] , [Cluster_SqlServer_TopQueries_ExecutionCount] , [Cluster_SqlServer_TopQueries_ExecutionTime] , [Cluster_SqlServer_TopQueries_LogicalReads] , [Cluster_SqlServer_TopQueries_LogicalWrites] , [Cluster_SqlServer_TopQueries_PhysicalReads] , [Cluster_SqlServer_TopQueries_WorkerTime] FROM [data].[Cluster_SqlServer_TopQueries_UnstableSamples_DateRange](@MinDate, @MaxDate, @MaxSamples) WHERE [Cluster_Name] = @Cluster_Name AND [Cluster_SqlServer_Name] = @Cluster_SqlServer_Name ' --#endregion --#region @DSQL: LOAD @tmpD w/ Child Details(S) SELECT @DSQL = @DSQL + ' INSERT INTO @tmpD SELECT [Id] , [IdCollectionDate] , [Cluster_Name] , [Cluster_SqlServer_Name] , [Cluster_SqlServer_TopQueries_DatabaseName] , [Cluster_SqlServer_TopQueries_PlanHandle] , [Cluster_SqlServer_TopQueries_StatementEnd] , [Cluster_SqlServer_TopQueries_StatementStart] , [CollectionDate] , [Cluster_SqlServer_TopQueries_QueryText] FROM [data].[Cluster_SqlServer_TopQueries_Instances_DateRange](@MinDate, @MaxDate, @MaxSamples) WHERE [Cluster_Name] = @Cluster_Name AND [Cluster_SqlServer_Name] = @Cluster_SqlServer_Name ' --#endregion --#region @DSQL: MAKE IT HAPPEN ;-P SELECT @DSQL = @DSQL + ' SELECT TOP (@TOP_X) MAX(TC.Cluster_SqlServer_TopQueries_ExecutionCount) - MIN(TC.Cluster_SqlServer_TopQueries_ExecutionCount) [Execution Count] , MAX(TC.Cluster_SqlServer_TopQueries_ExecutionTime) - MIN(TC.Cluster_SqlServer_TopQueries_ExecutionTime) [Execution Duration (ms)] , MAX(TC.Cluster_SqlServer_TopQueries_WorkerTime) - MIN(TC.Cluster_SqlServer_TopQueries_WorkerTime) [CPU time (ms)] , MAX(TC.Cluster_SqlServer_TopQueries_PhysicalReads) - MIN(TC.Cluster_SqlServer_TopQueries_PhysicalReads) [Physical Reads] , MAX(TC.Cluster_SqlServer_TopQueries_LogicalReads) - MIN(TC.Cluster_SqlServer_TopQueries_LogicalReads) [Logical Reads] , MAX(TC.Cluster_SqlServer_TopQueries_LogicalWrites) - MIN(TC.Cluster_SqlServer_TopQueries_LogicalWrites) [Logical Writes] , TD.Cluster_SqlServer_TopQueries_QueryText [Query Text] , TM.Cluster_SqlServer_TopQueries_DatabaseName [Database] , @LOCAL_START_TIME [@LOCAL_START_TIME] , @LOCAL_STOP_TIME [@LOCAL_STOP_TIME] , MAX(TM.Cluster_Name) SQL_SERVER_NAME FROM @tmpM AS TM JOIN @tmpC AS TC ON TM.Id = TC.Id JOIN @tmpD AS TD ON TC.ID = TD.ID GROUP BY TM.Cluster_Name , TM.Cluster_SqlServer_Name , TM.Cluster_SqlServer_TopQueries_DatabaseName , TD.Cluster_SqlServer_TopQueries_QueryText
, TM.ID
ORDER BY
' + @SORT_BY
--#endregion
--#region HAT TRICK
IF @PRINT_DSQL = 1
BEGIN
PRINT LEFT(@DSQL,4000)
PRINT SUBSTRING( @DSQL,3999,4000)
END
EXEC sp_executesql
@DSQL
,N'@Cluster_Name nvarchar(450),@Cluster_SqlServer_Name nvarchar(450),@MinDate bigint,@MaxDate bigint,@MaxSamples int,@LOCAL_STOP_TIME DATETIME,@LOCAL_START_TIME DATETIME,@TOP_X INT'
,@Cluster_Name
, @Cluster_SqlServer_Name
, @MinDate
, @MaxDate
, @MaxSamples
, @LOCAL_STOP_TIME
, @LOCAL_START_TIME
, @TOP_X
--#endregion
I needed to add in TM.ID to the last Group by clause, :) , all good again.
~ENJOY!!
Hi Bob, Have you been able to adjust this script to work with RedGate SQL Monitor 4.x ?
Br
Jan
Why yes. yes I have.
Find and replace:
Cluster_SqlServer_TopQueries_PlanHandle
with
Cluster_SqlServer_TopQueries_SqlHandle
BUT, those little stinker(kidding) decided to COMPRESS the QueryText. To overcome this tragedy i needed to finagle a CLR assembly(Orz to MH!!!).
--#region @DSQL: LOAD @tmpD w/ Child Details(S)
" dbo.DeserializeCompressedString( [Cluster_SqlServer_TopQueries_QueryText])"
Will post CLR later, or if anyone pesters me here.
Hi Bob,
Just wondering if you ever posted the CLR code. Looking at doing analysis of SQL Monitor myself and also bumped into the same issue.
Thanks
Frederico
no bother - sorted now.
for those interested on this the _queryText is stored after being compressed through a gzipstream.
one way to uncompress it to cast to a varchar(max), then to a varbinary, then pass it to a gzipstream to uncompress. easy to do with a CLR like this one http://aboutsqlserver.com/2015/04/07/compressing-lob-xml-data-in-the-database/ - just change from deflatestream to gzipstream