SQL Server DBA Health Check Toolkit

1 Instance Version and Build

Always start here. Confirm the build is current and identify any outstanding security updates. SQL Server 2022 Mainstream Support ends January 11, 2028. Extended Support ends January 11, 2033. As of April 2026, CU24 + GDR (build 16.0.4250.1) is the latest recommended build.

-- Confirm this is SQL Server 2022 before running the full toolkit
IF NOT EXISTS (SELECT * WHERE CONVERT(varchar(128), SERVERPROPERTY('ProductMajorVersion')) = '16')
    RAISERROR('This toolkit targets SQL Server 2022 (build 16.x). Verify your version.', 18, 16);
ELSE
    PRINT N'Confirmed: SQL Server 2022';

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];
------
-- SQL Server 2022 build reference (select recent builds):
-- 16.0.4250.1  CU24 + GDR  04/14/2026  (current recommended)
-- 16.0.4245.2  CU24        03/12/2026
-- 16.0.4240.4  CU23 + GDR  03/10/2026
-- 16.0.4236.2  CU23        01/29/2026
-- 16.0.4230.2  CU22 + GDR  01/13/2026
-- Full build history: https://learn.microsoft.com/troubleshoot/sql/releases/sqlserver-2022/build-versions

2 Core Counts and Hardware

Reads socket, physical core, and logical core counts from the error log. Also confirms whether SQL Server licensing is capping the core count. Watch for: “using 40 logical processors based on SQL Server licensing” which indicates grandfathered Server/CAL licensing.

-- May take a few seconds on large error logs
-- Returns no results if error log was recycled since last startup
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

SELECT
    cpu_count                                        AS [Logical CPU Count],
    scheduler_count,
    (socket_count * cores_per_socket)                AS [Physical Core Count],
    socket_count                                     AS [Socket Count],
    cores_per_socket,
    numa_node_count,
    physical_memory_kb / 1024                        AS [Physical Memory (MB)],
    max_workers_count                                AS [Max Workers Count],
    softnuma_configuration_desc                      AS [Soft NUMA Config],
    sql_memory_model_desc,                           -- CONVENTIONAL / LOCK_PAGES / LARGE_PAGES
    virtual_machine_type_desc                        AS [VM Type],
    sqlserver_start_time                             AS [SQL Start Time],
    DATEDIFF(hour, sqlserver_start_time, GETDATE())  AS [Uptime (hrs)]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------
-- sql_memory_model_desc = LOCK_PAGES means LPIM is active (recommended)
-- sql_memory_model_desc = LARGE_PAGES means TF 834 or 876 is active
-- Standard Edition: capped at 24 physical cores and 128 GB RAM

3 Server Properties

A comprehensive instance property snapshot. The last three properties — IsExternalGovernanceEnabled, IsServerSuspendedForSnapshotBackup, and SuspendedDatabaseCount — are new in SQL Server 2022.

SELECT
    SERVERPROPERTY('MachineName')                         AS [MachineName],
    SERVERPROPERTY('ServerName')                          AS [ServerName],
    SERVERPROPERTY('InstanceName')                        AS [Instance],
    SERVERPROPERTY('Edition')                             AS [Edition],
    SERVERPROPERTY('ProductLevel')                        AS [ProductLevel],
    SERVERPROPERTY('ProductUpdateLevel')                  AS [ProductUpdateLevel],
    SERVERPROPERTY('ProductVersion')                      AS [ProductVersion],
    SERVERPROPERTY('ProductBuild')                        AS [ProductBuild],
    SERVERPROPERTY('ProductBuildType')                    AS [BuildType],     -- GDR or NULL (CU build)
    SERVERPROPERTY('ProductUpdateReference')              AS [KB Article],
    SERVERPROPERTY('IsClustered')                         AS [IsClustered],
    SERVERPROPERTY('IsHadrEnabled')                       AS [IsHadrEnabled],
    SERVERPROPERTY('HadrManagerStatus')                   AS [HadrManagerStatus],
    SERVERPROPERTY('IsIntegratedSecurityOnly')            AS [WindowsAuthOnly],
    SERVERPROPERTY('IsXTPSupported')                      AS [InMemoryOLTP],
    SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')     AS [TempdbMetaMemOpt],
    SERVERPROPERTY('IsPolybaseInstalled')                 AS [IsPolybaseInstalled],
    SERVERPROPERTY('Collation')                           AS [Collation],
    SERVERPROPERTY('ErrorLogFileName')                    AS [ErrorLogFileName],
    SERVERPROPERTY('InstanceDefaultDataPath')             AS [DefaultDataPath],
    SERVERPROPERTY('InstanceDefaultLogPath')              AS [DefaultLogPath],
    SERVERPROPERTY('InstanceDefaultBackupPath')           AS [DefaultBackupPath],
    -- New in SQL Server 2022:
    SERVERPROPERTY('IsExternalGovernanceEnabled')         AS [ExternalGovernance],
    SERVERPROPERTY('IsServerSuspendedForSnapshotBackup')  AS [SuspendedForSnapshot],
    SERVERPROPERTY('SuspendedDatabaseCount')              AS [SuspendedDBCount];

4 Configuration Values

Reviews all instance-level sp_configure settings. The items below are the highest-value ones to validate on every server.

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);
------
-- Critical settings to validate:
-- backup checksum default           → 1  (enables backup integrity verification)
-- backup compression default        → 1  (compress all backups by default)
-- backup compression algorithm      → consider ZSTD (new in SQL 2022, best ratio)
-- cost threshold for parallelism    → default 5 is too low; start at 25-50
-- max degree of parallelism         → never leave at 0 on multi-core servers
-- max server memory (MB)            → MUST be set; default 2147483647 will starve the OS
-- optimize for ad hoc workloads     → 1  (reduces plan cache bloat)
-- remote admin connections          → 1  (enables DAC for emergency access)
-- priority boost                    → 0  (never enable in production)
-- lightweight pooling               → 0  (fiber mode; do not enable)
-- New in SQL Server 2022:
-- backup compression algorithm, ADR Cleaner Thread Count, hardware offload config

