SQL Server 2025: PRODUCT() Aggregate Function Explained (Part 4)

Leave a Comment / Articles / By

SQL Server 2025: The New PRODUCT() Aggregate Function**

Introduction

SQL Server 2025 introduces a small but surprisingly powerful new aggregate function: PRODUCT(). At first glance, it seems simple. SUM adds values, AVG averages them, COUNT counts them, and now PRODUCT multiplies them. But this addition opens doors to analytical patterns that were impossible or overly complex in previous versions.

If you’ve ever had to calculate compounded metrics, geometric scaling factors, probabilistic sequences, or chained multiplicative values, you probably wrote a cursor, XML path trick, or CLR function. PRODUCT() finally gives us a native, optimized way to perform multiplicative aggregation directly inside T-SQL.

What PRODUCT() Does

PRODUCT() returns the product of all numeric values in an expression.

Example:

SELECT PRODUCT(value) AS ProductValue
FROM dbo.Numbers;

If your values are:

2
5
4

The result is:

40

Simple concept, but incredibly useful.

Supported Data Types

SQL Server allows PRODUCT() on:

• INT
• BIGINT
• DECIMAL
• FLOAT
• REAL
• NUMERIC

NULL values are ignored by default.

Basic Examples

Here’s the simplest example:

CREATE TABLE PlayerMultipliers (
    PlayerId INT,
    Multiplier DECIMAL(10,4)
);
INSERT INTO PlayerMultipliers VALUES
(1, 1.05),
(1, 1.10),
(1, 0.95);

Compute the compounded multiplier:

SELECT PlayerId,
       PRODUCT(Multiplier) AS FinalRating
FROM PlayerMultipliers
GROUP BY PlayerId;

This is perfect for:

• Game ranking multipliers
• Skill curves
• Probability chains
• Machine learning feature scaling
• Finance and interest calculations
• Growth/decay modeling

How PRODUCT() Behaves

1. Ignores NULLs

SELECT PRODUCT(value)
FROM (VALUES (2),(NULL),(3)) AS x(value);

Result: 6

2. Returns NULL if all values are NULL

SELECT PRODUCT(value)
FROM (VALUES (NULL),(NULL)) AS x(value);

Result: NULL

3. Accepts DISTINCT

SELECT PRODUCT(DISTINCT value)
FROM dbo.Factors;

4. Can overflow

If the result exceeds the target type, SQL Server throws an arithmetic overflow.
Use DECIMAL(38,…) where needed.

Use Cases You Can Apply Immediately

Below are real-world patterns where PRODUCT() simplifies code.

1. Compounded win-rate factor






	

		

			

SELECT PlayerId,
       PRODUCT(WinFactor) AS WinScore
FROM PlayerStats
GROUP BY PlayerId;

		

	






2. Probability of sequential events





If events A, B, and C have probabilities:





0.9, 0.7, 0.4





Then:





	

		

			

SELECT PRODUCT(probability) AS CombinedProbability
FROM EventChain;

		

	






	

		

			

SELECT PRODUCT(probability) AS CombinedProbability
FROM EventChain;

		

	






Result: 0.252





3. Geometric mean (classic use case)





Geometric mean = PRODUCT(x) ^ (1/count)





	

		

			

SELECT EXP(AVG(LOG(Value))) AS GeometricMean
FROM Metrics;

		

	






Or with PRODUCT():





	

		

			

SELECT POWER(PRODUCT(Value), 1.0 / COUNT(*)) AS GeometricMean
FROM Metrics;

		

	






4. Financial compounding





	

		

			

SELECT PRODUCT(1 + Rate) - 1 AS CompoundReturn
FROM DailyRates;

		

	






5. Damage scaling in games





	

		

			

SELECT PlayerId,
       Product(DamageMultiplier) AS FinalDamage
FROM DamageModifiers
GROUP BY PlayerId;

		

	






This fits perfectly into your esports + SQL analytics pipelines.










Performance Considerations





PRODUCT() is fast. It’s implemented as a native aggregate with these behaviors:





• Streaming through rows
• No repeated casting
• No UDF overhead
• Uses numeric accumulation
• Matches SUM(), AVG(), MIN(), MAX() performance class





Indexing
PRODUCT() benefits from:





• Covering indexes
• Columnstore indexes
• Big data warehouse structures





Columnstore especially accelerates it for large scan workloads.










Comparison with Old Methods





Before SQL Server 2025, DBAs used:





1. XML multiplication hack





Not pretty.





2. CLR UDF





Required deployment permissions and extra overhead.





3. Loop or cursor





Slow and not scalable.





4. Precomputed values





Complex in ETL pipelines.





Now we simply write:





	

		

			

SELECT PRODUCT(MyValue)
FROM MyTable;

		

	






And we’re done.










Examples with Grouping Sets





You can combine PRODUCT() with modern grouping logic:





	

		

			

SELECT 
    Region,
    PRODUCT(GrowthRate) AS FinalGrowth
FROM Sales2025
GROUP BY GROUPING SETS
(
    (Region),
    ()
);

		

	






This gives:





• Per-region compounded growth
• Global compounded growth in a single query










Edge Case Testing





Zero values





If any value is zero, the result is zero.





Negative values





Odd number of negatives → negative product
Even number → positive product





NULL handling





Already covered above.










Workshop: Hands-on With PRODUCT()





Step 1. Create a table










	

		

			

CREATE TABLE GrowthRates (
    Id INT IDENTITY,
    Rate DECIMAL(10,4)
);

		

	






Step 2. Insert values





	

		

			

INSERT INTO GrowthRates (Rate)
VALUES (1.02), (1.05), (0.97), (1.01);

		

	






Step 3. Compute compounded growth





	

		

			

SELECT PRODUCT(Rate) AS GrowthFactor
FROM GrowthRates;

		

	






Step 4. Test NULLs





	

		

			

INSERT INTO GrowthRates VALUES (NULL);
SELECT PRODUCT(Rate) FROM GrowthRates;

		

	






Step 5. Financial example





	

		

			

SELECT PRODUCT(1 + Rate) - 1 AS PercentReturn
FROM GrowthRates;

		

	






Step 6. Advanced: geometric mean





	

		

			

SELECT POWER(PRODUCT(Rate), 1.0 / COUNT(Rate)) AS GeoMean
FROM GrowthRates;

		

	






Final Thoughts





PRODUCT() might not seem flashy compared to JSON indexes or REST endpoint calls, but this function fills a gap that developers, data scientists, and DBAs have been working around for years. It simplifies analytical queries, avoids ugly hacks, and enables cleaner modeling of growth, probabilities, compounding, and game analytics.





Tomorrow, Day 5 introduces the BASE64_ENCODE() and BASE64_DECODE() functions. These get extremely useful when working with REST APIs, security tokens, and payload transport.










References





• PRODUCT() Function
https://learn.microsoft.com/sql/t-sql/functions/product-transact-sql





• Aggregate Functions
https://learn.microsoft.com/sql/t-sql/functions/aggregate-functions





• SQL Server 2025 New Features
https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2025





TEST Block





	

		

			

USE YourExistingDB;
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
/*==============================================================
  1. SCHEMA
==============================================================*/
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'monitor')
BEGIN
    EXEC ('CREATE SCHEMA monitor AUTHORIZATION dbo;');
END
GO
/*==============================================================
  2. TABLES
==============================================================*/
IF OBJECT_ID('monitor.InstanceHealthSnapshot','U') IS NULL
BEGIN
    CREATE TABLE monitor.InstanceHealthSnapshot
    (
        SnapshotID                   BIGINT IDENTITY(1,1) PRIMARY KEY,
        SnapshotTime                 DATETIME2(0) NOT NULL DEFAULT SYSDATETIME(),
        ServerName                   SYSNAME      NOT NULL DEFAULT @@SERVERNAME,
        ActiveRequests               INT          NULL,
        LongRunningRequests          INT          NULL,
        BlockingSessions             INT          NULL,
        RunnableTasks                INT          NULL,
        SqlMaxMemoryMB               INT          NULL,
        SqlTotalServerMemoryMB       BIGINT       NULL,
        SqlTargetServerMemoryMB      BIGINT       NULL,
        OsTotalMemoryMB              BIGINT       NULL,
        OsAvailableMemoryMB          BIGINT       NULL,
        OsMemoryStateDesc            NVARCHAR(256) NULL,
        PageLifeExpectancy           BIGINT       NULL,
        TempDbDataFileCount          INT          NULL,
        TempDbDistinctSizeCount      INT          NULL,
        TempDbDistinctGrowthCount    INT          NULL,
        CpuCount                     INT          NULL,
        MaxDop                       INT          NULL,
        CostThreshold                INT          NULL
    );
