Provides the ability to assess stored procedure performance against history using data in the SentryOne database

GetProcPerfAgainstHistory.sql

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

IF NOT EXISTS
    (
        SELECT *
        FROM [sys].[procedures]
        WHERE [object_id] = OBJECT_ID(N'[dbo].[GetProcPerfAgainstHistory]', 'P')
    )
    EXEC [sys].[sp_executesql] N'CREATE PROCEDURE [dbo].[GetProcPerfAgainstHistory] AS SELECT 1;';
GO

ALTER PROCEDURE [dbo].[GetProcPerfAgainstHistory]
    @EndTimeCurrentHour DATETIME = NULL
  , @DaysHistoryToCompare INT = 30
  , @AggregatePeriodMin INT = 15
  , @MinIncreasePct INT = 100
  , @EvalCondition VARCHAR(10) = 'Duration' /* CPU | Duration | Execs | Reads | Writes */
  , @ProcedureName NVARCHAR(128) = NULL

/*
*******************************************************************************
**
** Description: Uses the Sentry data on proc stats to compare current running stats against the historical information.
				It is limited to what Sentry can capture so not much use for reporting, very useful for triage however.
				By default will compare the last 15 minutes to the same 15 minutes for the prior 30 days (excluding weekends).
				Filters out data that is below 100% of blended average.
				Average is calculated across a set of servers, so it does not matter if an AG is failed over.

				Params:
				@EndTimeCurrentHour - default null, use this if you want to look at a time period other than now
				@DaysHistoryToCompare - how far to look back in history - defaults to 30 days (excludes weekends from averages)
				@AggregatePeriodMin  - The period of time to aggregate - defaults to 15 minutes
				@MinIncreasePct  - Minimum increase that we want to report on, helps to filter out noise
				@EvalCondition  - defaults to Duration, can be one of CPU | Duration | Execs | Reads | Writes
				@ProcedureName  - reports on an individual procedure across all environments rather than all procs


	Important note: There is a section with a case statement (shown below). This should be adjusted to reflect your AGs so that the data can be appropriately grouped.
					Yes, this could be done automatically, but for now it isn't. Either use like for your machines or hard code actual servers
                , CASE WHEN [eventsourceconnections].[HostName] LIKE '%AG1%' THEN 'AG1'
                      WHEN [eventsourceconnections].[HostName] LIKE '%AG2%' THEN 'AG2'
					  WHEN [eventsourceconnections].[HostName] = 'AGServer3a' THEN 'AG3'
					  WHEN [eventsourceconnections].[HostName] = 'AGServer3b' THEN 'AG3'
                      ELSE [eventsourceconnections].[HostName]
                  END                                 AS [HostName]
                  
   Limitations: requires the data be captured by SentryOne - see the note at https://docs.sentryone.com/help/top-sql on "what's capture for Query Stats"
**
*******************************************************************************
*/