5 Global Trace Flags

Shows which global trace flags are currently active. See Section 21 for the full SQL Server 2022 trace flag baseline checklist including which flags are now obsolete.

DBCC TRACESTATUS(-1);
------
-- No results = no global trace flags enabled
-- See Section 21 for the recommended SQL Server 2022 trace flag baseline and
-- obsolescence guidance for TF 2371, 1117, 1118, and 8075

6 Process Memory and Locked Pages

Confirms whether SQL Server is under internal memory pressure and whether Lock Pages in Memory (LPIM) is active. Both process_physical_memory_low and process_virtual_memory_low should be 0.

SELECT
    physical_memory_in_use_kb / 1024   AS [SQL Server Memory Usage (MB)],
    locked_page_allocations_kb / 1024  AS [Locked Pages Allocation (MB)],  -- > 0 = LPIM active
    large_page_allocations_kb / 1024   AS [Large Pages Allocation (MB)],   -- > 0 = TF 834 or 876 active
    page_fault_count,
    memory_utilization_percentage,
    available_commit_limit_kb,
    process_physical_memory_low,   -- want 0
    process_virtual_memory_low     -- want 0
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

-- Consolidated memory snapshot including PLE, buffer pool, and LPIM status
DECLARE @MaxServerMemoryMB     DECIMAL(15,2);
DECLARE @SQLServerMemoryMB     BIGINT;
DECLARE @LockedPagesMB         BIGINT;
DECLARE @TotalPhysicalMB       DECIMAL(15,2);
DECLARE @AvailablePhysicalMB   BIGINT;
DECLARE @SystemMemoryState     NVARCHAR(50);
DECLARE @SQLStartTime          DATETIME;
DECLARE @BufferPoolMB          DECIMAL(15,2);
DECLARE @SOSNodeMB             DECIMAL(15,2);
DECLARE @AvgPLE                INT = 0;

SELECT @TotalPhysicalMB = total_physical_memory_kb/1024,
       @AvailablePhysicalMB = available_physical_memory_kb/1024,
       @SystemMemoryState = system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK);

SELECT @MaxServerMemoryMB = CONVERT(INT, value)
FROM sys.configurations WITH (NOLOCK)
WHERE [name] = N'max server memory (MB)';

SELECT @SQLServerMemoryMB = physical_memory_in_use_kb/1024,
       @LockedPagesMB     = locked_page_allocations_kb/1024
FROM sys.dm_os_process_memory WITH (NOLOCK);

SELECT @SQLStartTime = sqlserver_start_time FROM sys.dm_os_sys_info WITH (NOLOCK);

SELECT @BufferPoolMB = CAST(SUM(mc.pages_kb)/1024.0 AS DECIMAL(15,2))
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
WHERE mc.[type] = N'MEMORYCLERK_SQLBUFFERPOOL';

SET @AvgPLE = (SELECT AVG(cntr_value)
               FROM sys.dm_os_performance_counters WITH (NOLOCK)
               WHERE [object_name] LIKE N'%Buffer Node%'
               AND counter_name = N'Page life expectancy');

SELECT
    @@SERVERNAME                                                        AS [Server Name],
    CONVERT(INT, @TotalPhysicalMB)                                      AS [OS Physical Memory (MB)],
    @SystemMemoryState                                                   AS [System Memory State],
    @AvailablePhysicalMB                                                AS [OS Available Memory (MB)],
    CONVERT(INT, @MaxServerMemoryMB)                                    AS [SQL Max Server Memory (MB)],
    CONVERT(DECIMAL(18,2), (@MaxServerMemoryMB / @TotalPhysicalMB)*100) AS [Max Memory % of RAM],
    @SQLServerMemoryMB                                                   AS [SQL Total Memory Usage (MB)],
    @AvgPLE                                                             AS [Page Life Expectancy (sec)],
    @BufferPoolMB                                                       AS [Buffer Pool (MB)],
    @LockedPagesMB                                                      AS [Locked Pages (MB)],
    @SQLStartTime                                                       AS [SQL Server Start Time];
GO

7 SQL Server Services and IFI

Shows service accounts, startup types, cluster node, and whether Instant File Initialization is enabled. IFI should be Y on all production instances — without it, every data file growth event stalls while zeros are written to disk.

SELECT
    servicename,
    process_id,
    startup_type_desc,
    status_desc,
    last_startup_time,
    service_account,
    is_clustered,
    cluster_nodename,
    [filename],
    instant_file_initialization_enabled   -- should be Y
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

8 Backup Status by Database

Shows the last full, differential, and log backup for every database alongside log usage percentage, recovery model, and the last successful CHECKDB date. This is the most important daily health check query.

SELECT
    ISNULL(d.[name], bs.[database_name])    AS [Database],
    d.recovery_model_desc                   AS [Recovery Model],
    d.log_reuse_wait_desc                   AS [Log Reuse Wait],
    CONVERT(DECIMAL(18,2), ds.cntr_value/1024.0) AS [Data File Size (MB)],
    CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log File Size (MB)],
    CAST(CAST(lu.cntr_value AS FLOAT)/CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2))*100
                                            AS [Log Used %],
    MAX(CASE WHEN bs.[type]='D' THEN bs.backup_finish_date  ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN bs.[type]='D' THEN CONVERT(BIGINT, bs.compressed_backup_size/1048576)
                                ELSE NULL END)                             AS [Full Compressed (MB)],
    MAX(CASE WHEN bs.[type]='D' THEN bs.compression_algorithm ELSE NULL END) AS [Compression Algo],
    MAX(CASE WHEN bs.[type]='I' THEN bs.backup_finish_date  ELSE NULL END) AS [Last Differential],
    MAX(CASE WHEN bs.[type]='L' THEN bs.backup_finish_date  ELSE NULL END) AS [Last Log Backup],
    MAX(CASE WHEN bs.[type]='L' THEN bs.last_valid_restore_time ELSE NULL END) AS [Last Valid Restore],
    DATABASEPROPERTYEX(d.[name], 'LastGoodCheckDbTime')                    AS [Last Good CheckDB]
