Comprehensive SQL Performance Monitoring and Optimisation Script

-- *************************************************************************************************
-- Author j.roberts 
-- Date 11 July 2024
-- Reports costs/statistics of SQL statements for given time on given database
-- *************************************************************************************************
GO  
DECLARE @Database      sysname = 'MyDb', -- Set the database name
        @MonitorPeriod varchar(10) = '01:00:00' -- 1 hour -- set this to the time period you want to monitor for
BEGIN    
    
    SET NOCOUNT ON;
    SET ARITHABORT OFF;
    SET ANSI_WARNINGS OFF;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;    

    DROP TABLE IF EXISTS #CombinedResults;

    CREATE TABLE #CombinedResults
    (
        DBName sysname,
        QueryText varchar(MAX),
        Execution_count bigint,
        total_cpu_time bigint,
        total_IO bigint,
        total_physical_reads bigint,
        total_logical_reads bigint,
        total_logical_writes bigint,
        total_elapsed_time bigint,
        Identifier tinyint
       ,plan_handle varbinary(64) not null
       ,last_execution_time datetime
    );

    DECLARE @Iteration tinyint = 1;    
    WHILE @Iteration <= 2 BEGIN
        ;WITH PlanStats AS
        (
        SELECT st.dbid,
               st.text AS QueryText,
               cp.plan_handle,
               MAX(cp.usecounts) AS Execution_count,
               SUM(qs.total_worker_time) AS total_cpu_time,
               SUM(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) AS total_IO,
               SUM(qs.total_physical_reads) AS total_physical_reads,
               SUM(qs.total_logical_reads) AS total_logical_reads,
               SUM(qs.total_logical_writes) AS total_logical_writes,
               SUM(qs.total_elapsed_time) AS total_elapsed_time,
               MAX(qs.last_execution_time) AS last_execution_time
        FROM sys.dm_exec_cached_plans cp
        INNER JOIN sys.dm_exec_query_stats qs 
                ON cp.plan_handle = qs.plan_handle
        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
        WHERE DB_NAME(st.dbid) IS NOT NULL
          AND DB_NAME(st.dbid) LIKE @Database
        GROUP BY st.dbid, st.text, cp.plan_handle
        )
        INSERT INTO #CombinedResults
        SELECT DB_NAME(ps.dbid) AS DBName,
               ps.QueryText,
               SUM(ps.Execution_count) AS Execution_count,
               SUM(ps.total_cpu_time) AS total_cpu_time,
               SUM(ps.total_IO) AS total_IO,
               SUM(ps.total_physical_reads) AS total_physical_reads,
               SUM(ps.total_logical_reads) AS total_logical_reads,
               SUM(ps.total_logical_writes) AS total_logical_writes,
               SUM(ps.total_elapsed_time) AS total_elapsed_time,
               @Iteration AS Identifier
              ,ps.plan_handle
              ,max(ps.last_execution_time)
        FROM PlanStats ps
        GROUP BY ps.dbid, ps.QueryText, plan_handle;

        -- Wait for the specified monitoring period during the first iteration
        IF @Iteration = 1 BEGIN
            WAITFOR DELAY @MonitorPeriod;
        END

        SET @Iteration = @Iteration + 1;
    
    END        
  

-- ************************************************************************************************
-- Results
-- ************************************************************************************************

    SELECT ISNULL(A.DBName, B.DBName)                                            AS DBName,    
           ISNULL(A.QueryText, B.QueryText)                                      AS QueryText,    
           ISNULL(A.Execution_count, 0) - ISNULL(B.Execution_count, 0)           AS Execution_count,    
           ISNULL(A.total_cpu_time, 0) - ISNULL(B.total_cpu_time, 0)             AS total_cpu_time,            
           (ISNULL(A.total_cpu_time,0) - ISNULL(B.total_cpu_time, 0)) / NULLIF(ISNULL(A.Execution_count,0) - ISNULL(B.Execution_count, 0), 0) AS avg_cpu_time,
           ISNULL(A.total_IO, 0) - ISNULL(B.total_IO, 0)                         AS total_IO,             
           (ISNULL(A.total_IO,0) - ISNULL(B.total_IO, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_total_IO,
           ISNULL(A.total_physical_reads, 0) - ISNULL(B.total_physical_reads, 0) AS total_physical_reads,         
           (ISNULL(A.total_physical_reads,0) - ISNULL(B.total_physical_reads, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_physical_read,
           ISNULL(A.total_logical_reads, 0) - ISNULL(B.total_logical_reads, 0)   AS total_logical_reads,             
           (ISNULL(A.total_logical_reads, 0) - ISNULL(B.total_logical_reads, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_logical_read,
           ISNULL(A.total_logical_writes, 0) - ISNULL(B.total_logical_writes, 0) AS total_logical_writes,             
           (ISNULL(A.total_logical_writes, 0) - ISNULL(B.total_logical_writes, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_logical_writes,
           ISNULL(A.total_elapsed_time, 0) - ISNULL(B.total_elapsed_time, 0)     AS total_elapsed_time,            
           (ISNULL(A.total_elapsed_time, 0) - ISNULL(B.total_elapsed_time, 0)) / NULLIF(ISNULL(A.execution_count, 0) - ISNULL(B.execution_count, 0), 0) AS avg_elapsed_time,
           ISNULL(A.last_execution_time, A.last_execution_time) last_execution_time
      FROM (SELECT * FROM #CombinedResults WHERE Identifier = 2) A
      LEFT JOIN (SELECT * FROM #CombinedResults WHERE Identifier = 1) B
             ON A.DBName = B.DBName
            AND A.QueryText = B.QueryText
            AND A.plan_handle = B.plan_handle
     WHERE ISNULL(A.Execution_count, 0) - ISNULL(B.Execution_count, 0) <> 0
     ORDER BY 4 DESC;     

    DROP TABLE #CombinedResults;
END
GO