Introduction

In SQL Server, performance issues are very common. Sometimes queries run slow, sometimes users get stuck, and sometimes the system becomes unresponsive.

Two major reasons:

Blocking (queries waiting on each other)
Poor performance (CPU, IO, memory issues)

In this blog, we will learn how to solve both problems using two powerful stored procedures:

👉 dbusp_BlockingTree → For blocking analysis
👉 dbusp_PerformanceStats → Full performance dashboard

Part 1: Understanding Blocking

Blocking happens when:

One query locks data
Another query tries to access same data
Second query waits

Result: system slowdown

Solution 1: Blocking Tree Script

We use:

👉 dbusp_BlockingTree

Full script:

IF OBJECT_ID('dbusp_BlockingTree', 'P') IS NOT NULL
    DROP PROCEDURE dbusp_BlockingTree;
GO

/*
================================================================================
SP Name  : dbusp_BlockingTree
Purpose  : Shows full blocking chain tree using modern DMVs.
           NOTE: This is a BLOCKING TREE viewer, not deadlock capture.
           For actual deadlocks → use Extended Events (system_health session).
Author   : DBA Team
Version  : 2.0 (DBA Grade)

Parameters:
  @MinWaitSec      - Show only sessions waiting >= N seconds (default 0)
  @DBName          - Filter by database name (NULL = all databases)
  @ShowAllSessions - 0 = only blocking chain | 1 = all active sessions
  @KillLeadBlocker - 1 = KILL the head blocker SPID
  @DBAConfirm      - Safety switch: must be 1 to allow KILL

Result Sets:
  #1 → Blocking Tree (visual chain with depth indentation)
  #2 → Lock Resource Details (which object is being locked)
  #3 → Lead Blocker Summary (quick action card for DBA)
================================================================================
*/

CREATE PROCEDURE dbusp_BlockingTree
    @MinWaitSec       INT     = 0,      -- Min wait threshold in seconds
    @DBName           SYSNAME = NULL,   -- NULL = all DBs
    @ShowAllSessions  BIT     = 0,      -- 1 = include non-blocked active sessions too
    @KillLeadBlocker  BIT     = 0,      -- 1 = attempt KILL on lead blocker
    @DBAConfirm       BIT     = 0       -- Safety: must be 1 to allow @KillLeadBlocker