FROM sys.databases AS d WITH (NOLOCK)
INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON d.database_id = mf.database_id
LEFT  JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
    ON bs.[database_name] = d.[name] AND bs.backup_finish_date > GETDATE()-30
LEFT  JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON d.name = lu.instance_name
LEFT  JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON d.name = ls.instance_name
INNER JOIN sys.dm_os_performance_counters AS ds WITH (NOLOCK) ON d.name = ds.instance_name
WHERE d.name <> N'tempdb'
AND lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ds.counter_name LIKE N'Data File(s) Size (KB)%'
AND ls.cntr_value > 0
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc,
         d.[name], CONVERT(DECIMAL(18,2), ds.cntr_value/1024.0),
         CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0),
         CAST(CAST(lu.cntr_value AS FLOAT)/CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2))*100
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);

9 I/O Latency: Drive and File

Two views of the same I/O data — drive-level rollup and individual file detail — both cumulative since the last SQL Server restart. For OLTP workloads, sustained average write latency above 20–30 ms on any file warrants investigation.

SELECT
    tab.[Drive],
    tab.volume_mount_point                  AS [Volume],
    CASE WHEN num_of_reads=0  THEN 0 ELSE io_stall_read_ms /num_of_reads  END AS [Read Latency (ms)],
    CASE WHEN num_of_writes=0 THEN 0 ELSE io_stall_write_ms/num_of_writes END AS [Write Latency (ms)],
    CASE WHEN (num_of_reads=0 AND num_of_writes=0) THEN 0
         ELSE io_stall/(num_of_reads+num_of_writes) END                        AS [Overall Latency (ms)],
    CASE WHEN num_of_reads=0  THEN 0 ELSE num_of_bytes_read   /num_of_reads  END AS [Avg Bytes/Read],
    CASE WHEN num_of_writes=0 THEN 0 ELSE num_of_bytes_written/num_of_writes END AS [Avg Bytes/Write]
FROM (
    SELECT LEFT(UPPER(mf.physical_name),2) AS Drive,
           SUM(num_of_reads)         num_of_reads,
           SUM(io_stall_read_ms)     io_stall_read_ms,
           SUM(num_of_writes)        num_of_writes,
           SUM(io_stall_write_ms)    io_stall_write_ms,
           SUM(num_of_bytes_read)    num_of_bytes_read,
           SUM(num_of_bytes_written) num_of_bytes_written,
           SUM(io_stall)             io_stall,
           vs.volume_mount_point
    FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
    INNER JOIN sys.master_files AS mf WITH (NOLOCK)
        ON vfs.database_id=mf.database_id AND vfs.file_id=mf.file_id
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs
    GROUP BY LEFT(UPPER(mf.physical_name),2), vs.volume_mount_point
) AS tab
ORDER BY [Overall Latency (ms)] DESC OPTION (RECOMPILE);

SELECT
    DB_NAME(fs.database_id)                                                   AS [Database],
    mf.[name]                                                                 AS [Logical Name],
    mf.[type_desc],
    CAST(fs.io_stall_read_ms /(1.0+fs.num_of_reads)  AS NUMERIC(10,1))       AS [Avg Read Latency (ms)],
    CAST(fs.io_stall_write_ms/(1.0+fs.num_of_writes) AS NUMERIC(10,1))       AS [Avg Write Latency (ms)],
    CAST((fs.io_stall_read_ms+fs.io_stall_write_ms)/
         (1.0+fs.num_of_reads+fs.num_of_writes)      AS NUMERIC(10,1))       AS [Avg Overall Latency (ms)],
    CONVERT(DECIMAL(18,2), mf.[size]/128.0)                                   AS [File Size (MB)],
    mf.physical_name,
    io_stall_queued_read_ms  AS [RG Read IO Latency (ms)],
    io_stall_queued_write_ms AS [RG Write IO Latency (ms)]
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
    ON fs.database_id=mf.database_id AND fs.[file_id]=mf.[file_id]
ORDER BY [Avg Overall Latency (ms)] DESC OPTION (RECOMPILE);

10 Wait Statistics

Top cumulative waits since the last restart or the last manual clear. This is your primary entry point for any performance investigation — the wait type distribution tells you whether the bottleneck is CPU, I/O, memory, locking, or network. The URL column links directly to the SQLskills.com wait type library.

