RED-GATE SQLMonitor Top 10 Expensive Queries

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
 

There seems to be something else that needs to be call prior to this, to reset or refresh a counter or position. I get accurate numbers, for one server and was for another, but over time the latter got stale, it seems.

I needed to add in TM.ID to the last Group by clause, :) , all good again.


~ENJOY!!