END
GO
IF OBJECT_ID('monitor.PerformanceSnapshot','U') IS NULL
BEGIN
    CREATE TABLE monitor.PerformanceSnapshot
    (
        SnapshotID                   BIGINT IDENTITY(1,1) PRIMARY KEY,
        SnapshotTime                 DATETIME2(0) NOT NULL DEFAULT SYSDATETIME(),
        ServerName                   SYSNAME      NOT NULL DEFAULT @@SERVERNAME,
        CpuPercent                   INT          NULL,
        SqlCpuPercent                INT          NULL,
        DiskReadLatencyMs            DECIMAL(18,2) NULL,
        DiskWriteLatencyMs           DECIMAL(18,2) NULL,
        UserConnections              INT          NULL
    );
END
GO
IF OBJECT_ID('monitor.LongRunningRequestSnapshot','U') IS NULL
BEGIN
    CREATE TABLE monitor.LongRunningRequestSnapshot
    (
        SnapshotID                   BIGINT        NOT NULL,
        SnapshotTime                 DATETIME2(0)  NOT NULL,
        session_id                   SMALLINT      NULL,
        status                       NVARCHAR(60)  NULL,
        blocking_session_id          SMALLINT      NULL,
        database_name                SYSNAME       NULL,
        login_name                   NVARCHAR(128) NULL,
        host_name                    NVARCHAR(128) NULL,
        program_name                 NVARCHAR(256) NULL,
        wait_type                    NVARCHAR(120) NULL,
        cpu_time_ms                  INT           NULL,
        total_elapsed_time_ms        BIGINT        NULL,
        logical_reads                BIGINT        NULL,
        reads                        BIGINT        NULL,
        writes                       BIGINT        NULL,
        sql_text                     NVARCHAR(MAX) NULL
    );
END
GO
IF OBJECT_ID('monitor.AGHealthSnapshot','U') IS NULL
BEGIN
    CREATE TABLE monitor.AGHealthSnapshot
    (
        SnapshotID                    BIGINT        NOT NULL,
        SnapshotTime                  DATETIME2(0)  NOT NULL,
        AGName                        SYSNAME       NULL,
        ReplicaServerName             SYSNAME       NULL,
        RoleDesc                      NVARCHAR(60)  NULL,
        ConnectedStateDesc            NVARCHAR(60)  NULL,
        ReplicaSyncHealthDesc         NVARCHAR(60)  NULL,
        DatabaseName                  SYSNAME       NULL,
        DbSyncStateDesc               NVARCHAR(60)  NULL,
        IsSuspended                   BIT           NULL,
        LogSendQueueSizeKB            BIGINT        NULL,
        RedoQueueSizeKB               BIGINT        NULL,
        LogSendRateKBps               BIGINT        NULL,
        RedoRateKBps                  BIGINT        NULL,
        LastCommitTime                DATETIME      NULL
    );
END
GO
IF OBJECT_ID('monitor.AGSeedingSnapshot','U') IS NULL
BEGIN
    CREATE TABLE monitor.AGSeedingSnapshot
    (
        SnapshotID                    BIGINT        NOT NULL,
        SnapshotTime                  DATETIME2(0)  NOT NULL,
        AGName                        SYSNAME       NULL,
        ReplicaServerName             SYSNAME       NULL,
        DatabaseName                  SYSNAME       NULL,
        CurrentState                  NVARCHAR(60)  NULL,
        FailureState                  NVARCHAR(60)  NULL,
        ErrorCode                     INT           NULL,
        NumberOfAttempts              INT           NULL,
        StartTime                     DATETIME      NULL,
        CompletionTime                DATETIME      NULL
    );
END
GO
/*==============================================================
  3. HEALTH SNAPSHOT COLLECTOR
==============================================================*/
CREATE OR ALTER PROCEDURE monitor.usp_CaptureSqlHealthSnapshot
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @SnapshotTime               DATETIME2(0) = SYSDATETIME(),
        @SnapshotID                 BIGINT,
        @ActiveRequests             INT,
        @LongRunningRequests        INT,
        @BlockingSessions           INT,
        @RunnableTasks              INT,
        @SqlMaxMemoryMB             INT,
        @SqlTotalServerMemoryMB     BIGINT,
        @SqlTargetServerMemoryMB    BIGINT,
        @OsTotalMemoryMB            BIGINT,
        @OsAvailableMemoryMB        BIGINT,
        @OsMemoryStateDesc          NVARCHAR(256),
        @PLE                        BIGINT,
        @TempDbDataFileCount        INT,
        @TempDbDistinctSizeCount    INT,
        @TempDbDistinctGrowthCount  INT,
        @CpuCount                   INT,
        @MaxDop                     INT,
        @CostThreshold              INT;
    SELECT @ActiveRequests = COUNT(*)
    FROM sys.dm_exec_requests
    WHERE session_id > 50;
    SELECT @LongRunningRequests = COUNT(*)
    FROM sys.dm_exec_requests
    WHERE session_id > 50
      AND total_elapsed_time >= 300000;
    SELECT @BlockingSessions = COUNT(DISTINCT blocking_session_id)
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0;
    SELECT @RunnableTasks = SUM(runnable_tasks_count)
    FROM sys.dm_os_schedulers
    WHERE status = 'VISIBLE ONLINE';
    SELECT @SqlMaxMemoryMB = CAST(value_in_use AS INT)
    FROM sys.configurations
    WHERE name = 'max server memory (MB)';
    SELECT
        @SqlTotalServerMemoryMB  = MAX(CASE WHEN counter_name = 'Total Server Memory (KB)' THEN cntr_value END) / 1024,
        @SqlTargetServerMemoryMB = MAX(CASE WHEN counter_name = 'Target Server Memory (KB)' THEN cntr_value END) / 1024
    FROM sys.dm_os_performance_counters
    WHERE counter_name IN ('Total Server Memory (KB)','Target Server Memory (KB)');
    SELECT
        @OsTotalMemoryMB      = physical_memory_kb / 1024,
        @OsAvailableMemoryMB  = available_physical_memory_kb / 1024,
        @OsMemoryStateDesc    = system_memory_state_desc
    FROM sys.dm_os_sys_memory;
    SELECT
        @PLE = MAX(cntr_value)
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Page life expectancy'
      AND object_name LIKE '%Buffer Manager%';
    SELECT
        @TempDbDataFileCount = COUNT(*),
        @TempDbDistinctSizeCount = COUNT(DISTINCT size),
        @TempDbDistinctGrowthCount = COUNT(DISTINCT growth)
    FROM tempdb.sys.database_files
    WHERE type_desc = 'ROWS';
    SELECT @CpuCount = cpu_count
    FROM sys.dm_os_sys_info;
    SELECT @MaxDop = CAST(value_in_use AS INT)
    FROM sys.configurations
    WHERE name = 'max degree of parallelism';
    SELECT @CostThreshold = CAST(value_in_use AS INT)
    FROM sys.configurations
    WHERE name = 'cost threshold for parallelism';
    INSERT INTO monitor.InstanceHealthSnapshot
    (
        SnapshotTime,
        ActiveRequests,
        LongRunningRequests,
        BlockingSessions,
        RunnableTasks,
        SqlMaxMemoryMB,
        SqlTotalServerMemoryMB,
        SqlTargetServerMemoryMB,
        OsTotalMemoryMB,
        OsAvailableMemoryMB,
        OsMemoryStateDesc,
        PageLifeExpectancy,
        TempDbDataFileCount,
        TempDbDistinctSizeCount,
        TempDbDistinctGrowthCount,
        CpuCount,
        MaxDop,
        CostThreshold
    )
    VALUES
    (
        @SnapshotTime,
        @ActiveRequests,
        @LongRunningRequests,
        @BlockingSessions,
        @RunnableTasks,
        @SqlMaxMemoryMB,
        @SqlTotalServerMemoryMB,
        @SqlTargetServerMemoryMB,
        @OsTotalMemoryMB,
        @OsAvailableMemoryMB,
        @OsMemoryStateDesc,
        @PLE,
        @TempDbDataFileCount,
        @TempDbDistinctSizeCount,
        @TempDbDistinctGrowthCount,
        @CpuCount,
        @MaxDop,
        @CostThreshold
    );
    SET @SnapshotID = SCOPE_IDENTITY();
    INSERT INTO monitor.LongRunningRequestSnapshot
    (
        SnapshotID,
        SnapshotTime,
        session_id,
        status,
        blocking_session_id,
        database_name,
        login_name,
        host_name,
        program_name,
        wait_type,
        cpu_time_ms,
        total_elapsed_time_ms,
        logical_reads,
        reads,
        writes,
        sql_text
    )
    SELECT
        @SnapshotID,
        @SnapshotTime,
        r.session_id,
        r.status,
        r.blocking_session_id,
        DB_NAME(r.database_id),
        s.login_name,
        s.host_name,
        s.program_name,
        r.wait_type,
        r.cpu_time,
        r.total_elapsed_time,
        r.logical_reads,
        r.reads,
        r.writes,
        CAST(t.text AS NVARCHAR(MAX))
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s
        ON r.session_id = s.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE r.session_id > 50
      AND r.total_elapsed_time >= 300000;
    IF CAST(SERVERPROPERTY('IsHadrEnabled') AS INT) = 1
       AND EXISTS (SELECT 1 FROM sys.availability_groups)
    BEGIN
        BEGIN TRY
            INSERT INTO monitor.AGHealthSnapshot
            (
                SnapshotID,
                SnapshotTime,
                AGName,
                ReplicaServerName,
                RoleDesc,
                ConnectedStateDesc,
                ReplicaSyncHealthDesc,
                DatabaseName,
                DbSyncStateDesc,
                IsSuspended,
                LogSendQueueSizeKB,
                RedoQueueSizeKB,
                LogSendRateKBps,
                RedoRateKBps,
                LastCommitTime
            )
            SELECT
                @SnapshotID,
                @SnapshotTime,
                ag.name,
                ar.replica_server_name,
                ars.role_desc,
                ars.connected_state_desc,
                ars.synchronization_health_desc,
                drs.database_name,
                drs.synchronization_state_desc,
                drs.is_suspended,
                drs.log_send_queue_size,
                drs.redo_queue_size,
                drs.log_send_rate,
                drs.redo_rate,
                drs.last_commit_time
            FROM sys.dm_hadr_database_replica_states drs
            INNER JOIN sys.availability_replicas ar
                ON drs.replica_id = ar.replica_id
            INNER JOIN sys.availability_groups ag
                ON ar.group_id = ag.group_id
            INNER JOIN sys.dm_hadr_availability_replica_states ars
                ON ar.group_id = ars.group_id
               AND ar.replica_id = ars.replica_id;
        END TRY
        BEGIN CATCH
        END CATCH;
        BEGIN TRY
            INSERT INTO monitor.AGSeedingSnapshot
            (
                SnapshotID,
                SnapshotTime,
                AGName,
                ReplicaServerName,
                DatabaseName,
                CurrentState,
                FailureState,
                ErrorCode,
                NumberOfAttempts,
                StartTime,
                CompletionTime
            )
            SELECT
                @SnapshotID,
                @SnapshotTime,
                ag.name,
                ar.replica_server_name,
                seed.ag_db_name,
                seed.current_state_desc,
                seed.failure_state_desc,
                seed.error_code,
                seed.number_of_attempts,
                seed.start_time,
                seed.completion_time
            FROM sys.dm_hadr_automatic_seeding seed
            LEFT JOIN sys.availability_groups ag
                ON seed.ag_id = ag.group_id
            LEFT JOIN sys.availability_replicas ar
                ON seed.ag_id = ar.group_id
               AND seed.ag_remote_replica_id = ar.replica_id;
        END TRY
        BEGIN CATCH
        END CATCH;
    END