WITH [Waits] AS (
    SELECT wait_type,
           wait_time_ms/1000.0                          AS [WaitS],
           (wait_time_ms-signal_wait_time_ms)/1000.0    AS [ResourceS],
           signal_wait_time_ms/1000.0                   AS [SignalS],
           waiting_tasks_count                          AS [WaitCount],
           100.0*wait_time_ms/SUM(wait_time_ms) OVER()  AS [Percentage],
           ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE [wait_type] NOT IN (
        N'AZURE_IMDS_VERSIONS',N'BROKER_EVENTHANDLER',N'BROKER_RECEIVE_WAITFOR',N'BROKER_TASK_STOP',
        N'BROKER_TO_FLUSH',N'BROKER_TRANSMITTER',N'CHECKPOINT_QUEUE',N'CHKPT',N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',N'CLR_SEMAPHORE',N'CXCONSUMER',N'DBMIRROR_DBM_EVENT',
        N'DBMIRROR_EVENTS_QUEUE',N'DBMIRROR_WORKER_QUEUE',N'DBMIRRORING_CMD',N'DIRTY_PAGE_POLL',
        N'DISPATCHER_QUEUE_SEMAPHORE',N'EXECSYNC',N'FSAGENT',N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'FT_IFTSHC_MUTEX',N'HADR_CLUSAPI_CALL',N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',N'HADR_NOTIFICATION_DEQUEUE',N'HADR_TIMER_TASK',N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',N'LAZYWRITER_SLEEP',N'LOGMGR_QUEUE',N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',N'PARALLEL_REDO_DRAIN_WORKER',N'PARALLEL_REDO_LOG_CACHE',
        N'PARALLEL_REDO_TRAN_LIST',N'PARALLEL_REDO_WORKER_SYNC',N'PARALLEL_REDO_WORKER_WAIT_WORK',
        N'PREEMPTIVE_HADR_LEASE_MECHANISM',N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
        N'PREEMPTIVE_OS_LIBRARYOPS',N'PREEMPTIVE_OS_COMOPS',N'PREEMPTIVE_OS_CRYPTOPS',
        N'PREEMPTIVE_OS_PIPEOPS',N'PREEMPTIVE_OS_AUTHENTICATIONOPS',N'PREEMPTIVE_OS_GENERICOPS',
        N'PREEMPTIVE_OS_VERIFYTRUST',N'PREEMPTIVE_OS_FILEOPS',N'PREEMPTIVE_OS_DEVICEOPS',
        N'PREEMPTIVE_OS_QUERYREGISTRY',N'PREEMPTIVE_OS_WRITEFILE',N'PREEMPTIVE_OS_WRITEFILEGATHER',
        N'PREEMPTIVE_XE_CALLBACKEXECUTE',N'PREEMPTIVE_XE_DISPATCHER',N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PREEMPTIVE_XE_SESSIONCOMMIT',N'PREEMPTIVE_XE_TARGETINIT',N'PREEMPTIVE_XE_TARGETFINALIZE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'RESOURCE_QUEUE',N'SERVER_IDLE_CHECK',N'SLEEP_BPOOL_FLUSH',N'SLEEP_DBSTARTUP',
        N'SLEEP_DCOMSTARTUP',N'SLEEP_MASTERDBREADY',N'SLEEP_MASTERMDREADY',N'SLEEP_MASTERUPGRADED',
        N'SLEEP_MSDBSTARTUP',N'SLEEP_SYSTEMTASK',N'SLEEP_TASK',N'SLEEP_TEMPDBSTARTUP',
        N'SNI_HTTP_ACCEPT',N'SOS_WORK_DISPATCHER',N'SP_SERVER_DIAGNOSTICS_SLEEP',
        N'SQLTRACE_BUFFER_FLUSH',N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'SQLTRACE_WAIT_ENTRIES',
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',N'WAITFOR',N'WAITFOR_TASKSHUTDOWN',
        N'XE_DISPATCHER_JOIN',N'XE_DISPATCHER_WAIT',N'XE_LIVE_TARGET_TVF',N'XE_TIMER_EVENT')
    AND waiting_tasks_count > 0
)
SELECT
    MAX(W1.wait_type)                                        AS [WaitType],
    CAST(MAX(W1.Percentage)  AS DECIMAL(5,2))               AS [Wait %],
    CAST(MAX(W1.WaitS)/MAX(W1.WaitCount) AS DECIMAL(16,4))  AS [AvgWait_Sec],
    CAST(MAX(W1.ResourceS)/MAX(W1.WaitCount) AS DECIMAL(16,4)) AS [AvgRes_Sec],
    CAST(MAX(W1.SignalS)/MAX(W1.WaitCount)   AS DECIMAL(16,4)) AS [AvgSig_Sec],
    CAST(MAX(W1.WaitS) AS DECIMAL(16,2))                    AS [Wait_Sec],
    MAX(W1.WaitCount)                                        AS [Wait Count],
    CAST(N'https://www.sqlskills.com/help/waits/'+W1.wait_type AS XML) AS [Help URL]
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type
HAVING SUM(W2.Percentage)-MAX(W1.Percentage) < 99
OPTION (RECOMPILE);

11 Top CPU and Logical Read Queries

Identifies the most expensive cached queries by CPU consumption and by logical reads. These are your highest-value tuning targets. The Has Missing Index column flags queries where the optimizer detected a missing index during compilation.

SELECT TOP(50)
    DB_NAME(t.[dbid])                                                              AS [Database],
    REPLACE(REPLACE(LEFT(t.[text],255),CHAR(10),''),CHAR(13),'')                  AS [Short Query Text],
    qs.total_worker_time                                                           AS [Total Worker Time],
    qs.total_worker_time/qs.execution_count                                        AS [Avg Worker Time],
    qs.max_worker_time                                                             AS [Max Worker Time],
    qs.total_elapsed_time/qs.execution_count                                       AS [Avg Elapsed Time],
    qs.max_elapsed_time                                                            AS [Max Elapsed Time],
    qs.total_logical_reads/qs.execution_count                                      AS [Avg Logical Reads],
    qs.execution_count                                                             AS [Execution Count],
    CASE WHEN CONVERT(nvarchar(max),qp.query_plan) COLLATE Latin1_General_BIN2
         LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END                            AS [Has Missing Index],
    qs.creation_time                                                               AS [Plan Cached]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

SELECT TOP(50)
    DB_NAME(t.[dbid])                                                              AS [Database],
    REPLACE(REPLACE(LEFT(t.[text],255),CHAR(10),''),CHAR(13),'')                  AS [Short Query Text],
    qs.total_logical_reads                                                         AS [Total Logical Reads],
    qs.total_logical_reads/qs.execution_count                                      AS [Avg Logical Reads],
    qs.max_logical_reads                                                           AS [Max Logical Reads],
    qs.total_worker_time/qs.execution_count                                        AS [Avg Worker Time],
    qs.total_elapsed_time/qs.execution_count                                       AS [Avg Elapsed Time],
    qs.execution_count                                                             AS [Execution Count],
    CASE WHEN CONVERT(nvarchar(max),qp.query_plan) COLLATE Latin1_General_BIN2
         LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END                            AS [Has Missing Index],
    qs.creation_time                                                               AS [Plan Cached]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC OPTION (RECOMPILE);

