Hallengren SQL Server Maintenance Solution - Report on time taken to run and prepare for running commands

Hallengren - Report on time taken to run commands.sql

/*
=============================================================================
Report on time taken to run and prepare for running Hallengren commands

NOTE: Specifically UPDATE_STATISTICS can take a long time on databases with many partitions.

Finding command types get statistics for:
    SELECT DISTINCT CommandType FROM dbo.CommandLog ORDER BY CommandType;
=============================================================================
*/
-- Configurable variables
-----------------------------------------------------------------------------
DECLARE @CommandType AS TABLE (name NVARCHAR(60) PRIMARY KEY);
DECLARE
    @StartDate       AS VARCHAR(10)
    ,@EndDate        AS VARCHAR(10)
    ,@StartTimeOfDay AS VARCHAR(8)
    ,@EndTimeOfDay   AS VARCHAR(8);

-- Start and end dates to get statistics for and the time of day each day interval starts and ends
SELECT
    @StartDate       = LEFT(CONVERT(VARCHAR(50), DATEADD(DAY, -5, CURRENT_TIMESTAMP), 121), 10)
    ,@EndDate        = LEFT(CONVERT(VARCHAR(50), DATEADD(DAY, 1, CURRENT_TIMESTAMP), 121), 10)
    ,@StartTimeOfDay = '18:10:00'   -- Choose times which are likely to fall outside the interval in which the jobs are still running
    ,@EndTimeOfDay   = '18:00:00';

-- Command types to search for
INSERT INTO @CommandType (name) VALUES (N'UPDATE_STATISTICS');

-- Internal variables below - no further configuration needed
-----------------------------------------------------------------------------
DECLARE
    @StartTime       AS DATETIME2
    ,@EndTime        AS DATETIME2
SELECT
    @StartTime = CAST(@StartDate + N' ' + @StartTimeOfDay AS DATETIME2)
    ,@EndTime  = CAST(@EndDate + N' ' + @EndTimeOfDay AS DATETIME2);