END
GO
/*==============================================================
  4. PERFORMANCE SNAPSHOT COLLECTOR
==============================================================*/
CREATE OR ALTER PROCEDURE monitor.usp_CapturePerformanceSnapshot
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @CpuPercent INT = NULL,
        @SqlCpuPercent INT = NULL,
        @DiskReadLatencyMs DECIMAL(18,2) = NULL,
        @DiskWriteLatencyMs DECIMAL(18,2) = NULL,
        @UserConnections INT = NULL;
    BEGIN TRY
        ;WITH rb AS
        (
            SELECT TOP (1)
                CAST(record AS XML) AS record_xml
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
              AND record LIKE '%<SystemHealth>%'
            ORDER BY [timestamp] DESC
        )
        SELECT
            @CpuPercent = 100 - record_xml.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),
            @SqlCpuPercent = record_xml.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
        FROM rb;
    END TRY
    BEGIN CATCH
    END CATCH;
    BEGIN TRY
        SELECT
            @DiskReadLatencyMs =
                AVG(CASE WHEN num_of_reads = 0 THEN NULL ELSE CONVERT(DECIMAL(18,2), io_stall_read_ms * 1.0 / NULLIF(num_of_reads,0)) END),
            @DiskWriteLatencyMs =
                AVG(CASE WHEN num_of_writes = 0 THEN NULL ELSE CONVERT(DECIMAL(18,2), io_stall_write_ms * 1.0 / NULLIF(num_of_writes,0)) END)
        FROM sys.dm_io_virtual_file_stats(NULL,NULL);
    END TRY
    BEGIN CATCH
    END CATCH;
    SELECT @UserConnections = COUNT(*)
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1;
    INSERT INTO monitor.PerformanceSnapshot
    (
        CpuPercent,
        SqlCpuPercent,
        DiskReadLatencyMs,
        DiskWriteLatencyMs,
        UserConnections
    )
    VALUES
    (
        @CpuPercent,
        @SqlCpuPercent,
        @DiskReadLatencyMs,
        @DiskWriteLatencyMs,
        @UserConnections
    );
END
GO
/*==============================================================
  5. RETENTION CLEANUP
==============================================================*/
CREATE OR ALTER PROCEDURE monitor.usp_MonitoringCleanup
    @RetentionDays INT = 30
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Cutoff DATETIME2(0) = DATEADD(DAY, -@RetentionDays, SYSDATETIME());
    DELETE FROM monitor.LongRunningRequestSnapshot
    WHERE SnapshotTime < @Cutoff;
    DELETE FROM monitor.AGHealthSnapshot
    WHERE SnapshotTime < @Cutoff;
    DELETE FROM monitor.AGSeedingSnapshot
    WHERE SnapshotTime < @Cutoff;
    DELETE FROM monitor.PerformanceSnapshot
    WHERE SnapshotTime < @Cutoff;
    DELETE FROM monitor.InstanceHealthSnapshot
    WHERE SnapshotTime < @Cutoff;