12 Missing Indexes — All Databases

Instance-wide missing index recommendations sorted by Index Advantage — the optimizer's estimate of cumulative improvement if the index existed. Use as a starting list only. The optimizer overestimates included columns and generates too many suggestions. Always review before creating.

SELECT
    CONVERT(decimal(18,2), migs.user_seeks*migs.avg_total_user_cost*(migs.avg_user_impact*0.01))
                                                                  AS [Index Advantage],
    CONVERT(nvarchar(25), migs.last_user_seek, 20)                AS [Last User Seek],
    mid.[statement]                                               AS [Database.Schema.Table],
    COUNT(1) OVER(PARTITION BY mid.[statement])                   AS [Missing Indexes for Table],
    COUNT(1) OVER(PARTITION BY mid.[statement], mid.equality_columns)
                                                                  AS [Similar Missing Indexes],
    mid.equality_columns                                          AS [Equality Columns],
    mid.inequality_columns                                        AS [Inequality Columns],
    mid.included_columns                                          AS [Included Columns],
    migs.user_seeks                                               AS [User Seeks],
    CONVERT(decimal(18,2), migs.avg_total_user_cost)              AS [Avg Total User Cost],
    CONVERT(decimal(18,2), migs.avg_user_impact)                  AS [Avg User Impact %],
    REPLACE(REPLACE(LEFT(st.[text],500),CHAR(10),''),CHAR(13),'') AS [Short Query Text]
FROM sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_group_stats_query AS migs WITH (NOLOCK)
    ON mig.index_group_handle = migs.group_handle
CROSS APPLY sys.dm_exec_sql_text(migs.last_sql_handle) AS st
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
ORDER BY [Index Advantage] DESC OPTION (RECOMPILE);

13 Suspect Pages and I/O Warnings

Any rows in the suspect pages table require immediate investigation — this means SQL Server detected page-level corruption. The I/O warnings query scans the error log for requests that took longer than 15 seconds.

-- Ideally returns zero rows. Table is capped at 1000 rows.
SELECT
    DB_NAME(sp.database_id)  AS [Database Name],
    sp.[file_id], sp.page_id,
    sp.event_type,   -- 1=OS CRC/824 error, 2=bad checksum, 3=torn page, 4=restored, 5=DBCC repaired
    sp.error_count,
    sp.last_update_date,
    mf.[name]                AS [Logical Name],
    mf.physical_name         AS [File Path]
FROM msdb.dbo.suspect_pages AS sp WITH (NOLOCK)
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
    ON mf.database_id=sp.database_id AND mf.file_id=sp.file_id
ORDER BY sp.database_id OPTION (RECOMPILE);

DROP TABLE IF EXISTS #IOWarningResults;
CREATE TABLE #IOWarningResults (LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));

INSERT INTO #IOWarningResults EXEC xp_readerrorlog 0,1,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults EXEC xp_readerrorlog 1,1,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults EXEC xp_readerrorlog 2,1,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults EXEC xp_readerrorlog 3,1,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults EXEC xp_readerrorlog 4,1,N'taking longer than 15 seconds';
INSERT INTO #IOWarningResults EXEC xp_readerrorlog 5,1,N'taking longer than 15 seconds';

SELECT LogDate, ProcessInfo, LogText FROM #IOWarningResults ORDER BY LogDate DESC;
DROP TABLE IF EXISTS #IOWarningResults;
------
-- Also check for Long Sync IO warnings:
EXEC sys.xp_readerrorlog 0, 1, N'Long Sync IO';
EXEC sys.xp_readerrorlog 1, 1, N'Long Sync IO';

-- No rows = no memory dumps (good)
SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [Size (MB)]
FROM sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY creation_time DESC OPTION (RECOMPILE);

14 SQLYARD: Max Memory Recommendation

Calculates the recommended SQL Server max server memory setting using a tiered formula that reserves appropriate headroom for the OS. Leaving max server memory at the default of 2,147,483,647 MB will cause the OS to compete with SQL Server for memory — on systems under load this leads to paging, instability, and poor performance.

OS Reserve Formula:

• Total RAM ≤ 16 GB → Reserve 4 GB for OS
• Total RAM ≤ 64 GB → Reserve 6 GB + 1 GB for every 8 GB over 16 GB
• Total RAM > 64 GB → Reserve 8 GB + 1 GB for every 10 GB over 64 GB

IF OBJECT_ID('tempdb..#mem') IS NOT NULL DROP TABLE #mem;

DECLARE
    @memInMachine  DECIMAL(10,2),
    @memOsTot      DECIMAL(10,2),
    @memForSql     DECIMAL(10,2),
    @currentMem    DECIMAL(10,2),
    @currentOS     DECIMAL(10,2),
    @sql           NVARCHAR(MAX);

CREATE TABLE #mem (mem DECIMAL(10,2));

-- Get configured max server memory (GB)
SELECT @currentMem = CAST(value_in_use AS INT)/1024.0
FROM sys.configurations
WHERE name = 'max server memory (MB)';

-- Get total physical RAM (compatible SQL 2012+)
SET @sql = N'SELECT total_physical_memory_kb/1024.0/1024.0 FROM sys.dm_os_sys_memory';
INSERT INTO #mem(mem) EXEC (@sql);
SELECT @memInMachine = MAX(mem) FROM #mem;

-- Tiered OS reserve formula
SET @memOsTot = ROUND(
    CASE
        WHEN @memInMachine <= 16 THEN 4
        WHEN @memInMachine <= 64 THEN 6 + ((@memInMachine - 16) / 8.0)
        ELSE                          8 + ((@memInMachine - 64) / 10.0)
    END, 2);

SET @currentOS = ROUND(@memInMachine - @currentMem, 2);
SET @memForSql = ROUND(@memInMachine - @memOsTot,  2);

