SQL Server Instance Health Check

Purpose

Provides a point-in-time health snapshot of a SQL Server instance, covering:

1. Top wait types
2. Active blocking chains
3. Database file space
4. Backup currency
5. Missing index recommendations

Supported Platforms and Versions

SQL Server 2016, 2017, 2019, 2022 (on-premises and SQL Server on Azure VMs).

Not compatible with Azure SQL Database or Azure SQL Managed Instance without modification — some DMVs differ.

Required Permissions

• VIEW SERVER STATE — for wait statistics, session data, and instance DMVs
• VIEW DATABASE STATE — on each database for file space queries

Assign with:

GRANT VIEW SERVER STATE TO [monitoring_user];

Preconditions

• Script runs entirely in a read context; no changes are made.
• Wait statistics are cumulative since last reset. If statistics were recently cleared, results reflect only the period since the reset.

Risk Level

Low. Read-only queries. No data modification, schema change, or configuration change.

Expected Output

Five result sets:

1. Top 15 wait types (excluding benign system waits)
2. Active blocking chains with wait duration and statement text
3. Database file space (size, used, growth settings)
4. Last backup per database (full, differential, log)
5. Top 10 missing index recommendations by estimated improvement

Script

-- ============================================================
-- SQL Server Instance Health Check
-- Permissions: VIEW SERVER STATE, VIEW DATABASE STATE
-- Risk: Read-only
-- ============================================================

-- 1. Top wait types
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER(), 0) AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_WORK_QUEUE','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'HADR_CLUSAPI_CALL','HADR_TRANSPORT_DUMPLIST',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
    'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH','WAITFOR',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','XE_DISPATCHER_WAIT','XE_TIMER_EVENT',
    'SLEEP_TEMPDBSTARTUP','DIRTY_PAGE_POLL','HADR_SYNC_COMMIT'
)
ORDER BY wait_time_ms DESC;

-- 2. Active blocking chains
SELECT
    blocking.session_id            AS blocker_session_id,
    blocked.session_id             AS blocked_session_id,
    CAST(blocked.wait_time / 1000.0 AS DECIMAL(10,1)) AS wait_seconds,
    blocked.wait_type,
    blocked_req.command,
    SUBSTRING(t.text, 1, 300)      AS blocked_statement_snippet
FROM sys.dm_exec_sessions AS blocked
JOIN sys.dm_exec_requests AS blocked_req
    ON blocked.session_id = blocked_req.session_id
JOIN sys.dm_exec_sessions AS blocking
    ON blocked_req.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked_req.sql_handle) AS t
WHERE blocked_req.blocking_session_id > 0
ORDER BY blocked.wait_time DESC;

-- 3. Database file space
SELECT
    DB_NAME(mf.database_id)                                      AS database_name,
    mf.type_desc,
    mf.name                                                      AS logical_name,
    mf.physical_name,
    CAST(mf.size * 8.0 / 1024 AS DECIMAL(10,1))                 AS size_mb,
    CAST(FILEPROPERTY(mf.name, 'SpaceUsed') * 8.0 / 1024
         AS DECIMAL(10,1))                                       AS used_mb,
    mf.growth,
    mf.is_percent_growth
FROM sys.master_files AS mf
WHERE mf.database_id > 4
ORDER BY mf.database_id, mf.type_desc;

-- 4. Last backup by database
SELECT
    d.name                                                        AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END)    AS last_full_backup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END)    AS last_differential,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END)    AS last_log_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
    ON d.name = b.database_name
    AND b.backup_finish_date > DATEADD(day, -30, GETDATE())
WHERE d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

-- 5. Missing index recommendations
SELECT TOP 10
    ROUND(
        migs.avg_total_user_cost * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans), 0
    )                              AS improvement_measure,
    mid.statement                  AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups      AS mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details     AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Safe Execution Guidance

Run in SQL Server Management Studio or sqlcmd. The script is safe to run during business hours. On high-throughput instances, run during low-activity periods for the most representative wait statistics picture.

Rollback Steps

Not applicable — read-only script.