END
GO
/*==============================================================
  6. MORNING HTML EMAIL REPORT
==============================================================*/
CREATE OR ALTER PROCEDURE monitor.usp_SendMorningSqlHealthReport
    @Recipients NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @StartTime DATETIME2(0) = DATEADD(HOUR,-12,SYSDATETIME()),
        @EndTime   DATETIME2(0) = SYSDATETIME(),
        @ServerName SYSNAME = @@SERVERNAME,
        @Html NVARCHAR(MAX) = N'',
        @Subject NVARCHAR(300),
        @CurrentActiveRequests INT,
        @CurrentLongRunning    INT,
        @CurrentBlocking       INT,
        @CurrentRunnableTasks  INT,
        @CurrentSqlMaxMemoryMB INT,
        @CurrentSqlUsedMB      BIGINT,
        @CurrentSqlTargetMB    BIGINT,
        @CurrentOsAvailMB      BIGINT,
        @CurrentOsTotalMB      BIGINT,
        @CurrentPLE            BIGINT,
        @CurrentTempDbFiles    INT,
        @CurrentTempDbSizeVar  INT,
        @CurrentTempDbGrowthVar INT,
        @CurrentCpuCount       INT,
        @CurrentMaxDop         INT,
        @CurrentCostThreshold  INT,
        @CurrentCpuPercent     INT,
        @CurrentSqlCpuPercent  INT,
        @CurrentDiskReadLatencyMs DECIMAL(18,2),
        @CurrentDiskWriteLatencyMs DECIMAL(18,2),
        @CurrentUserConnections INT,
        @TempDbRecommendedFiles INT,
        @OsReserveRecommendedMB BIGINT,
        @SuggestedSqlMaxMemoryMB BIGINT,
        @StatusIcon NVARCHAR(10),
        @StatusText NVARCHAR(50),
        @StatusColor NVARCHAR(20),
        @RecommendationHtml NVARCHAR(MAX) = N'',
        @LongRunningHtml NVARCHAR(MAX) = N'',
        @BlockingHtml NVARCHAR(MAX) = N'',
        @AGHtml NVARCHAR(MAX) = N'',
        @AGSeedingHtml NVARCHAR(MAX) = N'',
        @FailedJobsHtml NVARCHAR(MAX) = N'',
        @BackupHtml NVARCHAR(MAX) = N'',
        @DbStateHtml NVARCHAR(MAX) = N'',
        @DiskHtml NVARCHAR(MAX) = N'',
        @LogSpaceHtml NVARCHAR(MAX) = N'',
        @WaitsHtml NVARCHAR(MAX) = N'',
        @GrowthHtml NVARCHAR(MAX) = N'',
        @DeadlockHtml NVARCHAR(MAX) = N'',
        @CheckDbHtml NVARCHAR(MAX) = N'',
        @DeadlockCount INT = 0,
        @DefaultTracePath NVARCHAR(4000);
    SELECT TOP (1)
        @CurrentActiveRequests  = ActiveRequests,
        @CurrentLongRunning     = LongRunningRequests,
        @CurrentBlocking        = BlockingSessions,
        @CurrentRunnableTasks   = RunnableTasks,
        @CurrentSqlMaxMemoryMB  = SqlMaxMemoryMB,
        @CurrentSqlUsedMB       = SqlTotalServerMemoryMB,
        @CurrentSqlTargetMB     = SqlTargetServerMemoryMB,
        @CurrentOsAvailMB       = OsAvailableMemoryMB,
        @CurrentOsTotalMB       = OsTotalMemoryMB,
        @CurrentPLE             = PageLifeExpectancy,
        @CurrentTempDbFiles     = TempDbDataFileCount,
        @CurrentTempDbSizeVar   = TempDbDistinctSizeCount,
        @CurrentTempDbGrowthVar = TempDbDistinctGrowthCount,
        @CurrentCpuCount        = CpuCount,
        @CurrentMaxDop          = MaxDop,
        @CurrentCostThreshold   = CostThreshold
    FROM monitor.InstanceHealthSnapshot
    ORDER BY SnapshotID DESC;
    SELECT TOP (1)
        @CurrentCpuPercent = CpuPercent,
        @CurrentSqlCpuPercent = SqlCpuPercent,
        @CurrentDiskReadLatencyMs = DiskReadLatencyMs,
        @CurrentDiskWriteLatencyMs = DiskWriteLatencyMs,
        @CurrentUserConnections = UserConnections
    FROM monitor.PerformanceSnapshot
    ORDER BY SnapshotID DESC;
    SET @TempDbRecommendedFiles =
        CASE
            WHEN @CurrentCpuCount IS NULL THEN NULL
            WHEN @CurrentCpuCount <= 8 THEN @CurrentCpuCount
            ELSE 8
        END;
    SET @OsReserveRecommendedMB =
        CASE
            WHEN @CurrentOsTotalMB IS NULL THEN NULL
            WHEN @CurrentOsTotalMB <= 16384 THEN 4096
            WHEN @CurrentOsTotalMB <= 65536 THEN 8192
            ELSE 12288
        END;
    SET @SuggestedSqlMaxMemoryMB =
        CASE
            WHEN @CurrentOsTotalMB IS NULL OR @OsReserveRecommendedMB IS NULL THEN NULL
            ELSE @CurrentOsTotalMB - @OsReserveRecommendedMB
        END;
    IF ISNULL(@CurrentBlocking,0) >= 5
       OR ISNULL(@CurrentLongRunning,0) >= 5
       OR ISNULL(@CurrentRunnableTasks,0) >= 10
       OR ISNULL(@CurrentOsAvailMB,999999) < 4096
       OR ISNULL(@CurrentCpuPercent,0) >= 90
       OR ISNULL(@CurrentDiskReadLatencyMs,0) >= 20
       OR ISNULL(@CurrentDiskWriteLatencyMs,0) >= 20
    BEGIN
        SET @StatusIcon = N'🔴';
        SET @StatusText = N'Critical';
        SET @StatusColor = N'#d9534f';
    END
    ELSE IF ISNULL(@CurrentBlocking,0) > 0
         OR ISNULL(@CurrentLongRunning,0) > 0
         OR ISNULL(@CurrentRunnableTasks,0) >= 5
         OR ISNULL(@CurrentPLE,999999) < 300
         OR ISNULL(@CurrentOsAvailMB,999999) < 8192
         OR ISNULL(@CurrentCpuPercent,0) >= 75
         OR ISNULL(@CurrentDiskReadLatencyMs,0) >= 10
         OR ISNULL(@CurrentDiskWriteLatencyMs,0) >= 10
    BEGIN
        SET @StatusIcon = N'🟡';
        SET @StatusText = N'Warning';
        SET @StatusColor = N'#f0ad4e';
    END
    ELSE
    BEGIN
        SET @StatusIcon = N'🟢';
        SET @StatusText = N'Healthy';
        SET @StatusColor = N'#5cb85c';
    END;
    IF @CurrentOsAvailMB IS NOT NULL AND @CurrentOsAvailMB < 8192
        SET @RecommendationHtml += N'<li><b>OS memory is low</b>. Available OS memory is ' + CAST(@CurrentOsAvailMB AS NVARCHAR(20)) + N' MB.</li>';
    IF @CurrentSqlMaxMemoryMB IS NOT NULL
       AND @SuggestedSqlMaxMemoryMB IS NOT NULL
       AND ABS(@CurrentSqlMaxMemoryMB - @SuggestedSqlMaxMemoryMB) >= 2048
        SET @RecommendationHtml += N'<li><b>Review max server memory</b>. Current = ' + CAST(@CurrentSqlMaxMemoryMB AS NVARCHAR(20)) + N' MB. Suggested starting point = ' + CAST(@SuggestedSqlMaxMemoryMB AS NVARCHAR(20)) + N' MB.</li>';
    IF @CurrentTempDbFiles IS NOT NULL
       AND @TempDbRecommendedFiles IS NOT NULL
       AND @CurrentTempDbFiles < @TempDbRecommendedFiles
        SET @RecommendationHtml += N'<li><b>TempDB file count may be low</b>. Current = ' + CAST(@CurrentTempDbFiles AS NVARCHAR(20)) + N'. Suggested starting point = ' + CAST(@TempDbRecommendedFiles AS NVARCHAR(20)) + N'.</li>';
    IF @CurrentTempDbSizeVar IS NOT NULL AND @CurrentTempDbSizeVar > 1
        SET @RecommendationHtml += N'<li><b>TempDB file sizes are not uniform</b>.</li>';
    IF @CurrentTempDbGrowthVar IS NOT NULL AND @CurrentTempDbGrowthVar > 1
        SET @RecommendationHtml += N'<li><b>TempDB growth settings are not uniform</b>.</li>';
    IF @CurrentCostThreshold IS NOT NULL AND @CurrentCostThreshold <= 5
        SET @RecommendationHtml += N'<li><b>Cost threshold for parallelism is low</b>. Current = ' + CAST(@CurrentCostThreshold AS NVARCHAR(20)) + N'. Review whether 25 to 50 fits your workload better.</li>';
    IF @CurrentMaxDop = 0
        SET @RecommendationHtml += N'<li><b>MAXDOP is set to 0</b>. Review this against Microsoft guidance and your NUMA or CPU layout.</li>';
    IF @CurrentCpuPercent IS NOT NULL AND @CurrentCpuPercent >= 75
        SET @RecommendationHtml += N'<li><b>CPU usage is elevated</b>. Current CPU = ' + CAST(@CurrentCpuPercent AS NVARCHAR(20)) + N'%.</li>';
    IF @CurrentDiskReadLatencyMs IS NOT NULL AND @CurrentDiskReadLatencyMs >= 10
        SET @RecommendationHtml += N'<li><b>Disk read latency is elevated</b>. Current read latency = ' + CAST(@CurrentDiskReadLatencyMs AS NVARCHAR(30)) + N' ms.</li>';
    IF @CurrentDiskWriteLatencyMs IS NOT NULL AND @CurrentDiskWriteLatencyMs >= 10
        SET @RecommendationHtml += N'<li><b>Disk write latency is elevated</b>. Current write latency = ' + CAST(@CurrentDiskWriteLatencyMs AS NVARCHAR(30)) + N' ms.</li>';
    /*==============================================================
      LONG RUNNING REQUESTS
    ==============================================================*/
    SELECT @LongRunningHtml =
    (
        SELECT TOP (10)
            N'<tr>' +
            N'<td>' + CAST(session_id AS NVARCHAR(10)) + N'</td>' +
            N'<td>' + ISNULL(database_name,N'') + N'</td>' +
            N'<td>' + ISNULL(status,N'') + N'</td>' +
            N'<td>' + CAST(ISNULL(blocking_session_id,0) AS NVARCHAR(10)) + N'</td>' +
            N'<td>' + CAST(total_elapsed_time_ms / 1000 AS NVARCHAR(20)) + N' sec</td>' +
            N'<td>' + CAST(ISNULL(cpu_time_ms,0) AS NVARCHAR(20)) + N'</td>' +
            N'<td>' + ISNULL(wait_type,N'') + N'</td>' +
            N'<td>' + LEFT(REPLACE(REPLACE(ISNULL(sql_text,N''),'<','&lt;'),'>','&gt;'),300) + N'</td>' +
            N'</tr>'
        FROM monitor.LongRunningRequestSnapshot
        WHERE SnapshotTime >= @StartTime
          AND SnapshotTime < @EndTime
        ORDER BY total_elapsed_time_ms DESC
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)');
    IF ISNULL(@LongRunningHtml,N'') = N''
        SET @LongRunningHtml = N'<tr><td colspan="8">No long running requests captured in the reporting window.</td></tr>';
    /*==============================================================
      CURRENT BLOCKING
    ==============================================================*/
    ;WITH blocking_cte AS
    (
        SELECT
            r.session_id,
            r.blocking_session_id,
            DB_NAME(r.database_id) AS database_name,
            r.wait_type,
            r.wait_time,
            s.login_name,
            s.host_name,
            s.program_name,
            ROW_NUMBER() OVER (ORDER BY r.wait_time DESC, r.session_id) AS rn
        FROM sys.dm_exec_requests r
        INNER JOIN sys.dm_exec_sessions s
            ON r.session_id = s.session_id
        WHERE r.blocking_session_id > 0
    )
    SELECT @BlockingHtml =
    (
        SELECT
            N'<tr>' +
            N'<td>' + CAST(session_id AS NVARCHAR(10)) + N'</td>' +
            N'<td>' + CAST(blocking_session_id AS NVARCHAR(10)) + N'</td>' +
            N'<td>' + ISNULL(database_name,N'') + N'</td>' +
            N'<td>' + ISNULL(wait_type,N'') + N'</td>' +
            N'<td>' + CAST(wait_time AS NVARCHAR(20)) + N'</td>' +
            N'<td>' + ISNULL(login_name,N'') + N'</td>' +
            N'<td>' + ISNULL(host_name,N'') + N'</td>' +
            N'<td>' + ISNULL(program_name,N'') + N'</td>' +
            N'</tr>'
        FROM blocking_cte
        WHERE rn <= 10
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)');
    IF ISNULL(@BlockingHtml,N'') = N''
        SET @BlockingHtml = N'<tr><td colspan="8">No current blocking chains detected.</td></tr>';
    /*==============================================================
      AG HEALTH
    ==============================================================*/
    IF CAST(SERVERPROPERTY('IsHadrEnabled') AS INT) = 1
       AND EXISTS (SELECT 1 FROM sys.availability_groups)
    BEGIN
        SELECT @AGHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + ISNULL(AGName,N'') + N'</td>' +
                N'<td>' + ISNULL(ReplicaServerName,N'') + N'</td>' +
                N'<td>' + ISNULL(DatabaseName,N'') + N'</td>' +
                N'<td>' + ISNULL(RoleDesc,N'') + N'</td>' +
                N'<td>' + ISNULL(ConnectedStateDesc,N'') + N'</td>' +
                N'<td>' + ISNULL(ReplicaSyncHealthDesc,N'') + N'</td>' +
                N'<td>' + ISNULL(DbSyncStateDesc,N'') + N'</td>' +
                N'<td>' + CAST(ISNULL(LogSendQueueSizeKB,0) / 1024 AS NVARCHAR(20)) + N' MB</td>' +
                N'<td>' + CAST(ISNULL(RedoQueueSizeKB,0) / 1024 AS NVARCHAR(20)) + N' MB</td>' +
                N'<td>' +
                    CASE
                        WHEN ConnectedStateDesc <> 'CONNECTED'
                          OR ReplicaSyncHealthDesc = 'NOT_HEALTHY'
                          OR DbSyncStateDesc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
                          OR ISNULL(IsSuspended,0) = 1
                          OR ISNULL(LogSendQueueSizeKB,0) > 512000
                          OR ISNULL(RedoQueueSizeKB,0) > 512000
                            THEN N'🔴'
                        WHEN ISNULL(LogSendQueueSizeKB,0) > 102400
                          OR ISNULL(RedoQueueSizeKB,0) > 102400
                          OR ReplicaSyncHealthDesc = 'PARTIALLY_HEALTHY'
                            THEN N'🟡'
                        ELSE N'🟢'
                    END +
                N'</td>' +
                N'</tr>'
            FROM
            (
                SELECT *
                FROM monitor.AGHealthSnapshot
                WHERE SnapshotID = (SELECT MAX(SnapshotID) FROM monitor.AGHealthSnapshot)
            ) x
            ORDER BY AGName, ReplicaServerName, DatabaseName
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
        IF ISNULL(@AGHtml,N'') = N''
            SET @AGHtml = N'<tr><td colspan="10">No AG rows found.</td></tr>';
        SELECT @AGSeedingHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + ISNULL(AGName,N'') + N'</td>' +
                N'<td>' + ISNULL(ReplicaServerName,N'') + N'</td>' +
                N'<td>' + ISNULL(DatabaseName,N'') + N'</td>' +
                N'<td>' + ISNULL(CurrentState,N'') + N'</td>' +
                N'<td>' + ISNULL(FailureState,N'') + N'</td>' +
                N'<td>' + CAST(ISNULL(ErrorCode,0) AS NVARCHAR(20)) + N'</td>' +
                N'<td>' + CAST(ISNULL(NumberOfAttempts,0) AS NVARCHAR(20)) + N'</td>' +
                N'<td>' +
                    CASE
                        WHEN CurrentState = 'FAILED' OR ISNULL(ErrorCode,0) <> 0 THEN N'🔴'
                        WHEN CurrentState IN ('IN_PROGRESS','PENDING') THEN N'🟡'
                        ELSE N'🟢'
                    END +
                N'</td>' +
                N'</tr>'
            FROM
            (
                SELECT *
                FROM monitor.AGSeedingSnapshot
                WHERE SnapshotID = (SELECT MAX(SnapshotID) FROM monitor.AGSeedingSnapshot)
            ) s
            ORDER BY AGName, ReplicaServerName, DatabaseName
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
        IF ISNULL(@AGSeedingHtml,N'') = N''
            SET @AGSeedingHtml = N'<tr><td colspan="8">No AG seeding activity detected.</td></tr>';
    END
    ELSE
    BEGIN
        SET @AGHtml = N'<tr><td colspan="10">Always On Availability Groups not enabled on this instance.</td></tr>';
        SET @AGSeedingHtml = N'<tr><td colspan="8">Always On Availability Groups not enabled on this instance.</td></tr>';
    END;
    /*==============================================================
      FAILED SQL AGENT JOBS
    ==============================================================*/
    BEGIN TRY
        ;WITH FailedJobs AS
        (
            SELECT
                j.name AS JobName,
                msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime,
                h.message,
                ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY msdb.dbo.agent_datetime(h.run_date, h.run_time) DESC) AS rn
            FROM msdb.dbo.sysjobhistory h
            INNER JOIN msdb.dbo.sysjobs j
                ON h.job_id = j.job_id
            WHERE h.step_id = 0
              AND h.run_status = 0
              AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @StartTime
        )
        SELECT @FailedJobsHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + JobName + N'</td>' +
                N'<td>' + CONVERT(NVARCHAR(19), RunDateTime, 120) + N'</td>' +
                N'<td>' + LEFT(REPLACE(REPLACE(ISNULL(message,N''),'<','&lt;'),'>','&gt;'),300) + N'</td>' +
                N'</tr>'
            FROM FailedJobs
            WHERE rn = 1
            ORDER BY RunDateTime DESC
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
    END TRY
    BEGIN CATCH
        SET @FailedJobsHtml = N'<tr><td colspan="3">Could not read SQL Agent job history.</td></tr>';
    END CATCH;
    IF ISNULL(@FailedJobsHtml,N'') = N''
        SET @FailedJobsHtml = N'<tr><td colspan="3">No failed SQL Agent jobs in the reporting window.</td></tr>';
    /*==============================================================
      BACKUP STATUS
    ==============================================================*/
    BEGIN TRY
        ;WITH LastBackup AS
        (
            SELECT
                d.name,
                MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup
            FROM sys.databases d
            LEFT JOIN msdb.dbo.backupset b
                ON d.name = b.database_name
            WHERE d.database_id > 4
            GROUP BY d.name
        )
        SELECT @BackupHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + name + N'</td>' +
                N'<td>' + ISNULL(CONVERT(NVARCHAR(19), LastFullBackup, 120), N'Never') + N'</td>' +
                N'<td>' +
                    CASE
                        WHEN LastFullBackup IS NULL THEN N'🔴'
                        WHEN LastFullBackup < DATEADD(HOUR,-24,@EndTime) THEN N'🟡'
                        ELSE N'🟢'
                    END +
                N'</td>' +
                N'</tr>'
            FROM LastBackup
            ORDER BY name
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
    END TRY
    BEGIN CATCH
        SET @BackupHtml = N'<tr><td colspan="3">Could not read backup history.</td></tr>';
    END CATCH;
    /*==============================================================
      DATABASE STATE
    ==============================================================*/
    SELECT @DbStateHtml =
    (
        SELECT
            N'<tr>' +
            N'<td>' + name + N'</td>' +
            N'<td>' + state_desc + N'</td>' +
            N'</tr>'
        FROM sys.databases
        WHERE state_desc <> 'ONLINE'
        ORDER BY name
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)');
    IF ISNULL(@DbStateHtml,N'') = N''
        SET @DbStateHtml = N'<tr><td colspan="2">All databases are ONLINE.</td></tr>';
    /*==============================================================
      DISK SPACE
    ==============================================================*/
    CREATE TABLE #drives
    (
        drive CHAR(1),
        MBFree INT
    );
    BEGIN TRY
        INSERT INTO #drives
        EXEC master..xp_fixeddrives;
        SELECT @DiskHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + drive + N':</td>' +
                N'<td>' + CAST(MBFree AS NVARCHAR(20)) + N' MB</td>' +
                N'<td>' +
                    CASE
                        WHEN MBFree < 10240 THEN N'🔴'
                        WHEN MBFree < 20480 THEN N'🟡'
                        ELSE N'🟢'
                    END +
                N'</td>' +
                N'</tr>'
            FROM #drives
            ORDER BY drive
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
    END TRY
    BEGIN CATCH
        SET @DiskHtml = N'<tr><td colspan="3">Could not read disk free space. Check xp_fixeddrives permissions.</td></tr>';
    END CATCH;
    DROP TABLE #drives;
    /*==============================================================
      LOG SPACE
    ==============================================================*/
    CREATE TABLE #LogSpace
    (
        DatabaseName SYSNAME,
        LogSizeMB DECIMAL(18,2),
        LogSpaceUsedPct DECIMAL(18,2),
        Status INT
    );
    BEGIN TRY
        INSERT INTO #LogSpace
        EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;');
        SELECT @LogSpaceHtml =
        (
            SELECT
                N'<tr>' +
                N'<td>' + DatabaseName + N'</td>' +
                N'<td>' + CAST(LogSizeMB AS NVARCHAR(30)) + N'</td>' +
                N'<td>' + CAST(LogSpaceUsedPct AS NVARCHAR(30)) + N'%</td>' +
                N'<td>' +
                    CASE
                        WHEN LogSpaceUsedPct >= 90 THEN N'🔴'
                        WHEN LogSpaceUsedPct >= 80 THEN N'🟡'
                        ELSE N'🟢'
                    END +
                N'</td>' +
                N'</tr>'
            FROM #LogSpace
            ORDER BY LogSpaceUsedPct DESC, DatabaseName
            FOR XML PATH(''), TYPE
        ).value('.','nvarchar(max)');
    END TRY
    BEGIN CATCH
        SET @LogSpaceHtml = N'<tr><td colspan="4">Could not read log space usage.</td></tr>';
    END CATCH;
    DROP TABLE #LogSpace;
    /*==============================================================
      TOP WAITS
    ==============================================================*/
    ;WITH waits AS
    (
        SELECT TOP (10)
            wait_type,
            wait_time_ms / 1000.0 AS wait_seconds,
            waiting_tasks_count
        FROM sys.dm_os_wait_stats
        WHERE wait_type NOT IN
        (
            'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP',
            'BROKER_TO_FLUSH','BROKER_TRANSMITTER','CHECKPOINT_QUEUE',
            'CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','CLR_SEMAPHORE',
            'DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE',
            'DBMIRRORING_CMD','DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE',
            'EXECSYNC','FSAGENT','FT_IFTS_SCHEDULER_IDLE_WAIT','FT_IFTSHC_MUTEX',
            'HADR_CLUSAPI_CALL','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'HADR_LOGCAPTURE_WAIT','HADR_NOTIFICATION_DEQUEUE','HADR_TIMER_TASK',
            'HADR_WORK_QUEUE','KSOURCE_WAKEUP','LAZYWRITER_SLEEP',
            'LOGMGR_QUEUE','MEMORY_ALLOCATION_EXT','ONDEMAND_TASK_QUEUE',
            'PARALLEL_REDO_DRAIN_WORKER','PARALLEL_REDO_LOG_CACHE',
            'PARALLEL_REDO_TRAN_LIST','PARALLEL_REDO_WORKER_SYNC',
            'PARALLEL_REDO_WORKER_WAIT_WORK','PREEMPTIVE_XE_GETTARGETSTATE',
            'PWAIT_ALL_COMPONENTS_INITIALIZED','PWAIT_DIRECTLOGCONSUMER_GETNEXT',
            'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE',
            'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            'QDS_SHUTDOWN_QUEUE','REDO_THREAD_PENDING_WORK',
            'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
            'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP',
            'SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED',
            'SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK',
            'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
            'SQLTRACE_BUFFER_FLUSH','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_WAIT_ENTRIES','WAIT_FOR_RESULTS','WAITFOR',
            'WAITFOR_TASKSHUTDOWN','WAIT_XTP_HOST_WAIT','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            'WAIT_XTP_CKPT_CLOSE','XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT',
            'XE_TIMER_EVENT'
        )
        ORDER BY wait_time_ms DESC
    )
    SELECT @WaitsHtml =
    (
        SELECT
            N'<tr>' +
            N'<td>' + wait_type + N'</td>' +
            N'<td>' + CAST(wait_seconds AS NVARCHAR(30)) + N'</td>' +
            N'<td>' + CAST(waiting_tasks_count AS NVARCHAR(30)) + N'</td>' +
            N'</tr>'
        FROM waits
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)');
    IF ISNULL(@WaitsHtml,N'') = N''
        SET @WaitsHtml = N'<tr><td colspan="3">No significant waits returned.</td></tr>';
    /*==============================================================
      GROWTH EVENTS
    ==============================================================*/
    SELECT @DefaultTracePath = CONVERT(NVARCHAR(4000), path)
    FROM sys.traces
    WHERE is_default = 1;
    IF @DefaultTracePath IS NOT NULL
    BEGIN
        CREATE TABLE #Growth
        (
            DatabaseName SYSNAME,
            EventName NVARCHAR(100),
            StartTime DATETIME,
            FileName NVARCHAR(260)
        );
        BEGIN TRY
            INSERT INTO #Growth (DatabaseName, EventName, StartTime, FileName)
            SELECT
                DB_NAME(t.DatabaseID),
                CASE t.EventClass
                    WHEN 92 THEN 'Data File Auto Grow'
                    WHEN 93 THEN 'Log File Auto Grow'
                    ELSE 'Other'
                END,
                t.StartTime,
                t.Filename
            FROM sys.fn_trace_gettable(@DefaultTracePath, DEFAULT) t
            WHERE t.EventClass IN (92,93)
              AND t.StartTime >= @StartTime;
            SELECT @GrowthHtml =
            (
                SELECT
                    N'<tr>' +
                    N'<td>' + ISNULL(DatabaseName,N'') + N'</td>' +
                    N'<td>' + EventName + N'</td>' +
                    N'<td>' + CONVERT(NVARCHAR(19), StartTime, 120) + N'</td>' +
                    N'<td>' + LEFT(ISNULL(FileName,N''),200) + N'</td>' +
                    N'</tr>'
                FROM #Growth
                ORDER BY StartTime DESC
                FOR XML PATH(''), TYPE
            ).value('.','nvarchar(max)');
        END TRY
        BEGIN CATCH
            SET @GrowthHtml = N'<tr><td colspan="4">Could not read default trace growth events.</td></tr>';
        END CATCH;
        DROP TABLE #Growth;
        IF ISNULL(@GrowthHtml,N'') = N''
            SET @GrowthHtml = N'<tr><td colspan="4">No auto growth events found in the reporting window.</td></tr>';
    END
    ELSE
    BEGIN
        SET @GrowthHtml = N'<tr><td colspan="4">Default trace is not enabled.</td></tr>';
    END;
    /*==============================================================
      DEADLOCKS
    ==============================================================*/
    BEGIN TRY
        ;WITH deadlocks AS
        (
            SELECT
                CAST(xed.event_data.value('(event/@timestamp)[1]', 'datetime2') AS DATETIME2(0)) AS DeadlockTime
            FROM
            (
                SELECT CAST(xst.target_data AS XML) AS target_data
                FROM sys.dm_xe_session_targets xst
                INNER JOIN sys.dm_xe_sessions xs
                    ON xs.address = xst.event_session_address
                WHERE xs.name = 'system_health'
                  AND xst.target_name = 'ring_buffer'
            ) src
            CROSS APPLY src.target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xed(event_data)
        )
        SELECT @DeadlockCount = COUNT(*)
        FROM deadlocks
        WHERE DeadlockTime >= @StartTime;
        SET @DeadlockHtml =
            N'<tr><td>Deadlocks in reporting window</td><td>' + CAST(ISNULL(@DeadlockCount,0) AS NVARCHAR(20)) + N'</td><td>' +
            CASE
                WHEN ISNULL(@DeadlockCount,0) > 5 THEN N'🔴'
                WHEN ISNULL(@DeadlockCount,0) > 0 THEN N'🟡'
                ELSE N'🟢'
            END +
            N'</td></tr>';
        IF ISNULL(@DeadlockCount,0) > 0
            SET @RecommendationHtml += N'<li><b>Deadlocks detected</b>. Count in reporting window = ' + CAST(@DeadlockCount AS NVARCHAR(20)) + N'.</li>';
    END TRY
    BEGIN CATCH
        SET @DeadlockHtml = N'<tr><td>Deadlocks in reporting window</td><td>Unable to read</td><td>🟡</td></tr>';
    END CATCH;
    /*==============================================================
      CHECKDB
    ==============================================================*/
    ;WITH checkdb AS
    (
        SELECT
            name AS DatabaseName,
            CAST(DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS DATETIME) AS LastGoodCheckDbTime
        FROM sys.databases
        WHERE database_id > 4
    )
    SELECT @CheckDbHtml =
    (
        SELECT
            N'<tr>' +
            N'<td>' + DatabaseName + N'</td>' +
            N'<td>' + ISNULL(CONVERT(NVARCHAR(19), LastGoodCheckDbTime, 120), N'Never') + N'</td>' +
            N'<td>' +
                CASE
                    WHEN LastGoodCheckDbTime IS NULL THEN N'🔴'
                    WHEN LastGoodCheckDbTime < DATEADD(DAY,-7,@EndTime) THEN N'🟡'
                    ELSE N'🟢'
                END +
            N'</td>' +
            N'</tr>'
        FROM checkdb
        ORDER BY DatabaseName
        FOR XML PATH(''), TYPE
    ).value('.','nvarchar(max)');
    IF EXISTS
    (
        SELECT 1
        FROM sys.databases
        WHERE database_id > 4
          AND CAST(DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS DATETIME) IS NULL
    )
        SET @RecommendationHtml += N'<li><b>Some databases do not show a successful CHECKDB time</b>. Review integrity check jobs.</li>';
    IF @RecommendationHtml = N''
        SET @RecommendationHtml = N'<li>No major recommendations from the current checks.</li>';
    SET @Html = N'