SELECT
    @currentMem     AS CurrentSQLMaxMemory_GB,
    @memInMachine   AS TotalMachineMemory_GB,
    @currentOS      AS CurrentOSMemory_GB,
    @memOsTot       AS RecommendedOSMemory_GB,
    @memForSql      AS RecommendedSQLMaxMemory_GB,
    CASE
        WHEN @currentMem BETWEEN @memForSql-1 AND @memForSql+1
             THEN N'Config looks good'
        WHEN @currentMem > @memForSql
             THEN N'SQL Server is using too much memory — consider lowering max server memory'
        ELSE N'SQL Server max memory is below recommended — could be increased'
    END AS Recommendation,
    'EXEC sp_configure ''show advanced options'', 1; RECONFIGURE; ' +
    'EXEC sp_configure ''max server memory (MB)'', ' +
    CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10)) +
    '; RECONFIGURE;' AS SuggestedCommand;

DROP TABLE IF EXISTS #mem;

15 SQLYARD: Missing Index Impact List with CREATE Statements

Generates ready-to-run CREATE NONCLUSTERED INDEX statements for the top 500 missing indexes ranked by cumulative impact factor. Auto-names each index from the table and column names. Review every suggested index carefully before creating — the optimizer is especially prone to suggesting redundant included columns, and creating an index without understanding the full workload can hurt write performance.

-- Run in the context of the database you want to analyze (USE YourDatabase;)
SELECT
    CAST((migs.avg_total_user_cost * migs.avg_user_impact) *
         (migs.user_seeks + migs.user_scans) AS DECIMAL(20,3))      AS [ImpactFactor],
    mid.[statement]                                                  AS [Table],
    N'CREATE NONCLUSTERED INDEX ix_' +
        sys.objects.name COLLATE DATABASE_DEFAULT + '_' +
        REPLACE(REPLACE(REPLACE(
            ISNULL(mid.equality_columns,'') + ISNULL(mid.inequality_columns,''),
            '[',''),']',''),', ','_') +
        ' ON ' + mid.[statement] + ' ( ' +
        ISNULL(mid.equality_columns,'') +
        CASE WHEN mid.inequality_columns IS NULL THEN ''
             ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END
                  + mid.inequality_columns END + ' ) ' +
        CASE WHEN mid.included_columns IS NULL THEN ''
             ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS [CreateIndexStatement],
    migs.avg_user_impact        AS [UserImprovement%],
    migs.avg_system_impact      AS [SystemImprovement%],
    migs.user_seeks             AS [UserSeeks],
    migs.user_scans             AS [UserScans],
    mid.equality_columns        AS [EqualityColumns],
    mid.inequality_columns      AS [InequalityColumns],
    mid.included_columns        AS [IncludedColumns]
FROM sys.dm_db_missing_index_group_stats   migs
INNER JOIN sys.dm_db_missing_index_groups  mig  ON migs.group_handle  = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid  ON mig.index_handle   = mid.index_handle
INNER JOIN sys.objects WITH (NOLOCK)            ON mid.OBJECT_ID       = sys.objects.OBJECT_ID
WHERE migs.group_handle IN (
    SELECT TOP(500) group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (NOLOCK)
    ORDER BY (avg_total_user_cost * avg_user_impact)*(user_seeks+user_scans) DESC)
AND OBJECTPROPERTY(sys.objects.OBJECT_ID,'isusertable') = 1
ORDER BY [ImpactFactor] DESC;

16 SQLYARD: TempDB Spill Detection

Finds queries whose execution plans spilled to TempDB — sort spills and hash join spills are the two most common. These cause heavy sequential I/O against TempDB and are almost always fixable through memory grant hints, better index coverage, or statistics updates. Query Store is required for the primary detection method; the wait stats fallback is always available.

-- Requires Query Store enabled on the target database
-- Query Store is ON by default in SQL Server 2022
SELECT TOP(25)
    qsqt.query_sql_text,
    qsp.last_execution_time,
    qsrs.avg_duration           AS [Avg Duration (us)],
    qsrs.avg_cpu_time           AS [Avg CPU (us)],
    qsrs.avg_logical_io_reads   AS [Avg Logical Reads],
    qsrs.count_executions       AS [Executions],
    TRY_CONVERT(XML, qsp.query_plan).value(
        '(//SpillToTempDb/@SpillLevel)[1]','INT') AS [Spill Level]
FROM sys.query_store_plan              qsp
JOIN sys.query_store_query             qsq  ON qsq.query_id      = qsp.query_id
JOIN sys.query_store_query_text        qsqt ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats     qsrs ON qsrs.plan_id       = qsp.plan_id
WHERE TRY_CONVERT(XML, qsp.query_plan).exist('//SpillToTempDb') = 1
ORDER BY qsrs.avg_cpu_time DESC;

-- Works on all versions regardless of Query Store status
-- Shows cumulative spill waits since last restart or wait stats clear
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count,0) AS [Avg Wait (ms)]
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('SORT_SPILL','HASH_SPILL')
ORDER BY wait_time_ms DESC;

