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!!

Comments :

4 comments to “RED-GATE SQLMonitor Top 10 Expensive Queries”
Jan Klaverdijk said...
on 

Hi Bob, Have you been able to adjust this script to work with RedGate SQL Monitor 4.x ?

Br
Jan

Bob Mihada said...
on 

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.

Frederico said...
on 

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

Frederico said...
on 

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