AS
BEGIN
    SET NOCOUNT ON;

    -- =========================================================================
    -- SECTION 0: Safety Guard for KILL
    -- =========================================================================
    IF @KillLeadBlocker = 1 AND @DBAConfirm = 0
    BEGIN
        RAISERROR(
            'KILL blocked! Set @DBAConfirm = 1 along with @KillLeadBlocker = 1 to confirm.',
            16, 1
        );
        RETURN;
    END;

    -- =========================================================================
    -- SECTION 1: Collect session + request data from modern DMVs
    -- sys.dm_exec_sessions  → login, host, DB, isolation level, open txns
    -- sys.dm_exec_requests  → wait info, CPU, reads, blocking_session_id
    -- sys.dm_exec_sql_text  → batch SQL text (safe NULL handle check)
    -- =========================================================================
    IF OBJECT_ID('tempdb..#tmpSessions') IS NOT NULL
    DROP TABLE #tmpSessions;

    SELECT
        s.session_id                                AS SPID,
        ISNULL(r.blocking_session_id, 0)            AS BlockedBy,

        -- Wait info
        ISNULL(r.wait_time, 0) / 1000.0             AS WaitTimeSec,
        ISNULL(r.wait_type, N'—')                   AS WaitType,
        ISNULL(r.wait_resource, N'—')               AS WaitResource,

        -- Session info
        s.login_name                                AS LoginName,
        s.host_name                                 AS HostName,
        s.program_name                              AS AppName,
        ISNULL(DB_NAME(r.database_id),
               DB_NAME(s.database_id))              AS DBName,

        -- Perf counters
        ISNULL(r.cpu_time, 0)                       AS CPUTimeMs,
        ISNULL(r.logical_reads, 0)                  AS LogicalReads,
        ISNULL(r.writes, 0)                         AS Writes,
        ISNULL(r.row_count, 0)                      AS RowsReturned,

        -- Transaction info
        s.open_transaction_count                    AS OpenTxnCount,
        CASE s.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'READ UNCOMMITTED'
            WHEN 2 THEN 'READ COMMITTED'
            WHEN 3 THEN 'REPEATABLE READ'
            WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT'
            ELSE 'Unknown'
        END                                         AS IsolationLevel,

        -- Status
        s.status                                    AS SessionStatus,
        ISNULL(r.status, N'—')                      AS RequestStatus,
        r.command                                   AS CommandType,

        -- SQL Text: full batch
        CASE
            WHEN r.sql_handle IS NOT NULL
              AND r.sql_handle <> 0x0000000000000000000000000000000000000000
            THEN REPLACE(REPLACE(
                    ISNULL(bt.TEXT, N'[Could not retrieve SQL]'),
                    CHAR(10), ' '), CHAR(13), ' ')
            ELSE N'[No active request / idle in txn]'
        END                                         AS BatchText,

        -- Current statement (slice from batch using offsets)
        CASE
            WHEN r.sql_handle IS NOT NULL
              AND r.sql_handle <> 0x0000000000000000000000000000000000000000
              AND bt.TEXT IS NOT NULL
            THEN REPLACE(REPLACE(
                    SUBSTRING(
                        bt.TEXT,
                        (r.statement_start_offset / 2) + 1,
                        CASE r.statement_end_offset
                            WHEN -1 THEN DATALENGTH(bt.TEXT)
                            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
                        END
                    ), CHAR(10), ' '), CHAR(13), ' ')
            ELSE N'—'
        END                                         AS CurrentStatement,

        -- Start time of current request
        r.start_time                                AS RequestStartTime,

        -- Used in CTE tree building
        CAST(RIGHT('0000' + CAST(s.session_id AS VARCHAR(10)), 4)
             AS VARCHAR(4000))                      AS SortPath

    INTO #tmpSessions
    FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_requests r
           ON s.session_id = r.session_id
    OUTER APPLY (
        SELECT TEXT
        FROM sys.dm_exec_sql_text(r.sql_handle)
        WHERE r.sql_handle IS NOT NULL
          AND r.sql_handle <> 0x0000000000000000000000000000000000000000
    ) bt
    WHERE s.is_user_process = 1                     -- Exclude system SPIDs
      AND s.session_id <> @@SPID                    -- Exclude this SP itself
      AND (
            -- Only blocking chain members
            (@ShowAllSessions = 0 AND (
                ISNULL(r.blocking_session_id, 0) > 0
                OR EXISTS (
                    SELECT 1 FROM sys.dm_exec_requests r2
                    WHERE r2.blocking_session_id = s.session_id
                )
            ))
            OR
            -- All active sessions
            (@ShowAllSessions = 1 AND (
                s.status IN ('running', 'sleeping', 'suspended')
                OR r.session_id IS NOT NULL
            ))
          )
      AND ISNULL(r.wait_time, 0) / 1000.0 >= @MinWaitSec
      AND (@DBName IS NULL
           OR DB_NAME(r.database_id) = @DBName
           OR DB_NAME(s.database_id) = @DBName);

    -- =========================================================================
    -- SECTION 2: Recursive CTE — Build Blocking Tree
    -- =========================================================================
    WITH BlockerTree AS
    (
        -- Anchor: Root blockers (sessions that are blocking others but not blocked themselves)
        SELECT
            SPID,
            BlockedBy,
            WaitTimeSec,
            WaitType,
            WaitResource,
            LoginName,
            HostName,
            AppName,
            DBName,
            CPUTimeMs,
            LogicalReads,
            OpenTxnCount,
            IsolationLevel,
            SessionStatus,
            RequestStatus,
            CommandType,
            BatchText,
            CurrentStatement,
            RequestStartTime,
            CAST(RIGHT('0000' + CAST(SPID AS VARCHAR(10)), 4)
                 AS VARCHAR(4000))                  AS LEVEL
        FROM #tmpSessions
        WHERE (BlockedBy = 0 OR BlockedBy = SPID)  -- Self-block edge case guard
          AND EXISTS (
                SELECT 1 FROM #tmpSessions c
                WHERE c.BlockedBy = SPID
                  AND c.BlockedBy <> c.SPID
              )

        UNION ALL

        -- Recursive: blocked children
        SELECT
            c.SPID,
            c.BlockedBy,
            c.WaitTimeSec,
            c.WaitType,
            c.WaitResource,
            c.LoginName,
            c.HostName,
            c.AppName,
            c.DBName,
            c.CPUTimeMs,
            c.LogicalReads,
            c.OpenTxnCount,
            c.IsolationLevel,
            c.SessionStatus,
            c.RequestStatus,
            c.CommandType,
            c.BatchText,
            c.CurrentStatement,
            c.RequestStartTime,
            CAST(p.LEVEL + RIGHT('0000' + CAST(c.SPID AS VARCHAR(10)), 4)
                 AS VARCHAR(4000))
        FROM #tmpSessions c
        INNER JOIN BlockerTree p ON c.BlockedBy = p.SPID
        WHERE c.BlockedBy > 0
          AND c.BlockedBy <> c.SPID
    )
    -- =========================================================================
    -- RESULT SET 1: Visual Blocking Tree
    -- =========================================================================
    SELECT
        -- Visual tree node
        REPLICATE('   ', (LEN(LEVEL) / 4) - 1)
        + CASE
            WHEN (LEN(LEVEL) / 4) - 1 = 0
            THEN N'👑 HEAD BLOCKER ──▶ '
            ELSE N'    └── '
          END
        + CAST(SPID AS NVARCHAR(10))
        + N'  (Blocked by: '
        + CASE WHEN BlockedBy = 0 THEN 'None' ELSE CAST(BlockedBy AS NVARCHAR(10)) END
        + N')'                                      AS BlockingTree,

        SPID,
        BlockedBy,
        WaitTimeSec,
        WaitType,
        WaitResource,
        LoginName,
        HostName,
        AppName,
        DBName,
        SessionStatus,
        RequestStatus,
        CommandType,
        IsolationLevel,
        OpenTxnCount,
        CPUTimeMs,
        LogicalReads,
        RequestStartTime,
        CurrentStatement,
        BatchText
    FROM BlockerTree
    ORDER BY LEVEL;

    -- =========================================================================
    -- RESULT SET 2: Lock Resource Details
    -- Which object/index is being contested
    -- =========================================================================
    SELECT
        tl.request_session_id                       AS SPID,
        tl.resource_type                            AS LockResourceType,    -- TABLE / PAGE / KEY / ROW
        tl.resource_subtype                         AS LockSubType,
        tl.resource_database_id                     AS LockDBID,
        DB_NAME(tl.resource_database_id)            AS LockDBName,
        ISNULL(OBJECT_NAME(
            tl.resource_associated_entity_id,
            tl.resource_database_id), '—')          AS LockedObject,
        tl.resource_description                     AS LockDetail,
        tl.request_mode                             AS LockMode,            -- S, X, IX, SIX etc.
        tl.request_status                           AS LockStatus,          -- GRANT / WAIT / CONVERT
        tl.request_type                             AS LockType
    FROM sys.dm_tran_locks tl
    INNER JOIN #tmpSessions ts ON tl.request_session_id = ts.SPID
    ORDER BY tl.request_session_id, tl.resource_type;

    -- =========================================================================
    -- RESULT SET 3: Lead Blocker Summary (DBA Action Card)
    -- =========================================================================
    SELECT TOP 10
        ts.SPID                                     AS LeadBlockerSPID,
        ts.LoginName,
        ts.HostName,
        ts.AppName,
        ts.DBName,
        ts.IsolationLevel,
        ts.OpenTxnCount,
        ts.CPUTimeMs,
        ts.LogicalReads,
        ts.RequestStartTime,
        DATEDIFF(SECOND, ts.RequestStartTime, GETDATE())
                                                    AS HoldingTxnSec,
        COUNT(victims.SPID)                         AS BlockedSessionCount,
        N'KILL ' + CAST(ts.SPID AS NVARCHAR(10)) + N';'
                                                    AS KillCommand,
        ts.CurrentStatement                         AS BlockerCurrentStatement
    FROM #tmpSessions ts
    LEFT JOIN #tmpSessions victims ON victims.BlockedBy = ts.SPID
    WHERE (ts.BlockedBy = 0 OR ts.BlockedBy = ts.SPID)
      AND EXISTS (
            SELECT 1 FROM #tmpSessions c2
            WHERE c2.BlockedBy = ts.SPID AND c2.SPID <> ts.SPID
          )
    GROUP BY
        ts.SPID, ts.LoginName, ts.HostName, ts.AppName,
        ts.DBName, ts.IsolationLevel, ts.OpenTxnCount,
        ts.CPUTimeMs, ts.LogicalReads, ts.RequestStartTime,
        ts.CurrentStatement
    ORDER BY BlockedSessionCount DESC, HoldingTxnSec DESC;

    -- =========================================================================
    -- SECTION 3: KILL Lead Blocker (with double safety check)
    -- =========================================================================
    IF @KillLeadBlocker = 1 AND @DBAConfirm = 1
    BEGIN
        DECLARE @LeadSPID   INT;
        DECLARE @KillSQL    NVARCHAR(50);
        DECLARE @KillMsg    NVARCHAR(200);

        -- Pick the worst offender: most victims, longest running
        SELECT TOP 1
            @LeadSPID = ts.SPID
        FROM #tmpSessions ts
        WHERE (ts.BlockedBy = 0 OR ts.BlockedBy = ts.SPID)
          AND EXISTS (
                SELECT 1 FROM #tmpSessions c2
                WHERE c2.BlockedBy = ts.SPID AND c2.SPID <> ts.SPID
              )
        ORDER BY
            (SELECT COUNT(*) FROM #tmpSessions v WHERE v.BlockedBy = ts.SPID) DESC,
            DATEDIFF(SECOND, ts.RequestStartTime, GETDATE()) DESC;

        IF @LeadSPID IS NOT NULL
        BEGIN
            BEGIN TRY
                SET @KillSQL = N'KILL ' + CAST(@LeadSPID AS NVARCHAR(10)) + N';';
                EXEC sp_executesql @KillSQL;

                SET @KillMsg = N'✅ KILLED SPID ' + CAST(@LeadSPID AS NVARCHAR(10))
                             + N' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120);
                RAISERROR(@KillMsg, 10, 1) WITH NOWAIT;
            END TRY
            BEGIN CATCH
                SET @KillMsg = N'❌ KILL failed for SPID '
                             + CAST(@LeadSPID AS NVARCHAR(10))
                             + N' → ' + ERROR_MESSAGE();
                RAISERROR(@KillMsg, 16, 1);
            END CATCH;
        END
        ELSE
        BEGIN
            RAISERROR('No lead blocker found to KILL.', 10, 1) WITH NOWAIT;
        END;
    END;

    -- Cleanup
    IF OBJECT_ID('tempdb..#tmpSessions') IS NOT NULL
    DROP TABLE #tmpSessions;

END;
GO

-- 1. Default: Show blocking chain only
EXEC dbusp_BlockingTree;

-- 2. Only sessions waiting 5+ seconds
EXEC dbusp_BlockingTree @MinWaitSec = 5;

-- 3. Specific database only
EXEC dbusp_BlockingTree @DBName = N'Vipul';

-- 4. All active sessions (not just blocked chain)
EXEC dbusp_BlockingTree @ShowAllSessions = 1;

-- 5. Kill lead blocker (double confirm required)
EXEC dbusp_BlockingTree
    @KillLeadBlocker = 1,
    @DBAConfirm      = 1;

-- 6. Full DBA investigation mode
EXEC dbusp_BlockingTree
    @MinWaitSec      = 3,
    @DBName          = N'Vipul',
    @ShowAllSessions = 0;

What This Script Does

It gives 3 outputs:

Blocking Tree
- Shows full chain
- Who is blocking whom
Lock Details
- Table / row / index lock info
Lead Blocker
- Main problem query
- Ready KILL command

How to Use

EXEC dbusp_BlockingTree;

Filter options:

EXEC dbusp_BlockingTree @MinWaitSec = 5;
EXEC dbusp_BlockingTree @DBName = 'PayrollDB';

Kill blocker (careful ⚠️):

EXEC dbusp_BlockingTree
    @KillLeadBlocker = 1,
    @DBAConfirm = 1;

When to Use This Script

Users stuck
Queries not finishing
LCK_M_* wait types
High wait time

Part 2: Full SQL Server Performance Monitoring

Blocking is only one part. A real DBA needs to monitor the complete system health.

This is used here:

`dbusp_PerformanceStats`

Full script:

-- ============================================================
-- dbusp_PerformanceStats  |  ENTERPRISE DBA-Grade Dashboard v3.1
-- Categories:
--   QUERY PERFORMANCE: 2, 3, 4, 5, 6, 7, 15, 26
--   INDEX HEALTH:      8, 9, 10, 16, 25
--   DATABASE HEALTH:   1, 11, 12, 13, 14, 17, 18, 19, 20, 27, 28
--   MEMORY/RESOURCES:  13, 21, 22, 23, 24
--   INFRASTRUCTURE:    11, 29, 30
--   CONCURRENCY:       5, 6, 14, 15, 24
-- SECTIONS:
--   1  Large Tables                            (Size Analysis)
--   2  Top CPU Heavy Queries                   (Query Perf)
--   3  Top Duration Queries                    (Query Perf)
--   4  Top IO Heavy Queries                    (Query Perf)
--   5  Currently Running + Blocking Queries    (Concurrency)
--   6  Execution Plans for Slow Queries        (Query Tuning)
--   7  Stored Procedure Performance            (SP Perf)
--   8  Missing Index Suggestions               (Index Health)
--   9  Index Fragmentation Report              (Index Health)
--  10  Unused Indexes                          (Index Cleanup)
--  11  Top Wait Statistics                     (Bottleneck Analysis)
--  12  TempDB Usage Per Session                (Resource Usage)
--  13  Memory Pressure Indicators              (Memory Health)
--  14  Deadlock History                        (Concurrency Issues)
--  15  Lock Escalation & Contention Pattern    (Locking Analysis)
--  16  Stale Statistics Report                 (DB Integrity)
--  17  Database Health & Configuration         (DB Status)
--  18  Transaction Log Status & VLF Check      (Log Health)
--  19  Page Compression Opportunities          (Storage Opt)
--  20  Plan Cache Regression / QS Fallback      (Query Analytics)
--  21  Parameter Sniffing Detection            (Query Issues)
--  22  Database IO Subsystem Analysis          (IO Performance)
--  23  Connection Pool Analysis                (Concurrency)
--  24  CPU & Scheduler Queue Analysis          (CPU Health)
--  25  Table Partitioning Opportunities        (Storage Opt)
--  26  Query Recompilation & Hints Analysis    (Query Stability)
--  27  Database Recovery Model & Backups       (Data Safety)
--  28  Object Permissions & Security Audit     (Security)
--  29  Server Configuration Issues             (Config Health)
--  30  Extended Events & Trace Configuration   (Monitoring)
-- ============================================================

IF OBJECT_ID('dbo.dbusp_PerformanceStats', 'P') IS NOT NULL
    DROP PROCEDURE dbo.dbusp_PerformanceStats;
GO

CREATE PROCEDURE dbo.dbusp_PerformanceStats
    @ThresholdAvgSec         FLOAT   = 2,        -- Avg elapsed time filter (seconds)
    @ThresholdCPUSec         FLOAT   = 1,        -- Avg CPU time filter (seconds)
    @ThresholdReads          BIGINT  = 10000,    -- Total logical+physical reads filter
    @TableSizeInMB           INT     = 1024,     -- Table size threshold (MB) -> 1 GB default
    @RowCount                BIGINT  = 10000000, -- Row count threshold -> 10 Million
    @TopRows                 INT     = 10,       -- Top N rows for query lists
    @DbName                  SYSNAME = N'',      -- Target DB; blank = current DB
    @FragmentationThreshold  FLOAT   = 30.0,     -- Index fragmentation % threshold
    @MinIndexSizePages       INT     = 500,      -- Skip small indexes in frag check
    @StaleStatsThresholdDays INT     = 7,        -- Statistics older than N days = stale
    @LockEscalationThreshold INT     = 5000,     -- Locks before escalation happens
    @QueryStoreThreshold     INT     = 10,       -- Query Store top N items
    @ShowSection             TINYINT = 0,        -- 0 = All sections; 1-30 = specific section
    @OnlyProblems            BIT     = 0,        -- 1 = hide INFO / healthy rows
    @IncludeRunbook          BIT     = 1         -- 1 = include DBA guidance text

AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY

        -- ── Resolve DB name ──────────────────────────────────────────
        IF @DbName = N'' OR @DbName IS NULL
            SET @DbName = DB_NAME();

        DECLARE @TargetDbId INT = DB_ID(@DbName);
        DECLARE @SqlMajorVersion INT;
        DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
        DECLARE @CpuCount INT;
        DECLARE @ServerStartTime DATETIME;

        SELECT
            @SqlMajorVersion = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), SERVERPROPERTY('ProductVersion')), 4)),
            @CpuCount = cpu_count
        FROM sys.dm_os_sys_info;

        SELECT @ServerStartTime = create_date
        FROM sys.databases
        WHERE database_id = 2;

        -- ── Capture this SP own sql_handle to exclude from results ──
        DECLARE @ThisSqlHandle VARBINARY(64);
        SELECT @ThisSqlHandle = sql_handle
        FROM   sys.dm_exec_requests
        WHERE  session_id = @@SPID;

        -- ── Section Directory (always shown) ─────────────────────────
        SELECT ID, [Type], Title
        FROM (VALUES
            (1,  'Table Size',  'Large Tables (>' + CAST(@TableSizeInMB AS VARCHAR) + ' MB or >' + CAST(@RowCount AS VARCHAR) + ' rows)'),
            (2,  'Query Perf',  'Top CPU Heavy Queries'),
            (3,  'Query Perf',  'Top Duration Queries (Longest Avg)'),
            (4,  'Query Perf',  'Top IO Heavy Queries (Reads)'),
            (5,  'Concurrency', 'Currently Running + Blocking Queries'),
            (6,  'Query Tuning','Execution Plan XML for Top Slow Queries'),
            (7,  'SP Perf',     'Stored Procedure Performance Analysis'),
            (8,  'Index',       'Missing Index Suggestions'),
            (9,  'Index',       'Index Fragmentation Report'),
            (10, 'Index',       'Unused Indexes'),
            (11, 'Wait Stats',  'Top Wait Statistics (Server-Wide)'),
            (12, 'TempDB',      'TempDB Usage Per Session'),
            (13, 'Memory',      'Memory Pressure Indicators'),
            (14, 'Deadlock',    'Deadlock History'),
            (15, 'Concurrency', 'Lock Escalation & Contention Pattern'),
            (16, 'Integrity',   'Stale Statistics Report (>' + CAST(@StaleStatsThresholdDays AS VARCHAR) + ' days)'),
            (17, 'DB Health',   'Database Health & Configuration Status'),
            (18, 'DB Health',   'Transaction Log Status & VLF Health'),
            (19, 'Storage',     'Page Compression Opportunities'),
            (20, 'Query Store', 'Plan Cache Regression / Query Store Fallback'),
            (21, 'Query Issues','Parameter Sniffing Detection'),
            (22, 'IO Perf',     'Database IO Subsystem Analysis'),
            (23, 'Concurrency', 'Connection Pool Analysis'),
            (24, 'CPU Health',  'CPU & Scheduler Queue Analysis'),
            (25, 'Storage',     'Table Partitioning Opportunities'),
            (26, 'Query Stab',  'Query Recompilation & Query Hints Analysis'),
            (27, 'Data Safety', 'Database Recovery Model & Backup Status'),
            (28, 'Security',    'Object Permissions & Security Audit'),
            (29, 'Config',      'Server Configuration Issues & Best Practices'),
            (30, 'Monitoring',  'Extended Events & Trace Configuration')
        ) AS T(ID, [Type], Title);


        -- ============================================================
        -- SECTION 1 : Large Tables
        -- ============================================================
        IF @ShowSection IN (0, 1)
        BEGIN
            SELECT
                'Large Tables'                                                          AS [Section],
                t.NAME                                                                  AS [Table],
                SUM(CASE WHEN i.index_id < 2 THEN p.rows ELSE 0 END)                  AS [Row Count],
                CAST(SUM(a.total_pages) * 8.0 / 1024       AS DECIMAL(10,1))           AS [Total Size MB],
                CAST(SUM(a.total_pages) * 8.0 / 1024 / 1024 AS DECIMAL(10,3))          AS [Total Size GB],
                CAST(SUM(a.used_pages)  * 8.0 / 1024       AS DECIMAL(10,1))           AS [Used Size MB]
            FROM       sys.tables           t
            INNER JOIN sys.indexes          i ON t.object_id    = i.object_id
            INNER JOIN sys.partitions       p ON i.object_id    = p.object_id AND i.index_id = p.index_id
            INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
            WHERE i.index_id < 2
            GROUP BY t.NAME
            HAVING
                (SUM(a.total_pages) * 8.0 / 1024) >= @TableSizeInMB
                OR SUM(CASE WHEN i.index_id < 2 THEN p.rows ELSE 0 END) >= @RowCount
            ORDER BY [Total Size MB] DESC;
        END


        -- ============================================================
        -- SECTION 2 : Top CPU Heavy Queries
        -- ============================================================
        IF @ShowSection IN (0, 2)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP (@TopRows)
                    'Top CPU Queries'                                                               AS [Section],
                    CASE
                        WHEN (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdCPUSec * 5) THEN 'CRITICAL'
                        WHEN (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdCPUSec * 2) THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                                             AS Severity,
                    'High CPU Query'                                                                AS IssueType,
                    QS.execution_count,
                    QS.creation_time                                                                AS FirstExecuted,
                    QS.last_execution_time                                                          AS LastExecuted,
                    CAST(QS.total_worker_time / 1000000.0 AS DECIMAL(18,2))                        AS TotalCPU_Sec,
                    CAST((QS.total_worker_time  / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgCPU_Sec,
                    CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))                       AS TotalElapsed_Sec,
                    CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
                    QS.total_logical_reads + QS.total_physical_reads                                AS TotalReads,
                    QS.total_logical_writes                                                         AS TotalWrites,
                    DB_NAME(T.dbid)                                                                 AS DBName,
                    CASE
                        WHEN QS.total_logical_reads + QS.total_physical_reads > @ThresholdReads THEN 'Expensive scans, missing indexes, bad joins, or parameter sniffing can drive CPU.'
                        ELSE 'High CPU concentrated inside execution plan operators or repeated recompiles.'
                    END                                                                             AS LikelyCause,
                    CASE
                        WHEN QS.total_logical_reads + QS.total_physical_reads > @ThresholdReads THEN 'Review actual plan, reduce reads, and validate selective indexes before touching MAXDOP.'
                        ELSE 'Review execution plan for CPU-heavy operators and validate parameter stability.'
                    END                                                                             AS RecommendedAction,
                    'SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Review actual plan for this statement' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Check plan cache entry, compare reads vs CPU, capture actual execution plan, and test with representative parameters in lower environment.'
                        ELSE NULL
                    END                                                                             AS DBARunbook,
                    T.text                                                                          AS FullQueryText,
                    ISNULL(NULLIF(LTRIM(RTRIM(
                        SUBSTRING(T.text,
                            (QS.statement_start_offset / 2) + 1,
                            CASE
                                WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
                                ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
                            END
                        )
                    )), ''), T.text)                                                               AS ExactStatement
                FROM sys.dm_exec_query_stats QS
                CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
                WHERE (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdCPUSec
                  AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
                  AND DB_NAME(T.dbid) = @DbName
                  AND T.text IS NOT NULL
            ) AS CPUQ
            WHERE @OnlyProblems = 0 OR CPUQ.Severity <> 'INFO'
            ORDER BY CPUQ.TotalCPU_Sec DESC;
        END


        -- ============================================================
        -- SECTION 3 : Top Duration Queries
        -- ============================================================
        IF @ShowSection IN (0, 3)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP (@TopRows)
                    'Top Duration Queries'                                                          AS [Section],
                    CASE
                        WHEN (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 5) THEN 'CRITICAL'
                        WHEN (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 2) THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                                             AS Severity,
                    'Long Running Query'                                                            AS IssueType,
                    QS.execution_count,
                    QS.creation_time                                                                AS FirstExecuted,
                    QS.last_execution_time                                                          AS LastExecuted,
                    CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))                       AS TotalElapsed_Sec,
                    CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
                    CAST(QS.total_worker_time  / 1000000.0 AS DECIMAL(18,2))                       AS TotalCPU_Sec,
                    QS.total_logical_reads + QS.total_physical_reads                                AS TotalReads,
                    DB_NAME(T.dbid)                                                                 AS DBName,
                    CASE
                        WHEN CAST(QS.total_worker_time AS FLOAT) / NULLIF(QS.total_elapsed_time,0) < 0.30 THEN 'Elapsed time is much higher than CPU, so waits, blocking, IO, or client fetch delay are likely.'
                        ELSE 'Query is expensive even when running, usually due to poor plan shape, large row counts, or repeated scans.'
                    END                                                                             AS LikelyCause,
                    CASE
                        WHEN CAST(QS.total_worker_time AS FLOAT) / NULLIF(QS.total_elapsed_time,0) < 0.30 THEN 'Correlate with wait stats and blocking section before changing indexes.'
                        ELSE 'Review joins, predicates, and statistics quality for this statement.'
                    END                                                                             AS RecommendedAction,
                    'SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Capture actual plan and compare waits during execution' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'If duration is high but CPU is low, first check waits, blockers, TempDB spills, and storage latency. If both are high, tune plan shape and row reduction.'
                        ELSE NULL
                    END                                                                             AS DBARunbook,
                    T.text                                                                          AS FullQueryText,
                    ISNULL(NULLIF(LTRIM(RTRIM(
                        SUBSTRING(T.text,
                            (QS.statement_start_offset / 2) + 1,
                            CASE
                                WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
                                ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
                            END
                        )
                    )), ''), T.text)                                                               AS ExactStatement
                FROM sys.dm_exec_query_stats QS
                CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
                WHERE (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
                  AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
                  AND DB_NAME(T.dbid) = @DbName
                  AND T.text IS NOT NULL
            ) AS DurQ
            WHERE @OnlyProblems = 0 OR DurQ.Severity <> 'INFO'
            ORDER BY DurQ.AvgElapsed_Sec DESC;
        END


        -- ============================================================
        -- SECTION 4 : Top IO Heavy Queries
        -- ============================================================
        IF @ShowSection IN (0, 4)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP (@TopRows)
                    'Top IO Queries'                                                                AS [Section],
                    CASE
                        WHEN (QS.total_logical_reads + QS.total_physical_reads) >= (@ThresholdReads * 10) THEN 'CRITICAL'
                        WHEN (QS.total_logical_reads + QS.total_physical_reads) >= (@ThresholdReads * 3) THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                                             AS Severity,
                    'High Read Query'                                                               AS IssueType,
                    QS.execution_count,
                    QS.creation_time                                                                AS FirstExecuted,
                    QS.last_execution_time                                                          AS LastExecuted,
                    QS.total_logical_reads + QS.total_physical_reads                                AS TotalReads,
                    (QS.total_logical_reads + QS.total_physical_reads)
                        / NULLIF(QS.execution_count,0)                                              AS AvgReads,
                    QS.total_logical_writes                                                         AS TotalWrites,
                    CAST(QS.total_worker_time  / 1000000.0 AS DECIMAL(18,2))                       AS TotalCPU_Sec,
                    CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))                       AS TotalElapsed_Sec,
                    DB_NAME(T.dbid)                                                                 AS DBName,
                    'Large scans, non-selective predicates, stale statistics, or missing covering indexes are likely.' AS LikelyCause,
                    'Check missing/duplicate indexes, verify up-to-date statistics, and reduce returned rows.'          AS RecommendedAction,
                    'SET STATISTICS IO ON; -- Validate seekability and compare actual vs estimated row counts'          AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Start with estimated and actual execution plans, then validate index usage and stale stats before creating new indexes.'
                        ELSE NULL
                    END                                                                             AS DBARunbook,
                    T.text                                                                          AS FullQueryText,
                    ISNULL(NULLIF(LTRIM(RTRIM(
                        SUBSTRING(T.text,
                            (QS.statement_start_offset / 2) + 1,
                            CASE
                                WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
                                ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
                            END
                        )
                    )), ''), T.text)                                                               AS ExactStatement
                FROM sys.dm_exec_query_stats QS
                CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
                WHERE (QS.total_logical_reads + QS.total_physical_reads) > @ThresholdReads
                  AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
                  AND DB_NAME(T.dbid) = @DbName
                  AND T.text IS NOT NULL
            ) AS IOQ
            WHERE @OnlyProblems = 0 OR IOQ.Severity <> 'INFO'
            ORDER BY IOQ.TotalReads DESC;
        END


        -- ============================================================
        -- SECTION 5 : Currently Running + Blocking Queries
        -- OUTER APPLY used for blocker — safe when blocking_session_id = NULL/0
        -- ============================================================
        IF @ShowSection IN (0, 5)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'Active + Blocking'                                         AS [Section],
                    CASE
                        WHEN R.blocking_session_id > 0 THEN 'CRITICAL'
                        WHEN R.wait_type LIKE 'LCK%' OR R.open_transaction_count > 0 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    CASE
                        WHEN R.blocking_session_id > 0 THEN 'Blocking'
                        WHEN R.wait_type LIKE 'LCK%' THEN 'Lock Wait'
                        ELSE 'Active Request'
                    END                                                         AS IssueType,
                    R.session_id,
                    R.status,
                    R.command,
                    DB_NAME(R.database_id)                                      AS DBName,
                    S.login_name                                                AS LoginName,
                    S.host_name                                                 AS HostName,
                    S.program_name                                              AS ProgramName,
                    T.text                                                      AS QueryText,
                    R.start_time,
                    CAST(R.total_elapsed_time / 1000.0 AS DECIMAL(18,1))        AS ElapsedTimeSec,
                    CAST(R.cpu_time           / 1000.0 AS DECIMAL(18,1))        AS CPUTimeSec,
                    R.granted_query_memory                                      AS GrantedMemoryKB,
                    R.reads                                                     AS CurrentReads,
                    R.writes                                                    AS CurrentWrites,
                    R.logical_reads                                             AS CurrentLogicalReads,
                    R.wait_type,
                    R.wait_time,
                    R.wait_resource,
                    R.blocking_session_id,
                    BT.text                                                     AS BlockingQueryText,
                    R.open_transaction_count                                    AS OpenTransactions,
                    CASE
                        WHEN R.blocking_session_id > 0 THEN 'Blocking chain detected. Open transaction, long-running DML, or uncommitted work is likely holding locks.'
                        WHEN R.wait_type LIKE 'LCK%' THEN 'Session is waiting on locks even if direct blocker text is not available.'
                        ELSE 'Request is active; review only if elapsed time or resource usage is high.'
                    END                                                         AS LikelyCause,
                    CASE
                        WHEN R.blocking_session_id > 0 THEN 'Review blocker session first, validate open transaction, and kill only after business confirmation.'
                        WHEN R.wait_type LIKE 'LCK%' THEN 'Trace blocker, commit/rollback offending transaction, and reduce transaction scope.'
                        ELSE 'Monitor request and capture plan if it remains active beyond normal runtime.'
                    END                                                         AS RecommendedAction,
                    CASE
                        WHEN R.blocking_session_id > 0 THEN 'DBCC INPUTBUFFER(' + CAST(R.blocking_session_id AS VARCHAR(20)) + ');'
                        ELSE 'DBCC INPUTBUFFER(' + CAST(R.session_id AS VARCHAR(20)) + ');'
                    END                                                         AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 AND R.blocking_session_id > 0 THEN 'Inspect blocker session, host, login, open transaction count, and business impact. Do not kill blocker before confirming rollback cost.'
                        WHEN @IncludeRunbook = 1 THEN 'Capture session text, wait type, and elapsed time. If request persists, correlate with waits, IO, and TempDB sections.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM       sys.dm_exec_requests  R
                CROSS APPLY sys.dm_exec_sql_text(R.sql_handle)  T
                LEFT  JOIN sys.dm_exec_sessions  S  ON R.session_id         = S.session_id
                LEFT  JOIN sys.dm_exec_requests  BR ON R.blocking_session_id = BR.session_id
                OUTER APPLY sys.dm_exec_sql_text(BR.sql_handle) BT
                WHERE R.session_id > 50
                  AND R.session_id <> @@SPID
                  AND DB_NAME(R.database_id) = @DbName
                  AND T.text IS NOT NULL
            ) AS BlockQ
            WHERE @OnlyProblems = 0 OR BlockQ.Severity <> 'INFO'
            ORDER BY BlockQ.ElapsedTimeSec DESC;
        END


        -- ============================================================
        -- SECTION 6 : Execution Plans for Slow Queries
        -- ============================================================
        IF @ShowSection IN (0, 6)
        BEGIN
            SELECT TOP (@TopRows)
                'Execution Plans'                                                               AS [Section],
                QS.execution_count,
                CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))                       AS TotalElapsed_Sec,
                CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
                DB_NAME(T.dbid)                                                                 AS DBName,
                -- Full batch text
                T.text                                                                          AS FullQueryText,
                -- Exact slow statement
                ISNULL(NULLIF(LTRIM(RTRIM(
                    SUBSTRING(T.text,
                        (QS.statement_start_offset / 2) + 1,
                        CASE 
                            WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
                            ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
                        END
                    )
                )), ''), T.text)                                                               AS ExactStatement,
                P.query_plan                                                                    AS ExecutionPlanXml
            FROM sys.dm_exec_query_stats    QS
            CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)  T
            CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) P
            WHERE (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
              AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
              AND DB_NAME(T.dbid) = @DbName
            ORDER BY AvgElapsed_Sec DESC;
        END


        -- ============================================================
        -- SECTION 7 : Stored Procedure Performance
        -- ============================================================
        IF @ShowSection IN (0, 7)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'SP Performance'                                                                AS [Section],
                    CASE
                        WHEN (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 5) THEN 'CRITICAL'
                        WHEN (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 2) THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                                             AS Severity,
                    'Slow Stored Procedure'                                                         AS IssueType,
                    DB_NAME(PS.database_id)                                                         AS DBName,
                    OBJECT_NAME(PS.object_id, PS.database_id)                                       AS ProcedureName,
                    PS.cached_time,
                    PS.last_execution_time,
                    PS.execution_count,
                    CAST(PS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))                       AS TotalElapsedSec,
                    CAST((PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
                    CAST(PS.total_worker_time  / 1000000.0 AS DECIMAL(18,2))                       AS TotalCPUSec,
                    CAST((PS.total_worker_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgCPUSec,
                    PS.total_logical_reads                                                          AS TotalLogicalReads,
                    PS.total_logical_reads  / NULLIF(PS.execution_count,0)                         AS AvgLogicalReads,
                    PS.total_logical_writes                                                         AS TotalLogicalWrites,
                    PS.total_physical_reads                                                         AS TotalPhysicalReads,
                    PS.total_physical_reads / NULLIF(PS.execution_count,0)                         AS AvgPhysicalReads,
                    CASE
                        WHEN PS.total_physical_reads > 0 THEN 'Procedure is hitting storage and likely needs indexing, stats review, or row reduction.'
                        WHEN PS.total_logical_reads / NULLIF(PS.execution_count,0) > @ThresholdReads THEN 'Procedure is scan-heavy and may have poor parameter selectivity or stale stats.'
                        ELSE 'Procedure CPU/runtime is high and needs statement-level plan analysis.'
                    END                                                                             AS LikelyCause,
                    'Drill into top statements inside the procedure, review plan cache, and compare runtime by parameter set.' AS RecommendedAction,
                    'EXEC sp_helptext ''' + OBJECT_NAME(PS.object_id, PS.database_id) + ''';'     AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Capture the procedure text, identify the slowest statements, and test representative parameters with actual plans before changing indexes.'
                        ELSE NULL
                    END                                                                             AS DBARunbook
                FROM sys.dm_exec_procedure_stats PS
                WHERE DB_NAME(PS.database_id) = @DbName
                  AND (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
                  AND OBJECT_NAME(PS.object_id, PS.database_id) <> 'dbusp_PerformanceStats'
            ) AS SPQ
            WHERE @OnlyProblems = 0 OR SPQ.Severity <> 'INFO'
            ORDER BY SPQ.AvgElapsedSec DESC;
        END


        -- ============================================================
        -- SECTION 8 : Missing Index Suggestions
        -- SQL Engine internally track karta hai kaunsa index
        -- queries ke liye helpful hota — ye wahi list hai
        -- ImpactScore jitna bada = utna zaruri index
        -- ============================================================
        IF @ShowSection IN (0, 8)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP 20
                    'Missing Indexes'                                           AS [Section],
                    CASE
                        WHEN CAST(MIG.avg_total_user_cost * MIG.avg_user_impact * (MIG.user_seeks + MIG.user_scans) AS DECIMAL(18,1)) >= 100000 THEN 'CRITICAL'
                        WHEN CAST(MIG.avg_total_user_cost * MIG.avg_user_impact * (MIG.user_seeks + MIG.user_scans) AS DECIMAL(18,1)) >= 10000 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Missing Index Candidate'                                   AS IssueType,
                    DB_NAME(MID.database_id)                                    AS DBName,
                    OBJECT_NAME(MID.object_id, MID.database_id)                 AS [Table],
                    CAST(
                        MIG.avg_total_user_cost
                        * MIG.avg_user_impact
                        * (MIG.user_seeks + MIG.user_scans)
                    AS DECIMAL(18,1))                                           AS ImpactScore,
                    CAST(MIG.avg_user_impact AS DECIMAL(5,1))                   AS [Avg Impact %],
                    MIG.user_seeks                                              AS Seeks,
                    MIG.user_scans                                              AS Scans,
                    MID.equality_columns                                        AS EqualityCols,
                    MID.inequality_columns                                      AS InequalityCols,
                    MID.included_columns                                        AS IncludedCols,
                    'Optimizer repeatedly needed a better access path; existing indexes may be missing, non-covering, or stale.' AS LikelyCause,
                    'Validate duplicate/overlapping indexes and test this index in lower environment before production deployment.' AS RecommendedAction,
               'CREATE INDEX ' 
                + QUOTENAME(
                    LEFT(
                        'IX_Missing_' 
                        + OBJECT_NAME(MID.object_id, MID.database_id)
                        + '_'
                        + REPLACE(REPLACE(REPLACE(
                            ISNULL(REPLACE(MID.equality_columns, ', ', '_'), '') 
                            + CASE 
                                WHEN MID.inequality_columns IS NOT NULL 
                                THEN '_' + REPLACE(MID.inequality_columns, ', ', '_') 
                                ELSE '' 
                              END
                        , '[',''), ']',''), ',','')
                    , 120)  -- prevent >128 char error
                )
                + ' ON '
                + QUOTENAME(DB_NAME(MID.database_id)) + '.'
                + QUOTENAME(OBJECT_SCHEMA_NAME(MID.object_id, MID.database_id)) + '.'
                + QUOTENAME(OBJECT_NAME(MID.object_id, MID.database_id))
                + ' ('
                + ISNULL(MID.equality_columns, '')
                + CASE 
                    WHEN MID.inequality_columns IS NOT NULL THEN 
                        CASE WHEN MID.equality_columns IS NOT NULL THEN ', ' ELSE '' END
                        + MID.inequality_columns
                    ELSE '' 
                  END
                + ')'
                + CASE 
                    WHEN MID.included_columns IS NOT NULL 
                    THEN ' INCLUDE (' + MID.included_columns + ')'
                    ELSE '' 
                  END
                + ';' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Compare with existing indexes first. Avoid creating near-duplicate indexes, and measure write overhead after implementation.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM       sys.dm_db_missing_index_details       MID
                INNER JOIN sys.dm_db_missing_index_groups        MIG_GRP ON MID.index_handle           = MIG_GRP.index_handle
                INNER JOIN sys.dm_db_missing_index_group_stats   MIG     ON MIG_GRP.index_group_handle = MIG.group_handle
                WHERE DB_NAME(MID.database_id) = @DbName
            ) AS MIQ
            WHERE @OnlyProblems = 0 OR MIQ.Severity <> 'INFO'
            ORDER BY MIQ.ImpactScore DESC;
        END


        -- ============================================================
        -- SECTION 9 : Index Fragmentation
        -- 10-30% frag  -> REORGANIZE  (lightweight, online)
        -- >30%  frag   -> REBUILD     (heavier but thorough)
        -- ============================================================
        IF @ShowSection IN (0, 9)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'Index Fragmentation'                                       AS [Section],
                    CASE
                        WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Index Fragmentation'                                       AS IssueType,
                    @DbName                                                     AS DBName,
                    OBJECT_NAME(IPS.object_id, @TargetDbId)                     AS [Table],
                    I.name                                                      AS [Index],
                    IPS.index_type_desc                                         AS IndexType,
                    IPS.index_depth                                             AS [Depth],
                    IPS.page_count                                              AS Pages,
                    CAST(IPS.avg_fragmentation_in_percent AS DECIMAL(5,1))      AS [Frag %],
                    'Index has fragmented pages due to page splits and ongoing modifications.' AS LikelyCause,
                    CASE
                        WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold THEN 'Schedule REBUILD in maintenance window; use REORGANIZE only for moderate fragmentation.'
                        ELSE 'Reorganize during low activity and then update statistics if needed.'
                    END                                                         AS RecommendedAction,
                    CASE
                        WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold
                            THEN 'ALTER INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(IPS.object_id, @TargetDbId) + '].[' + OBJECT_NAME(IPS.object_id, @TargetDbId) + '] REBUILD;'
                        ELSE 'ALTER INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(IPS.object_id, @TargetDbId) + '].[' + OBJECT_NAME(IPS.object_id, @TargetDbId) + '] REORGANIZE;'
                    END                                                         AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Skip tiny indexes, avoid rebuilding during peak load, and review fill factor if the same index fragments quickly after maintenance.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.dm_db_index_physical_stats(@TargetDbId, NULL, NULL, NULL, 'LIMITED') IPS
                INNER JOIN sys.indexes I ON IPS.object_id = I.object_id AND IPS.index_id = I.index_id
                WHERE IPS.page_count >= @MinIndexSizePages
                  AND IPS.avg_fragmentation_in_percent >= 10
                  AND I.name IS NOT NULL
            ) AS FragQ
            WHERE @OnlyProblems = 0 OR FragQ.Severity <> 'INFO'
            ORDER BY FragQ.[Frag %] DESC;
        END


        -- ============================================================
        -- SECTION 10 : Unused Indexes
        -- Ye indexes likhe hain but queries use nahi karti
        -- INSERT/UPDATE/DELETE pe ye bhi maintain hote hain = overhead
        -- NOTE: Stats SQL Server restart pe reset hoti hain
        -- ============================================================
        IF @ShowSection IN (0, 10)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'Unused Indexes'                                            AS [Section],
                    CASE
                        WHEN ISNULL(IUS.user_updates,0) >= 1000 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Unused Index'                                              AS IssueType,
                    @DbName                                                     AS DBName,
                    OBJECT_NAME(I.object_id, @TargetDbId)                       AS [Table],
                    I.name                                                      AS [Index],
                    I.type_desc                                                 AS IndexType,
                    ISNULL(IUS.user_seeks,   0)                                 AS UserSeeks,
                    ISNULL(IUS.user_scans,   0)                                 AS UserScans,
                    ISNULL(IUS.user_lookups, 0)                                 AS UserLookups,
                    ISNULL(IUS.user_updates, 0)                                 AS UserUpdates,
                    IUS.last_user_seek                                          AS LastSeek,
                    IUS.last_user_scan                                          AS LastScan,
                    'Index is being maintained for writes but has shown no read benefit since the last server restart.' AS LikelyCause,
                    CASE
                        WHEN ISNULL(IUS.user_updates,0) >= 1000 THEN 'Validate usage history and dependency before dropping this index.'
                        ELSE 'Monitor across at least one business cycle before removal.'
                    END                                                         AS RecommendedAction,
                    'DROP INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(I.object_id, @TargetDbId) + '].[' + OBJECT_NAME(I.object_id, @TargetDbId) + '];' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Do not drop immediately after restart-based stats reset. Validate with query plans, job workloads, and reporting windows first.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM       sys.indexes I
                LEFT  JOIN sys.dm_db_index_usage_stats IUS
                        ON I.object_id = IUS.object_id
                       AND I.index_id = IUS.index_id
                       AND IUS.database_id = @TargetDbId
                INNER JOIN sys.objects O ON I.object_id = O.object_id
                WHERE O.type = 'U'
                  AND I.index_id > 0
                  AND I.is_primary_key = 0
                  AND I.is_unique_constraint = 0
                  AND (ISNULL(IUS.user_seeks,0) + ISNULL(IUS.user_scans,0) + ISNULL(IUS.user_lookups,0)) = 0
            ) AS UnusedQ
            WHERE @OnlyProblems = 0 OR UnusedQ.Severity <> 'INFO'
            ORDER BY UnusedQ.UserUpdates DESC;
        END


        -- ============================================================
        -- SECTION 11 : Top Wait Statistics
        -- Server kahan "wait" kar raha hai — root cause tool
        -- CXPACKET      -> Parallelism (check MAXDOP)
        -- LCK_M_*       -> Locking/Blocking
        -- PAGEIOLATCH_* -> Disk IO slow / missing indexes
        -- SOS_SCHEDULER -> CPU pressure
        -- WRITELOG      -> TLog IO slow
        -- RESOURCE_SEMAPHORE -> Memory grant waits
        -- ============================================================
        IF @ShowSection IN (0, 11)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP 20
                    'Wait Statistics'                                           AS [Section],
                    CASE
                        WHEN CAST(WS.wait_time_ms * 100.0 / NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) >= 25 THEN 'CRITICAL'
                        WHEN CAST(WS.wait_time_ms * 100.0 / NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) >= 10 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    CASE
                        WHEN WS.wait_type LIKE 'LCK%'             THEN 'Locking / Blocking'
                        WHEN WS.wait_type LIKE 'PAGEIOLATCH%'     THEN 'Disk IO'
                        WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Parallelism'
                        WHEN WS.wait_type LIKE 'SOS_SCHEDULER%'   THEN 'CPU Pressure'
                        WHEN WS.wait_type = 'WRITELOG'            THEN 'Transaction Log IO'
                        WHEN WS.wait_type = 'RESOURCE_SEMAPHORE'  THEN 'Memory Grant Pressure'
                        WHEN WS.wait_type LIKE 'ASYNC_NETWORK%'   THEN 'Client Fetch Delay'
                        ELSE 'General Wait'
                    END                                                         AS IssueType,
                    WS.wait_type,
                    CAST(WS.wait_time_ms / 1000.0 AS DECIMAL(18,1))             AS TotalWaitSec,
                    CAST(WS.signal_wait_time_ms / 1000.0 AS DECIMAL(18,1))      AS SignalWaitSec,
                    CAST((WS.wait_time_ms - WS.signal_wait_time_ms) / 1000.0 AS DECIMAL(18,1)) AS ResourceWaitSec,
                    WS.waiting_tasks_count                                      AS WaitingTasks,
                    CAST(WS.wait_time_ms * 100.0
                        / NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) AS [Wait %],
                    CASE
                        WHEN WS.wait_type LIKE 'LCK%'             THEN 'Blocking chains, long transactions, or hot rows/pages are dominating wait time.'
                        WHEN WS.wait_type LIKE 'PAGEIOLATCH%'     THEN 'Storage latency, large scans, or missing indexes are forcing physical reads.'
                        WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Parallel worker imbalance or low cost threshold can create excessive parallel waits.'
                        WHEN WS.wait_type LIKE 'SOS_SCHEDULER%'   THEN 'Runnable tasks are waiting for CPU time.'
                        WHEN WS.wait_type = 'WRITELOG'            THEN 'Transaction log flush latency or undersized log files are slowing commits.'
                        WHEN WS.wait_type = 'RESOURCE_SEMAPHORE'  THEN 'Large memory grants are queued behind limited workspace memory.'
                        WHEN WS.wait_type LIKE 'ASYNC_NETWORK%'   THEN 'Application/client is consuming result sets slowly.'
                        ELSE 'Review this wait with workload context before taking action.'
                    END                                                         AS LikelyCause,
                    CASE
                        WHEN WS.wait_type LIKE 'LCK%'             THEN 'Go to blocking and lock sections first; reduce transaction scope and indexing contention paths.'
                        WHEN WS.wait_type LIKE 'PAGEIOLATCH%'     THEN 'Review high-read queries, missing indexes, and storage latency.'
                        WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Validate MAXDOP and cost threshold after confirming the queries are worth parallelism.'
                        WHEN WS.wait_type LIKE 'SOS_SCHEDULER%'   THEN 'Review scheduler queue, expensive CPU queries, and server concurrency.'
                        WHEN WS.wait_type = 'WRITELOG'            THEN 'Inspect log disk latency, autogrowth pattern, and transaction batch size.'
                        WHEN WS.wait_type = 'RESOURCE_SEMAPHORE'  THEN 'Tune big memory-grant queries and validate max server memory settings.'
                        WHEN WS.wait_type LIKE 'ASYNC_NETWORK%'   THEN 'Review application fetch pattern and avoid returning unnecessary rows.'
                        ELSE 'Monitor with workload change history before remediation.'
                    END                                                         AS RecommendedAction,
                    CASE
                        WHEN WS.wait_type LIKE 'LCK%' THEN 'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 5;'
                        WHEN WS.wait_type LIKE 'PAGEIOLATCH%' THEN 'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 22;'
                        WHEN WS.wait_type = 'WRITELOG' THEN 'DBCC SQLPERF(LOGSPACE);'
                        ELSE '-- Investigate related section in this procedure'
                    END                                                         AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Wait stats are cumulative since last restart (' + CONVERT(VARCHAR(19), @ServerStartTime, 120) + '). Clear interpretation must include workload timing and recent incidents.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.dm_os_wait_stats WS
                WHERE WS.wait_type NOT IN (
                    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_EVENTHANDLER',
                    'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
                    'CHECKPOINT_QUEUE','DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH',
                    'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','XE_DISPATCHER_WAIT',
                    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
                    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
                    'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
                    'WAITFOR','XE_TIMER_EVENT','BROKER_RECEIVE_WAITFOR',
                    'WAITFOR_TASKSHUTDOWN','SLEEP_BUFFERPOOL_SERIALIZATIONTEST',
                    'SLEEP_SYSTEMTASK','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE'
                )
            ) AS WaitQ
            WHERE @OnlyProblems = 0 OR WaitQ.Severity <> 'INFO'
            ORDER BY WaitQ.TotalWaitSec DESC;
        END


        -- ============================================================
        -- SECTION 12 : TempDB Usage Per Session
        -- Kaunsa session TempDB pe zyada load daal raha hai
        -- (#temp tables, sort spills, hash join spills)
        -- ============================================================
        IF @ShowSection IN (0, 12)
        BEGIN
            SELECT TOP (@TopRows)
                'TempDB Usage'                                                                  AS [Section],
                TSU.session_id,
                S.login_name,
                S.host_name,
                S.program_name,
                CAST(TSU.user_objects_alloc_page_count     * 8.0 / 1024 AS DECIMAL(10,1))      AS UserObjects_MB,
                CAST(TSU.internal_objects_alloc_page_count * 8.0 / 1024 AS DECIMAL(10,1))      AS InternalObjects_MB,
                CAST((TSU.user_objects_alloc_page_count
                    + TSU.internal_objects_alloc_page_count) * 8.0 / 1024 AS DECIMAL(10,1))    AS TotalTempDB_MB,
                T.text                                                                          AS CurrentQuery
            FROM       sys.dm_db_session_space_usage   TSU
            INNER JOIN sys.dm_exec_sessions             S   ON TSU.session_id = S.session_id
            LEFT  JOIN sys.dm_exec_requests             R   ON TSU.session_id = R.session_id
            OUTER APPLY sys.dm_exec_sql_text(R.sql_handle)  T
            WHERE TSU.session_id > 50
              AND (TSU.user_objects_alloc_page_count + TSU.internal_objects_alloc_page_count) > 0
            ORDER BY TotalTempDB_MB DESC;
        END


        -- ============================================================
        -- SECTION 13 : Memory Pressure Indicators
        -- ============================================================
        IF @ShowSection IN (0, 13)
        BEGIN
            -- 13a. Overall Memory Summary
            SELECT
                'Memory Summary'                                            AS [Section],
                CASE
                    WHEN memory_utilization_percentage >= 95 OR page_fault_count > 0 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'SQL Memory Pressure'                                       AS IssueType,
                physical_memory_in_use_kb / 1024                           AS [SQL Mem Used MB],
                memory_utilization_percentage                               AS [Mem Utilization %],
                page_fault_count                                            AS PageFaults,
                CASE
                    WHEN memory_utilization_percentage >= 95 THEN 'SQL Server is using almost all memory available within current target.'
                    WHEN page_fault_count > 0 THEN 'Process-level page faults indicate operating system memory churn.'
                    ELSE 'No immediate SQL memory pressure signal from process memory.'
                END                                                         AS LikelyCause,
                CASE
                    WHEN memory_utilization_percentage >= 95 THEN 'Validate max server memory against OS needs and correlate with pending memory grants.'
                    WHEN page_fault_count > 0 THEN 'Review OS memory pressure, antivirus exclusions, and non-SQL memory consumers.'
                    ELSE 'Monitor with memory grants and wait stats.'
                END                                                         AS RecommendedAction,
                'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 29;' AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'Treat this as an indicator only. Confirm with RESOURCE_SEMAPHORE waits, PLE trend, and max server memory configuration before changing memory.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM sys.dm_os_process_memory
            WHERE @OnlyProblems = 0
               OR memory_utilization_percentage >= 95
               OR page_fault_count > 0;

            -- 13b. Buffer Pool usage by Database
            SELECT
                'Buffer Pool by DB'                                         AS [Section],
                CASE database_id
                    WHEN 32767 THEN 'ResourceDB'
                    ELSE DB_NAME(database_id)
                END                                                         AS DBName,
                COUNT(*) * 8 / 1024                                         AS BufferPool_MB,
                SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS DirtyPages_MB
            FROM sys.dm_os_buffer_descriptors
            GROUP BY database_id
            ORDER BY BufferPool_MB DESC;

            -- 13c. Memory Grants (waiter_count > 0 = memory pressure)
            SELECT
                'Memory Grants'                                             AS [Section],
                CASE
                    WHEN SUM(waiter_count) > 0 THEN 'CRITICAL'
                    WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'Workspace Memory Grants'                                   AS IssueType,
                SUM(waiter_count)        AS PendingMemoryGrants,   -- > 0 is bad
                SUM(grantee_count)       AS GrantsOutstanding,
                SUM(target_memory_kb)  / 1024 AS TargetMemoryMB,
                SUM(total_memory_kb)   / 1024 AS TotalMemoryMB,
                SUM(available_memory_kb) / 1024 AS AvailableMemoryMB,
                CASE
                    WHEN SUM(waiter_count) > 0 THEN 'Queries are queued for memory grants, usually due to large sorts, hashes, or concurrent big reports.'
                    WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'Available grant memory is low and may soon queue large queries.'
                    ELSE 'Memory grant subsystem currently looks stable.'
                END                                                         AS LikelyCause,
                CASE
                    WHEN SUM(waiter_count) > 0 THEN 'Tune largest memory grant queries, reduce spills, and validate max server memory.'
                    WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'Review concurrent reporting workload and expensive sort/hash plans.'
                    ELSE 'Continue monitoring.'
                END                                                         AS RecommendedAction,
                'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 11;' AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'Capture currently running heavy queries when pending grants are non-zero; the blockers are often active reports or bad join/sort plans.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM sys.dm_exec_query_resource_semaphores
            HAVING @OnlyProblems = 0
                OR SUM(waiter_count) > 0
                OR SUM(available_memory_kb) / 1024 < 512;
        END


        -- ============================================================
        -- SECTION 14 : Deadlock History
        -- ============================================================
        IF @ShowSection IN (0, 14)
        BEGIN
            SELECT
                'Deadlock Guidance'                                          AS [Section],
                CASE
                    WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'INFO'
                    ELSE 'WARNING'
                END                                                          AS Severity,
                'Deadlock Capture'                                           AS IssueType,
                CASE
                    WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Helper procedure is available for deadlock history review.'
                    ELSE 'No local deadlock helper procedure was found.'
                END                                                          AS LikelyCause,
                CASE
                    WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Review captured deadlock graph and identify object access order mismatch.'
                    ELSE 'Enable deadlock capture through system_health Extended Events or trace flag based monitoring.'
                END                                                          AS RecommendedAction,
                CASE
                    WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'EXEC dbo.dbusp_DeadLockTree;'
                    ELSE 'SELECT CAST(target_data AS XML) FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address WHERE s.name = ''system_health'';'
                END                                                          AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 AND OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Review victim and survivor statements, isolate index/order differences, and reduce transaction overlap.'
                    WHEN @IncludeRunbook = 1 THEN 'On SQL Server 2012, system_health usually captures deadlocks. Extract XML deadlock graph, then fix access order, indexing, or transaction scope.'
                    ELSE NULL
                END                                                          AS DBARunbook
            WHERE @OnlyProblems = 0
               OR OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NULL;

            IF OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL
                EXEC dbo.dbusp_DeadLockTree;
        END


        -- ============================================================
        -- SECTION 15 : Lock Escalation & Contention Pattern
        -- Shows current locks, escalation trends, and blocking patterns
        -- ============================================================
        IF @ShowSection IN (0, 15)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'Lock Contention'                                           AS [Section],
                    CASE
                        WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold THEN 'CRITICAL'
                        WHEN TL.request_mode IN ('X','IX','SIX') THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Lock Contention'                                           AS IssueType,
                    R.session_id                                                AS SessionID,
                    R.database_id                                               AS DBId,
                    DB_NAME(R.database_id)                                      AS DBName,
                    CONVERT(VARCHAR(100), R.command)                            AS Command,
                    CONVERT(VARCHAR(100), R.status)                             AS [Status],
                    TL.request_mode                                             AS LockMode,
                    TL.request_type                                             AS LockType,
                    TL.request_status                                           AS LockStatus,
                    SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) AS LockCount,
                    CASE
                        WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold
                            THEN 'Large lock footprint suggests escalation risk or hot object contention.'
                        ELSE 'Current lock mix may still become a hotspot if transactions stay open.'
                    END                                                         AS LikelyCause,
                    CASE
                        WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold
                            THEN 'Reduce batch size, add selective indexes, and shorten transaction scope.'
                        ELSE 'Monitor lock footprint and correlate with blocker sessions.'
                    END                                                         AS RecommendedAction,
                    'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 5;' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Large lock counts usually come from wide updates/deletes or poor seekability. Review affected statements before considering lock hints.'
                        ELSE NULL
                    END                                                         AS DBARunbook,
                    T.text                                                      AS QueryText
                FROM       sys.dm_exec_requests R
                INNER JOIN sys.dm_tran_locks TL ON R.session_id = TL.request_session_id
                LEFT  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.database_id = @TargetDbId
                  AND TL.request_status <> 'WAIT'
            ) AS LockQ
            WHERE @OnlyProblems = 0 OR LockQ.Severity <> 'INFO'
            ORDER BY LockQ.LockCount DESC, LockQ.SessionID;
        END


        -- ============================================================
        -- SECTION 16 : Stale Statistics Report
        -- Shows tables where statistics haven't been updated in N days
        -- Stale stats = bad cardinality estimates = bad query plans
        -- ============================================================
        IF @ShowSection IN (0, 16)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP 50
                    'Stale Statistics'                                          AS [Section],
                    CASE
                        WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > (@StaleStatsThresholdDays * 4) THEN 'CRITICAL'
                        WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > @StaleStatsThresholdDays THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Stale Statistics'                                          AS IssueType,
                    @DbName                                                     AS DBName,
                    OBJECT_NAME(S.object_id, @TargetDbId)                       AS [Table],
                    S.name                                                      AS StatName,
                    CAST(STATS_DATE(S.object_id, S.stats_id) AS DATE)           AS LastUpdated,
                    DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) AS StaleInDays,
                    'Cardinality estimates may drift after significant data change or prolonged time without stats refresh.' AS LikelyCause,
                    CASE
                        WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > @StaleStatsThresholdDays THEN 'Update statistics for this object and retest affected queries.'
                        ELSE 'Monitor high-change tables for auto-update effectiveness.'
                    END                                                         AS RecommendedAction,
                    'UPDATE STATISTICS [' + OBJECT_SCHEMA_NAME(S.object_id, @TargetDbId) + '].[' + OBJECT_NAME(S.object_id, @TargetDbId) + '] [' + S.name + '];' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Prioritize large or volatile tables first. If parameter-sensitive workloads improved after stats refresh, review auto-stats and maintenance cadence.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.stats S
                WHERE OBJECTPROPERTY(S.object_id, 'IsUserTable') = 1
                  AND STATS_DATE(S.object_id, S.stats_id) IS NOT NULL
            ) AS StatsQ
            WHERE @OnlyProblems = 0 OR StatsQ.Severity <> 'INFO'
            ORDER BY StatsQ.StaleInDays DESC;
        END


        -- ============================================================
        -- SECTION 17 : Database Health & Configuration Status
        -- Recovery model, compatibility, flags, space info
        -- ============================================================
        IF @ShowSection IN (0, 17)
        BEGIN
            SELECT
                'Database Health'                                           AS [Section],
                CASE
                    WHEN D.state_desc <> 'ONLINE' OR D.is_auto_close_on = 1 OR D.is_auto_shrink_on = 1 THEN 'CRITICAL'
                    WHEN D.page_verify_option_desc <> 'CHECKSUM' OR D.compatibility_level < 110 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'Database Configuration'                                    AS IssueType,
                D.name                                                      AS DBName,
                D.recovery_model_desc                                       AS RecoveryModel,
                D.state_desc                                                AS [State],
                D.compatibility_level                                       AS CompatLevel,
                D.is_read_only                                              AS IsReadOnly,
                D.is_auto_close_on                                          AS AutoClose_On,
                D.is_auto_shrink_on                                         AS AutoShrink_On,
                D.page_verify_option_desc                                   AS PageVerify,
                SUM(CAST(MF.size AS BIGINT)) * 8 / 1024 / 1024              AS TotalSizeMB,
                CASE
                    WHEN D.state_desc <> 'ONLINE' THEN 'Database is not fully online.'
                    WHEN D.is_auto_close_on = 1 THEN 'AUTO_CLOSE can cause repeated open/close overhead and plan cache churn.'
                    WHEN D.is_auto_shrink_on = 1 THEN 'AUTO_SHRINK can create fragmentation and unnecessary IO.'
                    WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'Page verification is weaker than CHECKSUM.'
                    WHEN D.compatibility_level < 110 THEN 'Compatibility level is below SQL Server 2012 native level.'
                    ELSE 'Database health indicators are within expected baseline.'
                END                                                         AS LikelyCause,
                CASE
                    WHEN D.is_auto_close_on = 1 OR D.is_auto_shrink_on = 1 THEN 'Disable AUTO_CLOSE / AUTO_SHRINK unless a niche use case requires them.'
                    WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'Set PAGE_VERIFY CHECKSUM.'
                    WHEN D.compatibility_level < 110 THEN 'Review application compatibility and raise database compatibility level when safe.'
                    ELSE 'Continue baseline monitoring.'
                END                                                         AS RecommendedAction,
                CASE
                    WHEN D.is_auto_close_on = 1 THEN 'ALTER DATABASE [' + D.name + '] SET AUTO_CLOSE OFF;'
                    WHEN D.is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + D.name + '] SET AUTO_SHRINK OFF;'
                    WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'ALTER DATABASE [' + D.name + '] SET PAGE_VERIFY CHECKSUM;'
                    ELSE '-- No immediate fix required'
                END                                                         AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'Configuration fixes should be applied during controlled change windows and validated against vendor/application requirements.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM sys.databases D
            INNER JOIN sys.master_files MF ON D.database_id = MF.database_id
            WHERE D.name = @DbName
              AND (
                    @OnlyProblems = 0
                    OR D.state_desc <> 'ONLINE'
                    OR D.is_auto_close_on = 1
                    OR D.is_auto_shrink_on = 1
                    OR D.page_verify_option_desc <> 'CHECKSUM'
                    OR D.compatibility_level < 110
                  )
            GROUP BY D.name, D.recovery_model_desc, D.state_desc, D.compatibility_level,
                     D.is_read_only, D.is_auto_close_on, D.is_auto_shrink_on, D.page_verify_option_desc, D.database_id;
        END


        -- ============================================================
        -- SECTION 18 : Transaction Log Status & VLF Health
        -- High VLF count = more log fragmentation = worse performance
        -- IdeaL: <100 VLFs for healthy log
        -- ============================================================
        IF @ShowSection IN (0, 18)
        BEGIN
            CREATE TABLE #LogSpace
            (
                DatabaseName SYSNAME,
                LogSizeMB DECIMAL(18,5),
                LogSpaceUsedPct DECIMAL(18,5),
                [Status] INT
            );

            INSERT INTO #LogSpace
            EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

            SELECT
                'TLog Status'                                               AS [Section],
                CASE
                    WHEN LS.LogSpaceUsedPct >= 90 THEN 'CRITICAL'
                    WHEN LS.LogSpaceUsedPct >= 70 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'Transaction Log Health'                                    AS IssueType,
                LS.DatabaseName                                             AS DBName,
                CAST(LS.LogSizeMB AS DECIMAL(10,1))                         AS LogSizeMB,
                CAST(LS.LogSizeMB * LS.LogSpaceUsedPct / 100.0 AS DECIMAL(10,1)) AS LogUsedMB,
                CAST(LS.LogSpaceUsedPct AS DECIMAL(10,2))                   AS LogUsedPct,
                'High log usage can come from large transactions, missing log backups, or undersized log/autogrowth pattern.' AS LikelyCause,
                CASE
                    WHEN LS.LogSpaceUsedPct >= 90 THEN 'Take immediate action: check active transaction, log backup chain, and log autogrowth history.'
                    WHEN LS.LogSpaceUsedPct >= 70 THEN 'Review log backup frequency and growth settings before log fills further.'
                    ELSE 'Continue monitoring log utilization.'
                END                                                         AS RecommendedAction,
                'DBCC SQLPERF(LOGSPACE); DBCC LOGINFO(' + QUOTENAME(@DbName, '''') + ');' AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'If recovery model is FULL, confirm recent log backups. If log reuse is blocked, inspect log_reuse_wait_desc and active long transactions.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM #LogSpace LS
            WHERE LS.DatabaseName = @DbName
              AND (@OnlyProblems = 0 OR LS.LogSpaceUsedPct >= 70);

            DROP TABLE #LogSpace;
        END


        -- ============================================================
        -- SECTION 19 : Page Compression Opportunities
        -- Identifies tables that could benefit from compression
        -- Needs 2008+ Enterprise Edition
        -- ============================================================
        IF @ShowSection IN (0, 19)
        BEGIN
            SELECT
                'Compression Opportunities'                                 AS [Section],
                OBJECT_SCHEMA_NAME(IPS.object_id)                          AS SchemaName,
                OBJECT_NAME(IPS.object_id)                                  AS [Table],
                I.name                                                      AS [Index],
                IPS.page_count                                              AS UncompressedPages,
                CAST(IPS.page_count * 8 / 1024.0 AS DECIMAL(10,1))         AS UncompressedSizeMB,
                CASE
                    WHEN IPS.page_count > 1000
                        THEN 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(IPS.object_id) + '].[' 
                            + OBJECT_NAME(IPS.object_id) + '] REBUILD WITH (DATA_COMPRESSION = PAGE);'
                    ELSE 'Too small for compression benefit'
                END                                                         AS CompressionScript
            FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') IPS
            INNER JOIN sys.indexes I ON IPS.object_id = I.object_id AND IPS.index_id = I.index_id
            WHERE IPS.page_count > 1000
              AND IPS.alloc_unit_type_desc = 'IN_ROW_DATA'
            ORDER BY IPS.page_count DESC;
        END


        -- ============================================================
        -- SECTION 20 : Plan Cache Regression / Query Store Fallback
        -- SQL Server 2012 friendly fallback for regressed / unstable plans
        -- ============================================================
        IF @ShowSection IN (0, 20)
        BEGIN
            SELECT *
            FROM
            (
                SELECT TOP (@QueryStoreThreshold)
                    'Plan Cache Regression'                                   AS [Section],
                    CASE
                        WHEN QS.plan_generation_num >= 10 OR QS.max_elapsed_time >= (QS.min_elapsed_time * 20) THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                       AS Severity,
                    'Plan Regression / Instability'                           AS IssueType,
                    DB_NAME(T.dbid)                                           AS DBName,
                    QS.execution_count                                         AS ExecutionCount,
                    QS.plan_generation_num                                     AS RecompilationCount,
                    CAST(QS.min_elapsed_time / 1000000.0 AS DECIMAL(18,4))    AS MinElapsedSec,
                    CAST(QS.max_elapsed_time / 1000000.0 AS DECIMAL(18,4))    AS MaxElapsedSec,
                    CAST((QS.max_elapsed_time - QS.min_elapsed_time) / 1000000.0 AS DECIMAL(18,4)) AS VarianceSec,
                    sys.fn_varbintohexstr(QS.plan_handle)                      AS PlanHandleHex,
                    SUBSTRING(T.text, 1, 200)                                  AS QueryPreview,
                    'Plan cache shows high runtime variance or repeated recompiles, often due to parameter sensitivity, stats changes, or unstable memory grants.' AS LikelyCause,
                    'Compare good vs bad parameter sets, inspect cached plan, and consider stats refresh or targeted recompilation strategy.' AS RecommendedAction,
                    'DBCC FREEPROCCACHE(' + sys.fn_varbintohexstr(QS.plan_handle) + '); -- clear only this plan after validation' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'On SQL Server 2012 this section is a Query Store fallback. Validate parameter sniffing, stale statistics, and plan cache volatility before forcing plan-level changes.'
                        ELSE NULL
                    END                                                       AS DBARunbook
                FROM sys.dm_exec_query_stats QS
                CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
                WHERE DB_NAME(T.dbid) = @DbName
                  AND T.text IS NOT NULL
                  AND QS.execution_count > 5
                  AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
            ) AS PlanQ
            WHERE @OnlyProblems = 0 OR PlanQ.Severity <> 'INFO'
            ORDER BY PlanQ.VarianceSec DESC, PlanQ.RecompilationCount DESC;
        END


        -- ============================================================
        -- SECTION 21 : Parameter Sniffing Detection
        -- Queries with high variance in execution time/reads across invocations
        -- = potential parameter sniffing victims
        -- ============================================================
        IF @ShowSection IN (0, 21)
        BEGIN
            SELECT TOP (@TopRows)
                'Parameter Sniffing Risk'                                   AS [Section],
                CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsedSec,
                QS.execution_count,
                CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
                QS.total_logical_reads,
                CAST((QS.total_logical_reads / NULLIF(QS.execution_count,0)) AS DECIMAL(10,0)) AS AvgReads,
                DB_NAME(T.dbid) AS DBName,
                SUBSTRING(T.text, 1, 100) AS QueryPreview,
                'HIGH VARIANCE - Monitor for sniffing' AS Recommendation
            FROM sys.dm_exec_query_stats QS
            CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
            WHERE DB_NAME(T.dbid) = @DbName
              AND T.text IS NOT NULL
              AND QS.execution_count > 10
              AND QS.min_elapsed_time < QS.max_elapsed_time * 0.1
            ORDER BY (QS.max_elapsed_time - QS.min_elapsed_time) DESC;
        END


        -- ============================================================
        -- SECTION 22 : Database IO Subsystem Analysis
        -- Physical reads, writes, latency metrics from DMVs
        -- ============================================================
        IF @ShowSection IN (0, 22)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'IO Subsystem'                                              AS [Section],
                    CASE
                        WHEN CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) >= 50
                          OR CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) >= 20 THEN 'CRITICAL'
                        WHEN CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) >= 20
                          OR CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) >= 10 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Database File Latency'                                     AS IssueType,
                    DB_NAME(MFFP.database_id)                                   AS DBName,
                    MFFP.file_id,
                    MF.name                                                     AS FileName,
                    MF.physical_name                                            AS PhysicalPath,
                    MF.type_desc                                                AS FileType,
                    MFFP.num_of_reads                                           AS PhysicalReads,
                    CAST(MFFP.num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(10,1)) AS DataReadMB,
                    CAST(MFFP.io_stall_read_ms / 1000.0 AS DECIMAL(10,1))       AS ReadLatencySec,
                    CAST(CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) AS DECIMAL(10,2)) AS AvgReadLatencyMs,
                    MFFP.num_of_writes                                          AS PhysicalWrites,
                    CAST(MFFP.num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(10,1)) AS DataWrittenMB,
                    CAST(MFFP.io_stall_write_ms / 1000.0 AS DECIMAL(10,1))      AS WriteLatencySec,
                    CAST(CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) AS DECIMAL(10,2)) AS AvgWriteLatencyMs,
                    CAST(MFFP.io_stall / 1000.0 AS DECIMAL(10,1))               AS TotalLatencySec,
                    'Slow storage, overloaded LUN, large scans, or repeated autogrowth can increase file stall time.' AS LikelyCause,
                    'Review storage latency, file growth settings, and top read/write queries before moving files.' AS RecommendedAction,
                    'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 4;' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Use average latency ms per read/write as the first signal. Data files above ~20ms read or log files above ~10ms write deserve investigation.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.dm_io_virtual_file_stats(@TargetDbId, NULL) MFFP
                INNER JOIN sys.master_files MF ON MFFP.database_id = MF.database_id AND MFFP.file_id = MF.file_id
            ) AS IOFileQ
            WHERE @OnlyProblems = 0 OR IOFileQ.Severity <> 'INFO'
            ORDER BY IOFileQ.TotalLatencySec DESC;
        END


        -- ============================================================
        -- SECTION 23 : Connection Pool Analysis
        -- Current connections, idle sessions, potential connection issues
        -- ============================================================
        IF @ShowSection IN (0, 23)
        BEGIN
            SELECT
                'Connection Analysis'                                       AS [Section],
                COUNT(*)                                                    AS TotalConnections,
                SUM(CASE WHEN S.status = 'running' THEN 1 ELSE 0 END)      AS RunningConnections,
                SUM(CASE WHEN S.status = 'sleeping' THEN 1 ELSE 0 END)     AS SleepingConnections,
                SUM(CASE WHEN S.status = 'dormant' THEN 1 ELSE 0 END)      AS DormantConnections,
                MAX(DATEDIFF(SECOND, S.last_request_end_time, GETDATE())) AS MaxIdleSecond
            FROM sys.dm_exec_sessions S
            WHERE S.session_id > 50;

            -- Top connections by database
            SELECT TOP 20
                'Connections by DB'                                         AS [Section],
                DB_NAME(S.database_id) AS DBName,
                S.login_name,
                S.host_name,
                S.program_name,
                COUNT(*) AS ConnectionCount,
                SUM(S.memory_usage) * 8 / 1024 AS MemoryUsageMB
            FROM sys.dm_exec_sessions S
            WHERE S.session_id > 50
            GROUP BY DB_NAME(S.database_id), S.login_name, S.host_name, S.program_name
            ORDER BY ConnectionCount DESC;
        END


        -- ============================================================
        -- SECTION 24 : CPU & Scheduler Queue Analysis
        -- CPU throttling, runnable tasks, scheduler health
        -- ============================================================
        IF @ShowSection IN (0, 24)
        BEGIN
            -- Scheduler stats
            SELECT *
            FROM
            (
                SELECT
                    'Scheduler Health'                                          AS [Section],
                    CASE
                        WHEN runnable_tasks_count > 10 THEN 'CRITICAL'
                        WHEN runnable_tasks_count > 5 THEN 'WARNING'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'CPU Scheduler Queue'                                        AS IssueType,
                    scheduler_id,
                    cpu_id,
                    status,
                    runnable_tasks_count,
                    current_tasks_count,
                    CAST(work_queue_count AS DECIMAL(10,0))                     AS QueuedTasks,
                    CASE
                        WHEN runnable_tasks_count > 5 THEN 'Schedulers have runnable tasks waiting for CPU, indicating CPU pressure or excessive parallelism.'
                        ELSE 'Scheduler queue depth is within normal range.'
                    END                                                         AS LikelyCause,
                    CASE
                        WHEN runnable_tasks_count > 5 THEN 'Review CPU-heavy queries, MAXDOP, and concurrent workload bursts.'
                        ELSE 'Continue monitoring.'
                    END                                                         AS RecommendedAction,
                    'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 2;' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Sustained runnable queue is more important than a one-time spike. Re-sample during business peak before making server-wide changes.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.dm_os_schedulers
                WHERE scheduler_id < 256
            ) AS SchedQ
            WHERE @OnlyProblems = 0 OR SchedQ.Severity <> 'INFO'
            ORDER BY SchedQ.runnable_tasks_count DESC;

            -- CPU utilization over time
            SELECT
                'CPU Utilization'                                           AS [Section],
                CASE
                    WHEN CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) >= 25 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'CPU Signal Waits'                                          AS IssueType,
                CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) AS CPUUtilizationPercent,
                COUNT(DISTINCT wait_type)                                   AS UniqueWaitTypes,
                'High signal wait ratio means workers are waiting for CPU after getting in line.' AS LikelyCause,
                'If ratio remains high with runnable tasks, tune expensive CPU queries or reduce concurrency.' AS RecommendedAction,
                'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 11;' AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'This is not exact OS CPU percent; it is SQL wait-based pressure signal. Read it together with scheduler queues and top CPU queries.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM (
                SELECT signal_wait_time_ms, wait_time_ms, wait_type
                FROM sys.dm_os_wait_stats
                WHERE wait_type NOT IN ('SLEEP_TASK', 'WAITFOR', 'SOS_SCHEDULER_YIELD')
            ) WS
            HAVING @OnlyProblems = 0
                OR CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) >= 25;
        END


        -- ============================================================
        -- SECTION 25 : Table Partitioning Opportunities
        -- Large tables that could benefit from partitioning
        -- ============================================================
        IF @ShowSection IN (0, 25)
        BEGIN
            SELECT TOP 20
                'Partitioning Candidates'                                   AS [Section],
                OBJECT_SCHEMA_NAME(T.object_id)                            AS SchemaName,
                T.name                                                      AS TableName,
                SUM(CAST(P.rows AS BIGINT))                                 AS [RowCount],
                CAST(SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS SizeGB,
                CASE
                    WHEN SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 > 10
                        THEN 'Good candidate for partitioning (Range on DateAdded or ID)'
                    ELSE 'Monitor'
                END AS Recommendation
            FROM sys.tables T
            INNER JOIN sys.indexes I ON T.object_id = I.object_id
            INNER JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
            INNER JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
            WHERE I.index_id < 2
            GROUP BY T.object_id, T.name, OBJECT_SCHEMA_NAME(T.object_id)
            HAVING SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 > 1  -- > 1 GB
            ORDER BY SizeGB DESC;
        END


        -- ============================================================
        -- SECTION 26 : Query Recompilation & Query Hints Analysis
        -- Recompilation counts, forced plans, optimization hints
        -- ============================================================
        IF @ShowSection IN (0, 26)
        BEGIN
            SELECT TOP (@TopRows)
                'Query Recompilation Risk'                                  AS [Section],
                QS.plan_generation_num                                      AS RecompilationCount,
                QS.execution_count,
                CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
                DB_NAME(T.dbid)                                             AS DBName,
                SUBSTRING(T.text, 1, 100)                                   AS QueryPreview,
                CASE
                    WHEN QS.plan_generation_num > 10
                        THEN 'HIGH RECOMPILE COUNT - Use WITH(RECOMPILE) or add OPTION(RECOMPILE)'
                    ELSE 'Normal'
                END AS Recommendation
            FROM sys.dm_exec_query_stats QS
            CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
            WHERE DB_NAME(T.dbid) = @DbName
              AND T.text IS NOT NULL
            ORDER BY RecompilationCount DESC;
        END


        -- ============================================================
        -- SECTION 27 : Database Recovery Model & Backup Status
        -- Shows backup history, LSN progression, recovery readiness
        -- ============================================================
        IF @ShowSection IN (0, 27)
        BEGIN
            SELECT
                'Recovery Model & Backups'                                  AS [Section],
                CASE
                    WHEN BS.LastFullBackup IS NULL THEN 'CRITICAL'
                    WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'CRITICAL'
                    WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'WARNING'
                    ELSE 'INFO'
                END                                                         AS Severity,
                'Backup Compliance'                                         AS IssueType,
                DB_NAME(D.database_id)                                      AS DBName,
                D.recovery_model_desc                                       AS RecoveryModel,
                BS.LastFullBackup                                           AS LastFullBackup,
                BL.LastLogBackup                                            AS LastLogBackup,
                DATEDIFF(HOUR, BS.LastFullBackup, GETDATE())                AS HoursSinceFullBackup,
                DATEDIFF(DAY, BS.LastFullBackup, GETDATE())                 AS DaysSinceFullBackup,
                BS.first_lsn,
                BS.last_lsn,
                CASE
                    WHEN BS.LastFullBackup IS NULL THEN 'No full backup found in msdb history.'
                    WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'FULL recovery model without log backup history breaks point-in-time recovery.'
                    WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'Full backup is older than expected policy threshold.'
                    ELSE 'Backup history looks present for the current policy baseline.'
                END                                                         AS LikelyCause,
                CASE
                    WHEN BS.LastFullBackup IS NULL THEN 'Take an immediate full backup after business approval.'
                    WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'Start regular transaction log backups immediately.'
                    WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'Review SQL Agent/job schedule and backup retention failures.'
                    ELSE 'Continue monitoring backup jobs and test restores.'
                END                                                         AS RecommendedAction,
                CASE
                    WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'BACKUP LOG [' + D.name + '] TO DISK = ''<path>'' WITH INIT;'
                    ELSE 'BACKUP DATABASE [' + D.name + '] TO DISK = ''<path>'' WITH INIT;'
                END                                                         AS FixScript,
                CASE
                    WHEN @IncludeRunbook = 1 THEN 'Backup history in msdb is necessary but not sufficient. Also validate restore tests, job alerts, and backup file availability.'
                    ELSE NULL
                END                                                         AS DBARunbook
            FROM sys.databases D
            LEFT JOIN (
                SELECT database_name, MAX(backup_finish_date) AS LastFullBackup,
                       MAX(first_lsn) as first_lsn, MAX(last_lsn) as last_lsn
                FROM msdb.dbo.backupset
                WHERE type = 'D'  -- Full backup
                GROUP BY database_name
            ) BS ON D.name = BS.database_name
            LEFT JOIN (
                SELECT database_name, MAX(backup_finish_date) AS LastLogBackup
                FROM msdb.dbo.backupset
                WHERE type = 'L'
                GROUP BY database_name
            ) BL ON D.name = BL.database_name
            WHERE D.name = @DbName
              AND (
                    @OnlyProblems = 0
                    OR BS.LastFullBackup IS NULL
                    OR (D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL)
                    OR DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24
                  );
        END


        -- ============================================================
        -- SECTION 28 : Object Permissions & Security Audit
        -- Shows exposed permissions, excessive grants, security risks
        -- ============================================================
        IF @ShowSection IN (0, 28)
        BEGIN
            SELECT TOP 50
                'Permission Audit'                                          AS [Section],
                SCHEMA_NAME(O.schema_id)                                    AS SchemaName,
                O.name                                                      AS ObjectName,
                O.type_desc AS ObjectType,
                DP.name                                                     AS PrincipalName,
                DP.type_desc                                                AS PrincipalType,
                PR.permission_name                                          AS Permission,
                PR.state_desc                                               AS PermState,
                CASE
                    WHEN DP.name = 'public'
                        THEN 'SECURITY RISK - Excessive public permissions'
                    WHEN PR.permission_name IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE') AND DP.type = 'U'
                        THEN 'Review - User has DML'
                    ELSE 'Normal'
                END AS SecurityNote
            FROM sys.database_principals DP
            INNER JOIN sys.database_permissions PR ON DP.principal_id = PR.grantee_principal_id
            INNER JOIN sys.objects O ON PR.major_id = O.object_id
            WHERE (DP.name = 'public' OR DP.type = 'U')
              AND PR.state_desc = 'GRANT'
            ORDER BY DP.name, O.name;
        END


        -- ============================================================
        -- SECTION 29 : Server Configuration Issues & Best Practices
        -- Checks for bad server configs that impact DB performance
        -- ============================================================
        IF @ShowSection IN (0, 29)
        BEGIN
            SELECT *
            FROM
            (
                SELECT
                    'Config Issues'                                             AS [Section],
                    CASE
                        WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'CRITICAL'
                        WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'WARNING'
                        WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'WARNING'
                        WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'WARNING'
                        WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'INFO'
                        ELSE 'INFO'
                    END                                                         AS Severity,
                    'Server Configuration'                                      AS IssueType,
                    SCI.name                                                    AS ConfigName,
                    SCI.value_in_use                                            AS CurrentValue,
                    SCI.value                                                   AS ConfigValue,
                    SCI.description                                             AS Description,
                    CASE
                        WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'Priority boost is not recommended for SQL Server and can destabilize scheduling.'
                        WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'MAXDOP may be too high for current CPU topology.'
                        WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'Very low cost threshold can push too many queries into parallel plans.'
                        WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'SQL Server max memory is set very low for most workloads.'
                        WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'Ad hoc plan cache bloat risk exists on chatty ad hoc workloads.'
                        ELSE 'Configuration looks acceptable, but validate against workload baseline.'
                    END                                                         AS LikelyCause,
                    CASE
                        WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'Disable priority boost.'
                        WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'Review and lower MAXDOP after workload testing.'
                        WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'Raise cost threshold after confirming plan behavior.'
                        WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'Increase max server memory while preserving OS headroom.'
                        WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'Enable optimize for ad hoc workloads if plan cache churn is confirmed.'
                        ELSE 'No immediate server-wide change required.'
                    END                                                         AS RecommendedAction,
                    'EXEC sp_configure ''' + SCI.name + ''', <new_value>; RECONFIGURE;' AS FixScript,
                    CASE
                        WHEN @IncludeRunbook = 1 THEN 'Treat server-level changes carefully. Validate after-hours or in staging first, because these affect all databases on the instance.'
                        ELSE NULL
                    END                                                         AS DBARunbook
                FROM sys.configurations SCI
                WHERE SCI.name IN (
                    'max server memory (MB)',
                    'min server memory (MB)',
                    'max degree of parallelism',
                    'cost threshold for parallelism',
                    'optimize for ad hoc workloads',
                    'default full-text language',
                    'network packet size (B)',
                    'priority boost'
                )
            ) AS ConfigQ
            WHERE @OnlyProblems = 0 OR ConfigQ.Severity <> 'INFO'
            ORDER BY CASE ConfigQ.Severity WHEN 'CRITICAL' THEN 1 WHEN 'WARNING' THEN 2 ELSE 3 END, ConfigQ.ConfigName;
        END


        -- ============================================================
        -- SECTION 30 : Extended Events & Trace Configuration
        -- Shows tracing setup, extended events sessions, audit status
        -- ============================================================
        IF @ShowSection IN (0, 30)
        BEGIN
            -- Extended Events sessions (if available)
            IF OBJECT_ID('sys.dm_xe_sessions', 'V') IS NOT NULL
            BEGIN
                SELECT
                    'Extended Events'                                       AS [Section],
                    name                                                    AS SessionName,
                    'Active'                                                AS SessionStatus
                FROM sys.dm_xe_sessions;
            END
            ELSE
            BEGIN
                SELECT 'Extended Events not available on this SQL Server version' AS [Info];
            END

            -- Server Audits (if available)
            IF OBJECT_ID('sys.server_audits', 'V') IS NOT NULL
            BEGIN
                SELECT
                    'Server Audits'                                         AS [Section],
                    name,
                    CASE WHEN is_state_enabled = 1 THEN 'ENABLED' ELSE 'DISABLED' END
                FROM sys.server_audits;
            END
            ELSE
            BEGIN
                SELECT 'Extended Events / Audits not available on this SQL Server version' AS [Info];
            END

            PRINT N'TIP: Enable Extended Events or SQL Trace for troubleshooting:';
            PRINT N'  - xe_session_health: Tracks system issues';
            PRINT N'  - trace_events: Deadlock, long-running queries';
        END

    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER()    AS ErrorNum,
            ERROR_SEVERITY()  AS Severity,
            ERROR_STATE()     AS [State],
            ERROR_PROCEDURE() AS [Procedure],
            ERROR_LINE()      AS [Line],
            ERROR_MESSAGE()   AS ErrorMessage;
    END CATCH
END
GO

-- ── Quick run examples ───────────────────────────────────────
-- Full enterprise diagnostic dashboard
-- EXEC dbo.dbusp_PerformanceStats;

-- Specific DB
-- EXEC dbo.dbusp_PerformanceStats @DbName = 'Vipul';

-- Specific diagnostic sections:
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 5;   -- Blocking queries
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 15;  -- Lock contention
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 16;  -- Stale statistics
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 17;  -- Database health
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 22;  -- IO analysis
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 29;  -- Config issues
-- EXEC dbo.dbusp_PerformanceStats @OnlyProblems = 1;  -- Hide healthy rows

-- Custom thresholds & full diagnostics
-- EXEC dbo.dbusp_PerformanceStats @DbName = 'PayrollDB', @ThresholdAvgSec = 5, 
--     @TopRows = 20, @StaleStatsThresholdDays = 14, @ShowSection = 0, @IncludeRunbook = 1;

What This Script Does (Very Powerful )

This is a complete DBA dashboard with 30 sections.

Query Performance

Top CPU queries
Slow queries
IO heavy queries
Execution plans

Index Health

Missing indexes
Fragmentation
Unused indexes

Memory & CPU

Memory pressure
CPU usage
Scheduler load

Concurrency

Blocking queries
Deadlocks
Lock contention

Database Health

Large tables
Log usage
Backup status

Security & Config

Permissions audit
Server configuration issues

How to Use Performance Script

Run full dashboard:

EXEC dbo.dbusp_PerformanceStats;

Check specific section:

-- Blocking queries
EXEC dbo.dbusp_PerformanceStats @ShowSection = 5;

-- Wait stats
EXEC dbo.dbusp_PerformanceStats @ShowSection = 11;

-- Index fragmentation
EXEC dbo.dbusp_PerformanceStats @ShowSection = 9;

-- IO analysis
EXEC dbo.dbusp_PerformanceStats @ShowSection = 22;

Real-Life DBA Workflow (Very Important )

Step 1: Check Blocking

EXEC dbusp_BlockingTree;

👉 If blocking found → fix immediately

Step 2: Check Wait Stats

EXEC dbo.dbusp_PerformanceStats @ShowSection = 11;

👉 Find root cause:

LCK → Blocking
PAGEIOLATCH → IO issue
CXPACKET → Parallelism

Step 3: Check Slow Queries

EXEC dbo.dbusp_PerformanceStats @ShowSection = 3;

Step 4: Check Index Issues

EXEC dbo.dbusp_PerformanceStats @ShowSection = 8;
EXEC dbo.dbusp_PerformanceStats @ShowSection = 9;

Step 5: Check Memory & CPU

EXEC dbo.dbusp_PerformanceStats @ShowSection = 13;
EXEC dbo.dbusp_PerformanceStats @ShowSection = 24;

Best Practices (Golden Rules)

Keep transactions short
Always use proper indexes
Avoid large updates in peak time
Monitor wait stats regularly
Never kill session without checking

Conclusion

For any SQL Server DBA:

👉 dbusp_BlockingTree = Quick troubleshooting tool
👉 dbusp_PerformanceStats = Complete health dashboard

If you use both together, you can:

Detect problems fast
Fix root cause
Improve system performance

Results for "DatabasePerformance"

SQL Server Deadlock & Performance Monitoring Made Easy