-- Also check TempDB version store pressure:
SELECT
    DB_NAME(database_id)    AS [Database],
    reserved_page_count     AS [Version Store Pages],
    reserved_space_kb/1024  AS [Version Store (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY reserved_space_kb DESC OPTION (RECOMPILE);

17 SQLYARD: Heap Tables Needing a Clustered Index

Heaps — tables without a clustered index — cause forwarded record overhead when rows expand past the original page, require full table scans for most queries, and produce inefficient RID lookups from nonclustered indexes. Any heap above 1,000 rows with significant forwarded fetches or range scan activity is a candidate for a clustered index.

-- Run in the context of the user database you want to analyze
SELECT
    SCHEMA_NAME(o.schema_id)                                          AS [Schema],
    o.[name]                                                          AS [Table Name],
    p.[rows]                                                          AS [Row Count],
    SUM(ios.forwarded_fetch_count)                                    AS [Forwarded Fetches],
    SUM(ios.range_scan_count)                                         AS [Range Scans],
    SUM(ios.page_lock_wait_count)                                     AS [Page Lock Waits],
    SUM(ios.row_lock_wait_count)                                      AS [Row Lock Waits],
    CAST(SUM(ps.used_page_count)*8.0/1024 AS DECIMAL(10,2))           AS [Used Space (MB)]
FROM sys.indexes                                                       i
JOIN sys.objects                                                        o   ON o.object_id  = i.object_id
JOIN sys.partitions                                                     p   ON p.object_id  = i.object_id
JOIN sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)         ios ON ios.object_id = i.object_id
                                                                           AND ios.index_id  = i.index_id
JOIN sys.dm_db_partition_stats                                          ps  ON ps.object_id  = i.object_id
                                                                           AND ps.index_id   = i.index_id
WHERE i.index_id = 0          -- heap only
AND   o.[type]   = 'U'        -- user tables
AND   o.[name] NOT LIKE 'sys%'
GROUP BY SCHEMA_NAME(o.schema_id), o.[name], p.[rows]
HAVING p.[rows] > 1000
    OR SUM(ios.forwarded_fetch_count) > 0
ORDER BY [Forwarded Fetches] DESC, [Row Count] DESC;

18 SQLYARD: Partitioning Candidates (Large Tables)

Identifies large tables that may benefit from partitioning based on reserved space, row count, and scan activity. Partitioning is not a performance fix by itself — it becomes valuable when combined with a partition elimination strategy in queries. Tables above 10 GB or 10 million rows with high range scan counts are the starting candidates to discuss with the application team.

-- Run in the context of the user database you want to analyze
SELECT
    SCHEMA_NAME(o.schema_id)                                                 AS [Schema],
    o.[name]                                                                 AS [Table Name],
    i.[name]                                                                 AS [Index Name],
    i.index_id,
    CAST(SUM(ps.reserved_page_count)*8.0/1024/1024 AS DECIMAL(10,2))        AS [Reserved GB],
    SUM(p.[rows])                                                            AS [Row Count],
    SUM(ios.range_scan_count)                                                AS [Range Scans],
    SUM(ios.leaf_allocation_count)                                           AS [Page Splits],
    SUM(ios.page_lock_wait_count)                                            AS [Page Lock Waits],
    p.data_compression_desc                                                  AS [Compression]
FROM sys.indexes                                                              i
JOIN sys.objects                                                               o   ON o.object_id = i.object_id
JOIN sys.partitions                                                            p   ON p.object_id = i.object_id
                                                                                AND p.index_id   = i.index_id
JOIN sys.dm_db_partition_stats                                                 ps  ON ps.object_id= i.object_id
                                                                                AND ps.index_id  = i.index_id
JOIN sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL)                ios ON ios.object_id= i.object_id
                                                                                AND ios.index_id  = i.index_id
WHERE i.index_id IN (0,1)     -- heap or clustered index
AND   o.[type] = 'U'
GROUP BY SCHEMA_NAME(o.schema_id), o.[name], i.[name], i.index_id, p.data_compression_desc
HAVING CAST(SUM(ps.reserved_page_count)*8.0/1024/1024 AS DECIMAL(10,2)) > 1
    OR SUM(p.[rows]) > 1000000
ORDER BY [Reserved GB] DESC, [Row Count] DESC;

19 SQLYARD: Space Usage, VLF Count, and Drive Free Space

-- Run in the context of the user database you want to analyze
SELECT
    f.[name]                                                                  AS [File Name],
    f.physical_name                                                           AS [Physical Path],
    CAST(f.size/128.0 AS DECIMAL(15,2))                                       AS [Total Size (MB)],
    CAST(f.size/128.0 AS DECIMAL(15,2)) -
    CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name,'SpaceUsed') AS INT)/128.0
         AS DECIMAL(15,2))                                                    AS [Used (MB)],
    CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name,'SpaceUsed') AS INT)/128.0
         AS DECIMAL(15,2))                                                    AS [Available (MB)],
    f.[file_id],
    fg.[name]                                                                 AS [Filegroup],
    f.is_percent_growth,   -- 1 = BAD (percentage autogrowth causes VLF explosion)
    CASE f.is_percent_growth
        WHEN 1 THEN CAST(f.growth AS VARCHAR(10)) + '%  *** CHANGE TO MB ***'
        ELSE CAST(f.growth/128 AS VARCHAR(10)) + ' MB'
    END                                                                       AS [Growth Setting],
    fg.is_autogrow_all_files
FROM sys.database_files         AS f  WITH (NOLOCK)
LEFT JOIN sys.filegroups         AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
ORDER BY f.[type], f.[file_id] OPTION (RECOMPILE);

-- High VLF counts slow log backups, restores, and crash recovery
-- Target: under 200 VLFs for most databases
SELECT
    db.[name]        AS [Database Name],
    li.[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
    SELECT file_id, COUNT(*) AS [VLF Count]
    FROM sys.dm_db_log_info(db.database_id)
    GROUP BY file_id
) AS li
ORDER BY li.[VLF Count] DESC OPTION (RECOMPILE);
------
-- VLF growth formula for SQL Server 2022:
-- Growth increment < 1/8 current log size  → 1 new VLF
-- Growth up to 64 MB                       → 1 new VLF
-- Growth 64 MB to 1 GB                     → 8 new VLFs
-- Growth more than 1 GB                    → 16 new VLFs
-- Fix: pre-size the log file and set a fixed MB autogrowth value