AS
    IF @EndTimeCurrentHour IS NULL
        SELECT @EndTimeCurrentHour = GETUTCDATE();


    DECLARE @StartTimeCurrentHour DATETIME;
    SELECT @StartTimeCurrentHour = DATEADD(MINUTE, -@AggregatePeriodMin, @EndTimeCurrentHour);

	/* We need not be concerned with dirty reads and don't want to block writes (this is ideally run against a readable secondary of the SentryOne database) */
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    IF
        (
            SELECT OBJECT_ID(N'tempdb..#UngroupedData')
        ) IS NOT NULL
        DROP TABLE [#UngroupedData];

    IF
        (
            SELECT OBJECT_ID(N'tempdb..#HistoricalTimePeriods')
        ) IS NOT NULL
        DROP TABLE [#HistoricalTimePeriods];

    IF
        (
            SELECT OBJECT_ID(N'tempdb..#FinalResults')
        ) IS NOT NULL
        DROP TABLE [#FinalResults];

    WITH [cteDates]
    AS (
           SELECT @StartTimeCurrentHour AS [StartTime]
                , @EndTimeCurrentHour   AS [EndTime]
                , 0                     AS [Level]
           UNION ALL
           SELECT DATEADD(HOUR, -24, [cteDates].[StartTime])
                , DATEADD(HOUR, -24, [cteDates].[EndTime])
                , [cteDates].[Level] + 1
           FROM [cteDates]
           WHERE [cteDates].[Level] <= @DaysHistoryToCompare
       )
    SELECT *
    INTO [#HistoricalTimePeriods]
    FROM [cteDates]
    OPTION (MAXRECURSION 0);

    WITH [cteEvs]
    AS (
           SELECT [ESC].[ID]
                , [ESC].[EventSourceConnectionTypeID]
                , [ESC].[ObjectName]
                , [d].[HostName]
           FROM
               [dbo].[EventSourceConnection] AS [ESC]
               JOIN [dbo].[Device]           AS [d] ON [ESC].[DeviceID] = [d].[ID]
       )
       , [CTEProcedures]
    AS (
           SELECT [ps].[ID]
                , [ps].[DatabaseName]
                , [ps].[ObjectName]
				/* The case statement below should be adjusted to reflect your AGs so that the data can be appropriately grouped */
                , CASE WHEN [eventsourceconnections].[HostName] LIKE '%AG1%' THEN 'AG1'
                      WHEN [eventsourceconnections].[HostName] LIKE '%AG2%' THEN 'AG2'
                      ELSE [eventsourceconnections].[HostName]
                  END                                 AS [HostName]
                , [eventsourceconnections].[HostName] AS [CurrentlyRunningServer]
           FROM
               [dbo].[ProcedureStats] AS [ps]
               INNER JOIN [cteEvs]    AS [eventsourceconnections] ON [ps].[EventSourceConnectionID] = [eventsourceconnections].[ID]
           WHERE [ps].[ObjectName] = COALESCE(@ProcedureName, [ps].[ObjectName])
       )
       , [cteResults]
    AS (
           SELECT [c].[HostName]
                , [c].[DatabaseName]
                , [c].[ObjectName]
                , [c].[CurrentlyRunningServer]
                , SUM(CONVERT(BIGINT, [ps].[ExecutionCountDelta]))     AS [TotalExecutionCount]
                , SUM(CONVERT(BIGINT, [ps].[ElapsedTimeDelta])) / 1000 AS [TotalDuration]
                , SUM(CONVERT(BIGINT, [ps].[WorkerTimeDelta])) / 1000  AS [TotalCPU]
                , SUM(CONVERT(BIGINT, [ps].[LogicalReadsDelta]))       AS [TotalReads]
                , SUM(CONVERT(BIGINT, [ps].[LogicalWritesDelta]))      AS [TotalWrites]
                , [F].[Level]
           FROM
               [CTEProcedures]                    AS [c]
               JOIN [dbo].[ProcedureStatsHistory] AS [ps] ON [c].[ID] = [ps].[ProcedureStatsID]
               CROSS APPLY
               (
                   SELECT *
                   FROM [#HistoricalTimePeriods]
                   WHERE
                       DATEPART(WEEKDAY, [StartTime]) NOT IN ( 7, 1 )
               )                                  AS [F]
           WHERE
               [ps].[StartTimeUtc] >= [F].[StartTime]
               AND [ps].[EndTimeUtc] < [F].[EndTime]
               AND [c].[DatabaseName] NOT IN ( 'master', 'mssqlsystemresource' )
           GROUP BY
               [c].[HostName]
             , [c].[CurrentlyRunningServer]
             , [c].[DatabaseName]
             , [c].[ObjectName]
             , [F].[Level]
       )
    SELECT [cteResults].[HostName]
         , [cteResults].[CurrentlyRunningServer]
         , [cteResults].[DatabaseName]
         , [cteResults].[ObjectName] --UTCHour,
         , [cteResults].[TotalExecutionCount]                                                                 AS [ExecsForPeriod]
         , CONVERT(NUMERIC(20, 2), [cteResults].[TotalDuration] / ([cteResults].[TotalExecutionCount] * 1.0)) AS [AvgCallDurationForPeriod]
         , [cteResults].[TotalCPU]                                                                            AS [CPUForPeriod]
         , CONVERT(NUMERIC(20, 2), [cteResults].[TotalCPU] / ([cteResults].[TotalExecutionCount] * 1.0))      AS [AvgCPUPerCallForPeriod]
         , CONVERT(NUMERIC(20, 2), [cteResults].[TotalReads] / ([cteResults].[TotalExecutionCount] * 1.0))    AS [AvgReadsPerCallForPeriod]
         , CONVERT(NUMERIC(20, 2), [cteResults].[TotalWrites] / ([cteResults].[TotalExecutionCount] * 1.0))   AS [AvgWritesPerCallForPeriod]
         , [cteResults].[Level]
    INTO [#UngroupedData]
    FROM [cteResults];
    WITH [ctePriorAverages]
    AS (
           SELECT [HostName]
                , [DatabaseName]
                , [ObjectName]
                , ''                               AS [CurrentlyRunningServer]
                , AVG([ExecsForPeriod])            AS [AvgHistoricalExecs]
                , AVG([AvgCallDurationForPeriod])  AS [AvgHistoricalDuration]
                , AVG([AvgCPUPerCallForPeriod])    AS [AvgHistoricalCPU]
                , AVG([AvgReadsPerCallForPeriod])  AS [AvgHistoricalReads]
                , AVG([AvgWritesPerCallForPeriod]) AS [AvgHistoricalWrites]
           FROM [#UngroupedData]
           WHERE [Level] > 0
           GROUP BY
               [HostName]
             , [DatabaseName]
             , [ObjectName]
       )
       , [cteCurrentData]
    AS (
           SELECT [HostName]
                , [DatabaseName]
                , [ObjectName]
                , [CurrentlyRunningServer]
                , AVG([ExecsForPeriod])            AS [AvgCurrentExecs]
                , AVG([AvgCallDurationForPeriod])  AS [AvgCurrentDuration]
                , AVG([AvgCPUPerCallForPeriod])    AS [AvgCurrentCPU]
                , AVG([AvgReadsPerCallForPeriod])  AS [AvgCurrentReads]
                , AVG([AvgWritesPerCallForPeriod]) AS [AvgCurrentWrites]
           FROM [#UngroupedData]
           WHERE [Level] = 0
           GROUP BY
               [HostName]
             , [DatabaseName]
             , [ObjectName]
             , [CurrentlyRunningServer]
       )
    SELECT CAST(@EndTimeCurrentHour AS DATETIME2(0))                                           AS [EndTime]
         , [c].[HostName]                                                                      AS [Host/Site]
         , [c].[DatabaseName]
         , [c].[ObjectName]
         , [c].[CurrentlyRunningServer]
         , CONVERT(NUMERIC(20, 2), ([c].[AvgCurrentExecs] / ([p].[AvgHistoricalExecs] * 1.0))) AS [PctExecsvsHistorical]
         , CONVERT(NUMERIC(20, 2), ([c].[AvgCurrentDuration] / [p].[AvgHistoricalDuration]))   AS [PctDurationvsHistorical]
         , CONVERT(NUMERIC(20, 2), ([c].[AvgCurrentCPU] / [p].[AvgHistoricalCPU]))             AS [PctCPUvsHistorical]
         , CONVERT(NUMERIC(20, 2), ([c].[AvgCurrentReads] / [p].[AvgHistoricalReads]))         AS [PctReadsvsHistorical]
         , CASE WHEN [c].[AvgCurrentWrites] = 0 THEN 1
               WHEN [p].[AvgHistoricalWrites] = 0 THEN 1
               ELSE CONVERT(NUMERIC(20, 2), ([c].[AvgCurrentWrites] / [p].[AvgHistoricalWrites]))
           END                                                                                 AS [PctWritesvsHistorical]
         , CONVERT(NUMERIC(20, 0), [c].[AvgCurrentExecs])                                      AS [AvgCurrentExecs]
         , CONVERT(NUMERIC(20, 0), [p].[AvgHistoricalExecs])                                   AS [AvgHistoricalExecs]
         , CONVERT(NUMERIC(20, 2), [c].[AvgCurrentDuration])                                   AS [AvgCurrentDuration/ms]
         , CONVERT(NUMERIC(20, 2), [p].[AvgHistoricalDuration])                                AS [AvgHistoricalDuration/ms]
         , CONVERT(NUMERIC(20, 2), [c].[AvgCurrentCPU])                                        AS [AvgCurrentCPU/ms]
         , CONVERT(NUMERIC(20, 2), [p].[AvgHistoricalCPU])                                     AS [AvgHistoricalCPU/ms]
         , CONVERT(NUMERIC(20, 2), [c].[AvgCurrentReads])                                      AS [AvgCurrentReads]
         , CONVERT(NUMERIC(20, 2), [p].[AvgHistoricalReads])                                   AS [AvgHistoricalReads]
         , CONVERT(NUMERIC(20, 2), [c].[AvgCurrentWrites])                                     AS [AvgCurrentWrites]
         , CONVERT(NUMERIC(20, 2), [p].[AvgHistoricalWrites])                                  AS [AvgHistoricalWrites]
    INTO [#FinalResults]
    FROM
        [cteCurrentData]        AS [c]
        JOIN [ctePriorAverages] AS [p] ON [p].[ObjectName] = [c].[ObjectName]
                                          AND [p].[HostName] = [c].[HostName];

    SELECT [EndTime]
         , [Host/Site] + '  (' + [CurrentlyRunningServer] + ')' AS [Site/Server]
         , [DatabaseName]
         , [ObjectName]
         , FORMAT([PctExecsvsHistorical], '###,###%')           AS [PctExecsvsHist]
         , FORMAT([PctDurationvsHistorical], '###,###%')        AS [PctDurationvsHist]
         , FORMAT([PctCPUvsHistorical], '###,###%')             AS [PctCPUvsHist]
         , FORMAT([PctReadsvsHistorical], '###,###%')           AS [PctReadsvsHist]
         , FORMAT([PctWritesvsHistorical], '###,###%')          AS [PctWritesvsHist]
         , FORMAT([AvgCurrentExecs], '###,###,###.##')          AS [AvgCurrentExecs]
         , FORMAT([AvgHistoricalExecs], '###,###,###.##')       AS [AvgHistoricalExecs]
         , FORMAT([AvgCurrentDuration/ms], '###,###,###.##')    AS [AvgCurrentDuration/ms]
         , FORMAT([AvgHistoricalDuration/ms], '###,###,###.##') AS [AvgHistoricalDuration/ms]
         , FORMAT([AvgCurrentCPU/ms], '###,###,###.##')         AS [AvgCurrentCPU/ms]
         , FORMAT([AvgHistoricalCPU/ms], '###,###,###.##')      AS [AvgHistoricalCPU/ms]
         , FORMAT([AvgCurrentReads], '###,###,###')             AS [AvgCurrentReads]
         , FORMAT([AvgHistoricalReads], '###,###,###')          AS [AvgHistoricalReads]
         , FORMAT([AvgCurrentWrites], '###,###,###')            AS [AvgCurrentWrites]
         , FORMAT([AvgHistoricalWrites], '###,###,###')         AS [AvgHistoricalWrites]
    FROM [#FinalResults]
    WHERE
        CASE WHEN @EvalCondition = 'Duration' THEN [PctDurationvsHistorical]
            WHEN @EvalCondition = 'CPU' THEN [PctCPUvsHistorical]
            WHEN @EvalCondition = 'Reads' THEN [PctReadsvsHistorical]
            WHEN @EvalCondition = 'Writes' THEN [PctWritesvsHistorical]
            WHEN @EvalCondition = 'Execs' THEN [PctExecsvsHistorical]
            ELSE [PctDurationvsHistorical]
        END > @MinIncreasePct / 100
        AND [AvgCurrentExecs] > 15
    ORDER BY
        CASE WHEN @EvalCondition = 'Duration' THEN [PctDurationvsHistorical] END DESC
      , CASE WHEN @EvalCondition = 'CPU' THEN [PctCPUvsHistorical] END DESC
      , CASE WHEN @EvalCondition = 'Reads' THEN [PctReadsvsHistorical] END DESC
      , CASE WHEN @EvalCondition = 'Writes' THEN [PctWritesvsHistorical] END DESC
      , CASE WHEN @EvalCondition = 'Execs' THEN [PctExecsvsHistorical] END DESC;
GO