<html>
<head>
<style>
body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }
h1 { font-size: 20px; margin-bottom: 4px; }
h2 { font-size: 16px; margin: 18px 0 8px 0; }
.small { color: #666; font-size: 12px; }
.card { border: 1px solid #ddd; border-radius: 8px; padding: 12px; margin-bottom: 14px; }
.kpi { display:inline-block; width:190px; margin:6px; padding:10px; border-radius:8px; background:#f8f8f8; border:1px solid #e4e4e4; vertical-align:top; }
.kpi-title { font-size:12px; color:#666; }
.kpi-value { font-size:22px; font-weight:bold; margin-top:4px; }
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 7px; text-align: left; vertical-align: top; }
th { background: #f3f3f3; }
ul { margin-top: 6px; }
</style>
</head>
<body>
<h1>SQL Server Morning Health Report</h1>
<div class="small">Server: ' + @ServerName + N'<br/>Window: ' + CONVERT(NVARCHAR(19),@StartTime,120) + N' to ' + CONVERT(NVARCHAR(19),@EndTime,120) + N'</div>
<div class="card" style="border-left: 6px solid ' + @StatusColor + N';">
    <div style="font-size:22px;font-weight:bold;">' + @StatusIcon + N' ' + @StatusText + N'</div>
    <div class="small">Based on blocking, long running requests, CPU, latency, memory availability, AG health, and configuration checks.</div>
</div>
<div>
    <div class="kpi">
        <div class="kpi-title">Active Requests</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentActiveRequests,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">Long Running</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentLongRunning,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">Blocking Sessions</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentBlocking,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">CPU %</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentCpuPercent,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">SQL CPU %</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentSqlCpuPercent,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">User Connections</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentUserConnections,0) AS NVARCHAR(20)) + N'</div>
    </div>
    <div class="kpi">
        <div class="kpi-title">PLE</div>
        <div class="kpi-value">' + CAST(ISNULL(@CurrentPLE,0) AS NVARCHAR(20)) + N'</div>
    </div>
</div>
<h2>Memory</h2>
<div class="card">
<table>
<tr><th>Metric</th><th>Value</th><th>Status</th></tr>
<tr><td>SQL Max Memory</td><td>' + CAST(ISNULL(@CurrentSqlMaxMemoryMB,0) AS NVARCHAR(20)) + N' MB</td><td></td></tr>
<tr><td>SQL Memory In Use</td><td>' + CAST(ISNULL(@CurrentSqlUsedMB,0) AS NVARCHAR(20)) + N' MB</td><td></td></tr>
<tr><td>SQL Target Memory</td><td>' + CAST(ISNULL(@CurrentSqlTargetMB,0) AS NVARCHAR(20)) + N' MB</td><td></td></tr>
<tr><td>OS Total Memory</td><td>' + CAST(ISNULL(@CurrentOsTotalMB,0) AS NVARCHAR(20)) + N' MB</td><td></td></tr>
<tr><td>OS Available Memory</td><td>' + CAST(ISNULL(@CurrentOsAvailMB,0) AS NVARCHAR(20)) + N' MB</td><td>' +
CASE WHEN ISNULL(@CurrentOsAvailMB,999999) < 4096 THEN N'🔴'
     WHEN ISNULL(@CurrentOsAvailMB,999999) < 8192 THEN N'🟡'
     ELSE N'🟢' END + N'</td></tr>
<tr><td>Suggested Max Memory</td><td>' + CAST(ISNULL(@SuggestedSqlMaxMemoryMB,0) AS NVARCHAR(20)) + N' MB</td><td></td></tr>
</table>
</div>
<h2>Performance</h2>
<div class="card">
<table>
<tr><th>Metric</th><th>Value</th><th>Status</th></tr>
<tr><td>CPU %</td><td>' + CAST(ISNULL(@CurrentCpuPercent,0) AS NVARCHAR(20)) + N'%</td><td>' +
CASE WHEN ISNULL(@CurrentCpuPercent,0) >= 90 THEN N'🔴'
     WHEN ISNULL(@CurrentCpuPercent,0) >= 75 THEN N'🟡'
     ELSE N'🟢' END + N'</td></tr>
<tr><td>SQL CPU %</td><td>' + CAST(ISNULL(@CurrentSqlCpuPercent,0) AS NVARCHAR(20)) + N'%</td><td></td></tr>
<tr><td>Disk Read Latency</td><td>' + CAST(ISNULL(@CurrentDiskReadLatencyMs,0) AS NVARCHAR(30)) + N' ms</td><td>' +
CASE WHEN ISNULL(@CurrentDiskReadLatencyMs,0) >= 20 THEN N'🔴'
     WHEN ISNULL(@CurrentDiskReadLatencyMs,0) >= 10 THEN N'🟡'
     ELSE N'🟢' END + N'</td></tr>
<tr><td>Disk Write Latency</td><td>' + CAST(ISNULL(@CurrentDiskWriteLatencyMs,0) AS NVARCHAR(30)) + N' ms</td><td>' +
CASE WHEN ISNULL(@CurrentDiskWriteLatencyMs,0) >= 20 THEN N'🔴'
     WHEN ISNULL(@CurrentDiskWriteLatencyMs,0) >= 10 THEN N'🟡'
     ELSE N'🟢' END + N'</td></tr>
</table>
</div>
<h2>TempDB and Parallelism</h2>
<div class="card">
<table>
<tr><th>Check</th><th>Value</th><th>Status</th></tr>
<tr><td>TempDB Data Files</td><td>' + CAST(ISNULL(@CurrentTempDbFiles,0) AS NVARCHAR(20)) + N' (recommended starting point: ' + CAST(ISNULL(@TempDbRecommendedFiles,0) AS NVARCHAR(20)) + N')</td><td>' +
CASE WHEN @CurrentTempDbFiles < ISNULL(@TempDbRecommendedFiles,@CurrentTempDbFiles) THEN N'🟡' ELSE N'🟢' END + N'</td></tr>
<tr><td>TempDB File Sizes Uniform</td><td>' + CASE WHEN ISNULL(@CurrentTempDbSizeVar,1) > 1 THEN N'No' ELSE N'Yes' END + N'</td><td>' +
CASE WHEN ISNULL(@CurrentTempDbSizeVar,1) > 1 THEN N'🔴' ELSE N'🟢' END + N'</td></tr>
<tr><td>TempDB Growth Uniform</td><td>' + CASE WHEN ISNULL(@CurrentTempDbGrowthVar,1) > 1 THEN N'No' ELSE N'Yes' END + N'</td><td>' +
CASE WHEN ISNULL(@CurrentTempDbGrowthVar,1) > 1 THEN N'🔴' ELSE N'🟢' END + N'</td></tr>
<tr><td>MAXDOP</td><td>' + CAST(ISNULL(@CurrentMaxDop,0) AS NVARCHAR(20)) + N'</td><td>' +
CASE WHEN @CurrentMaxDop = 0 THEN N'🟡' ELSE N'🟢' END + N'</td></tr>
<tr><td>Cost Threshold for Parallelism</td><td>' + CAST(ISNULL(@CurrentCostThreshold,0) AS NVARCHAR(20)) + N'</td><td>' +
CASE WHEN ISNULL(@CurrentCostThreshold,0) <= 5 THEN N'🟡' ELSE N'🟢' END + N'</td></tr>
</table>
</div>
<h2>Always On AG Health</h2>
<div class="card">
<table>
<tr><th>AG</th><th>Replica</th><th>Database</th><th>Role</th><th>Connected</th><th>Replica Health</th><th>DB Sync State</th><th>Log Send Queue</th><th>Redo Queue</th><th>Status</th></tr>
' + @AGHtml + N'
</table>
</div>
<h2>AG Seeding</h2>
<div class="card">
<table>
<tr><th>AG</th><th>Replica</th><th>Database</th><th>Current State</th><th>Failure State</th><th>Error Code</th><th>Attempts</th><th>Status</th></tr>
' + @AGSeedingHtml + N'
</table>
</div>
<h2>Recommendations</h2>
<div class="card">
<ul>' + @RecommendationHtml + N'</ul>
</div>
<h2>Current Blocking</h2>
<div class="card">
<table>
<tr><th>Session</th><th>Blocked By</th><th>DB</th><th>Wait Type</th><th>Wait ms</th><th>Login</th><th>Host</th><th>Program</th></tr>
' + @BlockingHtml + N'
</table>
</div>
<h2>Long Running Requests</h2>
<div class="card">
<table>
<tr><th>Session</th><th>DB</th><th>Status</th><th>Blocked By</th><th>Elapsed</th><th>CPU ms</th><th>Wait</th><th>SQL Text</th></tr>
' + @LongRunningHtml + N'
</table>
</div>
<h2>Failed SQL Agent Jobs</h2>
<div class="card">
<table>
<tr><th>Job</th><th>Run Time</th><th>Message</th></tr>
' + @FailedJobsHtml + N'
</table>
</div>
<h2>Backup Status</h2>
<div class="card">
<table>
<tr><th>Database</th><th>Last Full Backup</th><th>Status</th></tr>
' + @BackupHtml + N'
</table>
</div>
<h2>Database State</h2>
<div class="card">
<table>
<tr><th>Database</th><th>State</th></tr>
' + @DbStateHtml + N'
</table>
</div>
<h2>Disk Free Space</h2>
<div class="card">
<table>
<tr><th>Drive</th><th>Free MB</th><th>Status</th></tr>
' + @DiskHtml + N'
</table>
</div>
<h2>Transaction Log Space</h2>
<div class="card">
<table>
<tr><th>Database</th><th>Log Size MB</th><th>Used %</th><th>Status</th></tr>
' + @LogSpaceHtml + N'
</table>
</div>
<h2>Deadlocks</h2>
<div class="card">
<table>
<tr><th>Metric</th><th>Value</th><th>Status</th></tr>
' + @DeadlockHtml + N'
</table>
</div>
<h2>Top Waits Since Restart</h2>
<div class="card">
<table>
<tr><th>Wait Type</th><th>Wait Seconds</th><th>Tasks</th></tr>
' + @WaitsHtml + N'
</table>
</div>
<h2>Auto Growth Events</h2>
<div class="card">
<table>
<tr><th>Database</th><th>Event</th><th>Time</th><th>File</th></tr>
' + @GrowthHtml + N'
</table>
</div>
<h2>Last Good CHECKDB Time</h2>
<div class="card">
<table>
<tr><th>Database</th><th>Last Good CHECKDB</th><th>Status</th></tr>
' + @CheckDbHtml + N'
</table>
</div>
</body>
</html>';
    SET @Subject = N'SQL Morning Health Report - ' + @ServerName + N' - ' + CONVERT(NVARCHAR(10), GETDATE(), 120);
    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'YourDatabaseMailProfile',
         @recipients   = @Recipients,
         @subject      = @Subject,
         @body         = @Html,
         @body_format  = 'HTML';
END
GO

		

	











	
	

	

	
	
Discover more from SQLYARD

	

	
	
Subscribe to get the latest posts sent to your email.

	

	
	

			

		

							

					

												

							
													

												

							
							
							
							
							
							
							
														
						

					

				

								

	

	
	

	



		
		
			




	




	        
 
 Related Posts 
 
						
											
					
 
		

	
	
	
	
	
		

			
Leave a Reply
			

				
									
					
					
							

		


		
		

		
	



			

			
		
	



	
 
	

		

			

			

			

					

						
					

					

								

			
		

				

			
Contact us
Contact us
		

							

					

											

				

		

		


		
	





					

						

								
	


		
		
Discover more from SQLYARD

		

		
		
Subscribe now to keep reading and get access to the full archive.

		

			

		

							

					

												

							
													

												

							
							
							
							
							
							
							
														
						

					

				

								

	

	

		
		
Continue reading