SELECT DISTINCT
    vs.volume_mount_point,
    vs.file_system_type,
    vs.logical_volume_name,
    CONVERT(DECIMAL(18,2), vs.total_bytes    /1073741824.0) AS [Total (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes*1./vs.total_bytes*100.) AS [Free %],
    vs.supports_compression,
    vs.is_compressed
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

20 SQLYARD: Overlapping and Write-Heavy Index Analysis

Two complementary index quality checks: nonclustered indexes where write traffic significantly exceeds read traffic (candidates for removal), and an overlap detection query that identifies indexes whose key columns are a subset of another index on the same table.

-- Run in the context of the user database you want to analyze
-- Indexes that cost more to maintain than they return in read benefit
SELECT
    SCHEMA_NAME(o.[schema_id])                               AS [Schema],
    OBJECT_NAME(s.[object_id])                               AS [Table Name],
    i.name                                                   AS [Index Name],
    i.index_id,
    i.is_disabled, i.fill_factor,
    s.user_updates                                           AS [Total Writes],
    s.user_seeks + s.user_scans + s.user_lookups             AS [Total Reads],
    s.user_updates-(s.user_seeks+s.user_scans+s.user_lookups) AS [Write/Read Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes  AS i WITH (NOLOCK) ON s.[object_id]=i.[object_id] AND i.index_id=s.index_id
INNER JOIN sys.objects  AS o WITH (NOLOCK) ON i.[object_id]=o.[object_id]
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_updates > (s.user_seeks+s.user_scans+s.user_lookups)
AND i.index_id > 1
AND i.[type_desc]      = N'NONCLUSTERED'
AND i.is_primary_key   = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0
ORDER BY [Write/Read Difference] DESC OPTION (RECOMPILE);
------
-- Investigate further before dropping — consider full workload history
-- Index usage stats reset on every SQL Server restart

-- Identifies indexes whose key columns are a leading subset of another index on the same table
-- These are candidates for consolidation — the narrower index is usually redundant
-- Requires SQL Server 2017+ for STRING_AGG
SELECT
    SCHEMA_NAME(t.schema_id)    AS [Schema],
    t.[name]                    AS [Table Name],
    i1.[name]                   AS [Index A (narrower)],
    i2.[name]                   AS [Index B (wider — may subsume A)],
    STRING_AGG(c1.[name], ', ')
        WITHIN GROUP (ORDER BY ic1.key_ordinal)   AS [Index A Columns],
    i1.is_disabled              AS [A Disabled],
    s1.user_seeks + s1.user_scans + s1.user_lookups AS [A Total Reads],
    s1.user_updates             AS [A Total Writes]
FROM sys.indexes               i1
JOIN sys.indexes               i2  ON i1.object_id = i2.object_id AND i1.index_id <> i2.index_id
JOIN sys.index_columns         ic1 ON ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id
                                   AND ic1.is_included_column = 0
JOIN sys.index_columns         ic2 ON ic2.object_id = i2.object_id AND ic2.index_id = i2.index_id
                                   AND ic2.column_id = ic1.column_id
                                   AND ic2.key_ordinal = ic1.key_ordinal
                                   AND ic2.is_included_column = 0
JOIN sys.columns               c1  ON c1.object_id = i1.object_id AND c1.column_id = ic1.column_id
JOIN sys.tables                t   ON t.object_id = i1.object_id
LEFT JOIN sys.dm_db_index_usage_stats s1
    ON s1.object_id = i1.object_id AND s1.index_id = i1.index_id AND s1.database_id = DB_ID()
WHERE i1.index_id > 1 AND i2.index_id > 1
AND i1.is_primary_key = 0 AND i2.is_primary_key = 0
AND i1.[type_desc] = N'NONCLUSTERED' AND i2.[type_desc] = N'NONCLUSTERED'
GROUP BY SCHEMA_NAME(t.schema_id), t.[name], i1.[name], i2.[name],
         i1.is_disabled, s1.user_seeks, s1.user_scans, s1.user_lookups, s1.user_updates
HAVING COUNT(*) = (
    SELECT COUNT(*) FROM sys.index_columns
    WHERE object_id = i1.object_id AND index_id = i1.index_id AND is_included_column = 0)
ORDER BY [Table Name], [Index A (narrower)];

21 Trace Flag Baseline Checklist for SQL Server 2022

The table below reflects the current status of commonly discussed trace flags on SQL Server 2022. Several flags that were valid recommendations for SQL Server 2012–2014 are now obsolete, default engine behavior, or actively harmful if enabled on a 2022 instance.

-- Check currently active global trace flags
DBCC TRACESTATUS(-1);

-- Recommended startup parameters for SQL Server 2022
-- Add to SQL Server Configuration Manager → SQL Server service → Startup Parameters:
-- -T3226   suppress backup success messages
-- -T4199   optimizer CU hotfixes (test first)
-- -T7745   async Query Store shutdown
-- -T7752   async Query Store load
-- If LPIM active, RAM > 48GB, Enterprise, NO columnstore:
-- -T834    large memory pages
-- If columnstore IS in use (SQL 2019+):
-- -T876    large pages for columnstore instead

-- Enable at runtime (does NOT persist through restart):
DBCC TRACEON(3226, -1);
DBCC TRACEON(7745, -1);
DBCC TRACEON(7752, -1);
DBCC TRACEON(4199, -1);   -- run after testing query plan changes

References

• Glenn Berry — SQL Server Diagnostic Information Queries (June 2024 baseline post)
• Glenn Berry — Diagnostic Information Queries Download (always use version-matched file)
• Microsoft Docs — SQL Server 2022 Build Versions
• Microsoft Docs — Trace Flags (Transact-SQL)
• Microsoft SQL Server Team — Let's Talk About Trace Flags
• Microsoft Docs — Query Store Overview
• Microsoft Docs — sys.dm_os_wait_stats
• Microsoft Docs — sys.dm_io_virtual_file_stats
• SQLskills.com — SQL Server Wait Types Library (Paul Randal)
• SQLYARD — SQL Server Trace Flags: Comprehensive Guide (with Version and AG Flags)
• Microsoft — Antivirus exclusions for SQL Server
• Ola Hallengren — SQL Server Maintenance Solution (backup, integrity, index jobs)