IF OBJECT_ID(N'tempdb..#tmpDurations', N'U') IS NOT NULL DROP TABLE #tmpDurations;
;WITH cteIntervalsRec AS
(
    SELECT
        CAST(@StartDate + N' ' + @StartTimeOfDay AS DATETIME2)                 AS StartInterval
        ,DATEADD(DAY, 1, CAST(@StartDate + N' ' + @EndTimeOfDay AS DATETIME2)) AS EndInterval
    UNION ALL
    SELECT
        DATEADD(DAY, 1, cteIntervalsRec.StartInterval) AS StartInterval
        ,DATEADD(DAY, 1, cteIntervalsRec.EndInterval)  AS EndInterval
    FROM cteIntervalsRec
    WHERE DATEADD(DAY, 1, EndInterval) <= @EndTime
)
,cteIntervals AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY cteIntervalsRec.StartInterval) AS id
        ,cteIntervalsRec.StartInterval
        ,cteIntervalsRec.EndInterval
    FROM cteIntervalsRec
)
,cteDurations AS
(
    SELECT
        @@SERVERNAME                                           AS ServerName
        ,cl.ID
        ,ci.id                                                 AS IntervalId
        ,cl.DatabaseName
        ,cl.CommandType
        ,cl.StartTime
        ,cl.EndTime
        ,ISNULL(DATEDIFF(SECOND, cl.StartTime, cl.EndTime), 0) AS DurationSeconds -- Duration in seconds
        ,ISNULL(DATEDIFF(MINUTE, cl.StartTime, cl.EndTime), 0) AS DurationMinutes -- Duration in minutes
        ,cl.ErrorNumber
        ,cl.Command
    FROM dbo.CommandLog         AS cl
        INNER JOIN cteIntervals AS ci ON cl.StartTime     >= ci.StartInterval
                                         AND cl.StartTime <= ci.EndInterval
    WHERE
        EXISTS (SELECT 1 FROM @CommandType AS ct WHERE ct.name = cl.CommandType)
        AND cl.StartTime BETWEEN @StartTime AND @EndTime
)
,cteDurationBetween AS
(
    SELECT
        d.ID
        ,d.IntervalId
        ,d.DatabaseName
        ,CAST(DATEPART(YEAR, i.StartInterval) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, i.StartInterval) AS VARCHAR(2)), 2) + '-' + RIGHT('0' + CAST(DATEPART(DAY, i.StartInterval) AS VARCHAR(2)), 2) AS StartIntervalDate
        ,ISNULL(DATEDIFF(SECOND, LAG(d.EndTime, 1) OVER (PARTITION BY d.IntervalId, d.DatabaseName ORDER BY d.ID), d.StartTime), 0)  AS DurationFromPreviousSeconds      -- Duration in seconds between this and the previous command
        ,ISNULL(DATEDIFF(MINUTE, LAG(d.EndTime, 1) OVER (PARTITION BY d.IntervalId, d.DatabaseName ORDER BY d.ID), d.StartTime), 0)  AS DurationFromPreviousMinutes      -- Duration in minutes between this and the previous command
        ,ISNULL(DATEDIFF(SECOND, d.EndTime, LEAD(d.StartTime, 1) OVER (PARTITION BY d.IntervalId, d.DatabaseName ORDER BY d.ID)), 0) AS DurationUntilFollowingSeconds    -- Duration in seconds between this and the following command
        ,ISNULL(DATEDIFF(MINUTE, d.EndTime, LEAD(d.StartTime, 1) OVER (PARTITION BY d.IntervalId, d.DatabaseName ORDER BY d.ID)), 0) AS DurationUntilFollowingMinutes    -- Duration in minutes between this and the following command
    FROM cteDurations AS d
        INNER JOIN cteIntervals AS i ON i.id = d.IntervalId
)
SELECT
    d.ServerName
    ,d.ID
    ,d.IntervalId
    ,d.DatabaseName
    ,d.CommandType
    ,d.StartTime
    ,d.EndTime
    ,d.DurationSeconds
    ,d.DurationMinutes
    ,dbw.StartIntervalDate
    ,dbw.DurationFromPreviousSeconds
    ,dbw.DurationFromPreviousMinutes
    ,dbw.DurationUntilFollowingSeconds
    ,dbw.DurationUntilFollowingMinutes
    ,d.ErrorNumber
    ,d.Command
INTO #tmpDurations
FROM cteDurations AS d
    INNER JOIN cteDurationBetween AS dbw ON dbw.ID = d.ID
ORDER BY d.ID;

CREATE CLUSTERED INDEX [clix_tmpDurations] ON #tmpDurations (ID)

/*
-----------------------------------------------------------------------------
Report on total time spent on running command and waiting time between commands
per database, command type and interval date
-----------------------------------------------------------------------------
*/
;WITH cteReport AS
(
    SELECT
        d.ServerName
        ,d.DatabaseName
        ,d.CommandType
        ,d.StartIntervalDate
        ,MIN(d.StartTime)                   AS FirstStartTime
        ,MAX(d.EndTime)                     AS LastEndTime
        ,SUM(d.DurationSeconds)             AS SumCmdDurationSeconds
        ,SUM(d.DurationFromPreviousSeconds) AS SumBetweendCmdDurationSeconds
    FROM #tmpDurations AS d
    GROUP BY
        d.ServerName
        ,d.DatabaseName
        ,d.CommandType
        ,d.StartIntervalDate
)
SELECT
    cr.*
    ,SUM(cr.SumCmdDurationSeconds + cr.SumBetweendCmdDurationSeconds) OVER (PARTITION BY cr.StartIntervalDate
                                                                            ORDER BY cr.FirstStartTime
                                                                           ) AS RunningTotalSeconds
FROM cteReport AS cr
ORDER BY
    cr.ServerName
    ,cr.StartIntervalDate
    ,cr.FirstStartTime;

/*
-----------------------------------------------------------------------------
Output the raw data from the durations table to help verify the above report
-----------------------------------------------------------------------------
*/
SELECT td.* FROM #tmpDurations AS td ORDER BY td.ID;