Onsys 20-Point SQL Server Health Check — Data Collection Scripts

This bundle collects the evidence behind all twenty checkpoints. You can run it yourself and send us the output, or we can run it together on a screen share — whichever your change process allows.

Read every script before you run it. You should never run a script against your production database because a supplier sent it to you. Everything here is SELECT against system views and DMVs, plus read-only PowerShell — nothing writes, nothing changes configuration, and nothing reads your business data. Check that for yourself rather than taking our word for it.

What you need

SQL Server: a login with VIEW SERVER STATE and VIEW ANY DEFINITION. Membership of sysadmin is not required for most checks; where it is, the script says so.
Windows: a local session on the database host for the PowerShell in checks 1–2. Skip them if you cannot, and we will work without host data.
Output: run in SSMS or Azure Data Studio with Results to Grid, then right-click → Save Results As… to CSV, or Results to Text and copy.

The twenty checkpoints

Host and operating system PowerShell

OS version and build, CPU count and model, physical RAM, uptime and power plan. Run in an elevated PowerShell session on the database host.

# --- Operating system -------------------------------------------------
Get-CimInstance Win32_OperatingSystem |
  Select-Object Caption, Version, BuildNumber, OSArchitecture, InstallDate, LastBootUpTime,
    @{n='TotalRAM_GB'; e={[math]::Round($_.TotalVisibleMemorySize/1MB,1)}},
    @{n='FreeRAM_GB';  e={[math]::Round($_.FreePhysicalMemory/1MB,1)}} |
  Format-List

# --- Machine and processors -------------------------------------------
Get-CimInstance Win32_ComputerSystem |
  Select-Object Manufacturer, Model, Domain, NumberOfProcessors, NumberOfLogicalProcessors,
    @{n='PhysicalRAM_GB'; e={[math]::Round($_.TotalPhysicalMemory/1GB,1)}} |
  Format-List

Get-CimInstance Win32_Processor |
  Select-Object Name, NumberOfCores, NumberOfLogicalProcessors, MaxClockSpeed, L3CacheSize |
  Format-Table -AutoSize

# Power plan. "Balanced" throttles CPU frequency and is a common, invisible
# cause of poor SQL Server performance on physical hosts.
powercfg /getactivescheme

# Is the SQL Server service account granted Lock Pages in Memory and
# Perform Volume Maintenance Tasks? Both affect memory and file growth.
whoami /priv | Select-String 'SeManageVolume|SeLockMemory'

Disk capacity and layout PowerShell

Free space per volume and the NTFS allocation unit size. SQL Server data volumes should be formatted at 64 KB; the default 4 KB costs measurable throughput.

# --- Volumes: capacity, free space and allocation unit ----------------
Get-CimInstance Win32_Volume -Filter "DriveType=3" |
  Select-Object Name, Label, FileSystem,
    @{n='AllocUnit_KB'; e={$_.BlockSize/1KB}},
    @{n='Capacity_GB';  e={[math]::Round($_.Capacity/1GB,1)}},
    @{n='Free_GB';      e={[math]::Round($_.FreeSpace/1GB,1)}},
    @{n='Free_Pct';     e={if($_.Capacity){[math]::Round(($_.FreeSpace/$_.Capacity)*100,1)}}} |
  Sort-Object Name | Format-Table -AutoSize

# --- Physical disks and media type (SSD vs HDD) -----------------------
Get-PhysicalDisk |
  Select-Object DeviceId, FriendlyName, MediaType, BusType, HealthStatus,
    @{n='Size_GB'; e={[math]::Round($_.Size/1GB,1)}} |
  Format-Table -AutoSize

Version, edition and patch level T-SQL

Build number, edition and update level. This is what we check against the Microsoft lifecycle to tell you whether the instance is supported and current.

SELECT
    SERVERPROPERTY('MachineName')           AS machine_name,
    SERVERPROPERTY('ServerName')            AS server_name,
    SERVERPROPERTY('InstanceName')          AS instance_name,
    SERVERPROPERTY('ProductVersion')        AS product_version,
    SERVERPROPERTY('ProductLevel')          AS product_level,
    SERVERPROPERTY('ProductUpdateLevel')    AS cumulative_update,   -- 2012 SP3+ / 2014 SP2+
    SERVERPROPERTY('Edition')               AS edition,
    SERVERPROPERTY('EngineEdition')         AS engine_edition,
    SERVERPROPERTY('Collation')             AS server_collation,
    SERVERPROPERTY('IsClustered')           AS is_clustered,
    SERVERPROPERTY('IsHadrEnabled')         AS is_hadr_enabled,
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only,
    @@VERSION                               AS full_version_string;

Instance configuration T-SQL

Max server memory, MAXDOP and cost threshold against the CPU and RAM actually present. Left at defaults, SQL Server contends with the OS for memory and parallelises trivial queries.

-- Key settings, with defaults shown for comparison
SELECT name, value_in_use, [description]
FROM   sys.configurations
WHERE  name IN (
        'max server memory (MB)',        -- default 2147483647 = uncapped
        'min server memory (MB)',
        'max degree of parallelism',     -- default 0 = unlimited
        'cost threshold for parallelism',-- default 5, almost always too low
        'optimize for ad hoc workloads',
        'backup compression default',
        'remote admin connections',
        'fill factor (%)')
ORDER BY name;

-- What the server actually has to work with
SELECT  cpu_count,
        hyperthread_ratio,
        cpu_count / NULLIF(hyperthread_ratio,0) AS physical_sockets,
        physical_memory_kb / 1024 AS physical_memory_mb,   -- 2012+; 2008 R2: physical_memory_in_bytes
        committed_kb       / 1024 AS committed_mb,
        committed_target_kb/ 1024 AS committed_target_mb,
        sqlserver_start_time
FROM sys.dm_os_sys_info;

-- NUMA layout. MAXDOP guidance depends on cores per NUMA node, not total cores.
SELECT parent_node_id AS numa_node, COUNT(*) AS visible_schedulers
FROM   sys.dm_os_schedulers
WHERE  status = 'VISIBLE ONLINE' AND parent_node_id < 64
GROUP BY parent_node_id
ORDER BY parent_node_id;

tempdb configuration T-SQL

File count, equal sizing and autogrowth. Unequal tempdb files cause allocation contention that looks like a query problem and is not.

SELECT  mf.file_id,
        mf.name,
        mf.type_desc,
        CAST(mf.size AS BIGINT) * 8 / 1024       AS size_mb,
        CASE WHEN mf.is_percent_growth = 1
             THEN CONCAT(mf.growth, ' %')
             ELSE CONCAT(CAST(mf.growth AS BIGINT) * 8 / 1024, ' MB') END AS growth,
        CASE mf.max_size WHEN -1 THEN 'Unlimited'
                         WHEN 0  THEN 'No growth'
                         ELSE CONCAT(CAST(mf.max_size AS BIGINT) * 8 / 1024, ' MB') END AS max_size,
        mf.physical_name
FROM    sys.master_files mf
WHERE   mf.database_id = DB_ID('tempdb')
ORDER BY mf.type_desc, mf.file_id;

-- Current tempdb space use by category
SELECT  SUM(user_object_reserved_page_count)     * 8 / 1024 AS user_objects_mb,
        SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
        SUM(version_store_reserved_page_count)   * 8 / 1024 AS version_store_mb,
        SUM(unallocated_extent_page_count)       * 8 / 1024 AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;

Database inventory and options T-SQL

Every database with its size, recovery model, compatibility level and the options that quietly cause problems — auto-close, auto-shrink and page verify.

SELECT  d.name,
        d.database_id,
        d.state_desc,
        d.recovery_model_desc,
        d.compatibility_level,
        d.page_verify_option_desc,           -- anything but CHECKSUM is a finding
        d.is_auto_close_on,                  -- should be 0
        d.is_auto_shrink_on,                 -- should be 0
        d.is_auto_create_stats_on,
        d.is_auto_update_stats_on,
        d.is_read_committed_snapshot_on,
        d.collation_name,
        d.create_date,
        CAST(SUM(mf.size) * 8.0 / 1024 AS DECIMAL(18,1)) AS total_size_mb
FROM    sys.databases d
JOIN    sys.master_files mf ON mf.database_id = d.database_id
GROUP BY d.name, d.database_id, d.state_desc, d.recovery_model_desc, d.compatibility_level,
         d.page_verify_option_desc, d.is_auto_close_on, d.is_auto_shrink_on,
         d.is_auto_create_stats_on, d.is_auto_update_stats_on,
         d.is_read_committed_snapshot_on, d.collation_name, d.create_date
ORDER BY total_size_mb DESC;

Backup coverage T-SQL

Last full, differential and log backup per database. Databases with no backup at all are the single most common finding in this health check.

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,
        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,
        DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE())
                                                   AS hours_since_full,
        DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE())
                                                   AS minutes_since_log
FROM    sys.databases d
LEFT JOIN msdb.dbo.backupset b
       ON b.database_name = d.name
      AND b.is_copy_only = 0          -- copy-only backups do not affect the chain
WHERE   d.database_id <> DB_ID('tempdb')
  AND   d.source_database_id IS NULL  -- exclude snapshots
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full ASC;

Restore history T-SQL

Restores actually performed on this instance. A backup nobody has ever restored is a hypothesis rather than a recovery plan — this is the evidence, not an assumption.

SELECT TOP (100)
        rh.destination_database_name,
        rh.restore_date,
        rh.restore_type,          -- D = database, F = file, I = differential, L = log, V = verifyonly
        rh.[user_name],
        bs.backup_finish_date     AS source_backup_taken,
        bs.database_name          AS source_database,
        bmf.physical_device_name  AS restored_from
FROM    msdb.dbo.restorehistory rh
LEFT JOIN msdb.dbo.backupset bs        ON bs.backup_set_id = rh.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_date DESC;

-- Summary: has anything been restored at all, and how recently?
SELECT  COUNT(*)              AS restores_recorded,
        MIN(restore_date)     AS earliest,
        MAX(restore_date)     AS most_recent,
        COUNT(DISTINCT destination_database_name) AS distinct_databases
FROM msdb.dbo.restorehistory;

Log backup cadence T-SQL

How often log backups actually run over the last seven days. The interval between them is your real worst-case data loss, whatever the recovery objective says on paper.

SELECT  d.name AS database_name,
        d.recovery_model_desc,
        COUNT(b.backup_set_id)                            AS log_backups_last_7d,
        MIN(b.backup_finish_date)                         AS earliest,
        MAX(b.backup_finish_date)                         AS latest,
        CASE WHEN COUNT(b.backup_set_id) > 1
             THEN DATEDIFF(MINUTE, MIN(b.backup_finish_date), MAX(b.backup_finish_date))
                  / NULLIF(COUNT(b.backup_set_id) - 1, 0)
        END                                               AS avg_interval_minutes,
        CAST(AVG(b.backup_size / 1048576.0) AS DECIMAL(18,1)) AS avg_size_mb
FROM    sys.databases d
LEFT JOIN msdb.dbo.backupset b
       ON b.database_name = d.name
      AND b.type = 'L'
      AND b.backup_finish_date > DATEADD(DAY, -7, GETDATE())
WHERE   d.recovery_model_desc IN ('FULL','BULK_LOGGED')
  AND   d.database_id <> DB_ID('tempdb')
GROUP BY d.name, d.recovery_model_desc
ORDER BY log_backups_last_7d ASC;

Integrity checks T-SQL

When DBCC CHECKDB last completed cleanly on each database. Corruption found late is often corruption that is already inside every backup you hold.

-- SQL Server 2016 SP2 / 2017 CU3 and later
SELECT  d.name AS database_name,
        DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS last_known_good_checkdb,
        DATEDIFF(DAY, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME), GETDATE())
                                                          AS days_since_checkdb
FROM    sys.databases d
WHERE   d.database_id <> DB_ID('tempdb')
  AND   d.state_desc = 'ONLINE'
ORDER BY last_known_good_checkdb ASC;

-- Older builds: DBCC DBINFO per database. Read-only, but noisy — run only if
-- the query above returns NULL for everything.
--
-- CREATE TABLE #dbinfo (ParentObject VARCHAR(255), [Object] VARCHAR(255),
--                       Field VARCHAR(255), [Value] VARCHAR(255));
-- INSERT INTO #dbinfo EXEC ('DBCC DBINFO(''YourDatabase'') WITH TABLERESULTS');
-- SELECT [Value] AS last_known_good FROM #dbinfo WHERE Field = 'dbi_dbccLastKnownGood';
-- DROP TABLE #dbinfo;

High availability configuration T-SQL

Always On availability groups, failover clustering, mirroring and log shipping — what exists, and whether replicas are actually synchronising right now.

-- Always On availability groups (returns nothing if not configured)
SELECT  ag.name AS ag_name, ar.replica_server_name, ar.availability_mode_desc,
        ar.failover_mode_desc, rs.role_desc, rs.connected_state_desc,
        rs.synchronization_health_desc, rs.last_connect_error_description
FROM    sys.availability_groups ag
JOIN    sys.availability_replicas ar        ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name;

-- Per-database synchronisation state and redo queue
SELECT  DB_NAME(drs.database_id) AS database_name, drs.synchronization_state_desc,
        drs.synchronization_health_desc, drs.log_send_queue_size, drs.redo_queue_size,
        drs.last_hardened_time, drs.last_redone_time
FROM    sys.dm_hadr_database_replica_states drs;

-- Failover cluster nodes
SELECT NodeName, status_description, is_current_owner FROM sys.dm_os_cluster_nodes;

-- Database mirroring
SELECT DB_NAME(database_id) AS database_name, mirroring_role_desc, mirroring_state_desc,
       mirroring_safety_level_desc, mirroring_partner_name
FROM   sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

-- Log shipping
SELECT primary_server, primary_database, backup_threshold, threshold_alert_enabled
FROM   msdb.dbo.log_shipping_monitor_primary;

File growth settings T-SQL

Percentage growth and unlimited log files. A 10% autogrowth on a 200 GB file is a 20 GB pause the first time it fires, usually at the worst moment.

SELECT  DB_NAME(mf.database_id)  AS database_name,
        mf.name                  AS logical_name,
        mf.type_desc,
        CAST(mf.size AS BIGINT) * 8 / 1024 AS size_mb,
        CASE WHEN mf.is_percent_growth = 1
             THEN CONCAT(mf.growth, ' %')
             ELSE CONCAT(CAST(mf.growth AS BIGINT) * 8 / 1024, ' MB') END AS growth,
        mf.is_percent_growth,
        CASE mf.max_size WHEN -1 THEN 'Unlimited'
                         WHEN 0  THEN 'No growth'
                         ELSE CONCAT(CAST(mf.max_size AS BIGINT) * 8 / 1024, ' MB') END AS max_size,
        mf.physical_name
FROM    sys.master_files mf
ORDER BY mf.is_percent_growth DESC, size_mb DESC;

Storage latency T-SQL

Average read and write stall per file since the instance started. This is what separates a slow query problem from a slow disk problem.

SELECT  DB_NAME(vfs.database_id) AS database_name,
        mf.name                  AS logical_name,
        mf.type_desc,
        vfs.num_of_reads,
        CASE WHEN vfs.num_of_reads  = 0 THEN 0
             ELSE vfs.io_stall_read_ms  / vfs.num_of_reads  END AS avg_read_stall_ms,
        vfs.num_of_writes,
        CASE WHEN vfs.num_of_writes = 0 THEN 0
             ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_stall_ms,
        CAST(vfs.num_of_bytes_read  / 1048576.0 AS DECIMAL(18,1)) AS mb_read,
        CAST(vfs.num_of_bytes_written/ 1048576.0 AS DECIMAL(18,1)) AS mb_written,
        mf.physical_name
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN    sys.master_files mf
     ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_stall_ms DESC;

-- Context: counters are cumulative since this time.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Wait statistics T-SQL

What the instance is actually waiting on, with idle and benign waits filtered out. This tells you where the time goes rather than where you assume it goes.

SELECT TOP (25)
        wait_type,
        waiting_tasks_count,
        CAST(wait_time_ms / 1000.0 AS DECIMAL(18,1))                        AS total_wait_s,
        CAST((wait_time_ms - signal_wait_time_ms) / 1000.0 AS DECIMAL(18,1)) AS resource_wait_s,
        CAST(signal_wait_time_ms / 1000.0 AS DECIMAL(18,1))                 AS signal_wait_s,
        CAST(wait_time_ms / NULLIF(waiting_tasks_count,0) AS DECIMAL(18,1)) AS avg_wait_ms,
        CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_of_total
FROM    sys.dm_os_wait_stats
WHERE   waiting_tasks_count > 0
  AND   wait_type NOT IN (
        'BROKER_TASK_STOP','BROKER_TO_FLUSH','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR',
        'BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT','CLR_AUTO_EVENT','CLR_MANUAL_EVENT',
        'CLR_SEMAPHORE','DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE',
        'DBMIRRORING_CMD','DIRTY_PAGE_POLL','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
        'FT_IFTSHC_MUTEX','HADR_CLUSAPI_CALL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_LOGCAPTURE_WAIT',
        'HADR_NOTIFICATION_DEQUEUE','HADR_TIMER_TASK','HADR_WORK_QUEUE','LAZYWRITER_SLEEP',
        'LOGMGR_QUEUE','MEMORY_ALLOCATION_EXT','ONDEMAND_TASK_QUEUE','PARALLEL_REDO_DRAIN_WORKER',
        'PARALLEL_REDO_LOG_CACHE','PARALLEL_REDO_TRAN_LIST','PARALLEL_REDO_WORKER_SYNC',
        'PARALLEL_REDO_WORKER_WAIT_WORK','PREEMPTIVE_XE_GETTARGETSTATE','PWAIT_ALL_COMPONENTS_INITIALIZED',
        'PWAIT_DIRECTLOGCONSUMER_GETNEXT','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE',
        'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP','QDS_SHUTDOWN_QUEUE','REDO_THREAD_PENDING_WORK',
        'REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK','SLEEP_BPOOL_FLUSH',
        'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY',
        'SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK','SLEEP_TEMPDBSTARTUP',
        'SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
        'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_WAIT_ENTRIES','WAIT_FOR_RESULTS',
        'WAITFOR','WAITFOR_TASKSHUTDOWN','WAIT_XTP_HOST_WAIT','WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        'WAIT_XTP_CKPT_CLOSE','XE_DISPATCHER_JOIN','XE_DISPATCHER_WAIT','XE_TIMER_EVENT')
ORDER BY wait_time_ms DESC;

Index health T-SQL Per database

Fragmentation on indexes large enough to matter, plus indexes nobody reads that every write still has to maintain. Change the database context and run once per database.

USE [YourDatabase];   -- change this, then run once per database
GO

-- Fragmentation. LIMITED mode is cheap and safe on production;
-- DETAILED reads every page and is not.
SELECT  DB_NAME()                                AS database_name,
        OBJECT_SCHEMA_NAME(ips.object_id)        AS schema_name,
        OBJECT_NAME(ips.object_id)               AS table_name,
        i.name                                   AS index_name,
        ips.index_type_desc,
        CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS frag_pct,
        ips.page_count,
        ips.page_count * 8 / 1024                AS size_mb
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN    sys.indexes i
     ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count > 1000        -- ignore anything under ~8 MB
  AND   ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Unused indexes: written on every insert, never read.
-- Counters reset when the instance restarts — check uptime before acting.
SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
        OBJECT_NAME(i.object_id)        AS table_name,
        i.name                          AS index_name,
        ISNULL(us.user_seeks,0)   AS seeks,
        ISNULL(us.user_scans,0)   AS scans,
        ISNULL(us.user_lookups,0) AS lookups,
        ISNULL(us.user_updates,0) AS writes
FROM    sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE   i.type_desc = 'NONCLUSTERED'
  AND   i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND   OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND   ISNULL(us.user_seeks,0) + ISNULL(us.user_scans,0) + ISNULL(us.user_lookups,0) = 0
ORDER BY writes DESC;

Missing index pressure T-SQL

What the optimiser has been asking for, weighted by impact. Treated as evidence to review — never applied as written, because the optimiser suggests one index per query with no regard for the ones already there.

SELECT TOP (25)
        DB_NAME(mid.database_id)                  AS database_name,
        OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
        CAST(migs.avg_total_user_cost * migs.avg_user_impact
             * (migs.user_seeks + migs.user_scans) AS DECIMAL(18,2)) AS improvement_score,
        migs.user_seeks, migs.user_scans,
        CAST(migs.avg_user_impact AS DECIMAL(5,1)) AS avg_impact_pct,
        migs.last_user_seek,
        mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM    sys.dm_db_missing_index_group_stats migs
JOIN    sys.dm_db_missing_index_groups  mig ON mig.index_group_handle = migs.group_handle
JOIN    sys.dm_db_missing_index_details mid ON mid.index_handle       = mig.index_handle
ORDER BY improvement_score DESC;

Statistics currency T-SQL Per database

How stale the statistics are and how much has changed since. Stale statistics produce bad plans that look exactly like a hardware problem.

USE [YourDatabase];   -- change this, then run once per database
GO

SELECT TOP (50)
        OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
        OBJECT_NAME(s.object_id)        AS table_name,
        s.name                          AS statistic_name,
        sp.last_updated,
        DATEDIFF(DAY, sp.last_updated, GETDATE()) AS days_old,
        sp.[rows],
        sp.rows_sampled,
        CAST(100.0 * sp.rows_sampled / NULLIF(sp.[rows],0) AS DECIMAL(5,1)) AS sampled_pct,
        sp.modification_counter                   -- changes since last update
FROM    sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND   sp.[rows] > 10000
ORDER BY sp.modification_counter DESC;

Expensive queries and blocking T-SQL

The queries costing the most CPU and reads across the whole instance, plus anything blocking right now and the deadlocks recorded by the system health session.

-- Top 20 by total CPU since the plan cache was last cleared
SELECT TOP (20)
        DB_NAME(st.dbid)                              AS database_name,
        qs.execution_count,
        CAST(qs.total_worker_time / 1000.0 AS DECIMAL(18,1))  AS total_cpu_ms,
        CAST(qs.total_worker_time / 1000.0 / qs.execution_count AS DECIMAL(18,1)) AS avg_cpu_ms,
        CAST(qs.total_elapsed_time / 1000.0 AS DECIMAL(18,1)) AS total_duration_ms,
        qs.total_logical_reads,
        qs.total_logical_reads / qs.execution_count    AS avg_reads,
        qs.last_execution_time,
        SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

-- Anything blocked right now
SELECT  r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
        r.wait_resource, r.status, r.command, DB_NAME(r.database_id) AS database_name,
        s.login_name, s.host_name, s.program_name,
        t.text AS running_statement
FROM    sys.dm_exec_requests r
JOIN    sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   r.blocking_session_id <> 0;

-- Deadlocks captured by the always-on system_health session
SELECT TOP (20)
        xed.value('@timestamp','datetime2') AS deadlock_time,
        xed.query('.')                      AS deadlock_graph
FROM   (SELECT CAST([target_data] AS XML) AS target_data
        FROM   sys.dm_xe_session_targets st
        JOIN   sys.dm_xe_sessions s ON s.address = st.event_session_address
        WHERE  s.name = 'system_health' AND st.target_name = 'ring_buffer') AS d
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed)
ORDER BY deadlock_time DESC;

SQL Agent job health T-SQL

Failed and long-running jobs, jobs with no failure notification at all, and jobs still owned by accounts belonging to people who have left.

-- Job inventory, owner and whether anyone is told when it fails
SELECT  j.name                          AS job_name,
        j.enabled,
        SUSER_SNAME(j.owner_sid)        AS job_owner,
        j.date_created,
        CASE WHEN j.notify_level_email = 0 THEN 'No email on failure' ELSE o.name END AS notifies,
        ja.next_scheduled_run_date
FROM    msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysoperators o ON o.id = j.notify_email_operator_id
LEFT JOIN (SELECT job_id, MAX(next_scheduled_run_date) AS next_scheduled_run_date
           FROM msdb.dbo.sysjobactivity GROUP BY job_id) ja ON ja.job_id = j.job_id
ORDER BY j.enabled DESC, j.name;

-- Outcomes over the last 14 days. run_status: 0 failed, 1 succeeded,
-- 2 retry, 3 cancelled, 4 in progress.
SELECT  j.name AS job_name,
        SUM(CASE WHEN h.run_status = 0 THEN 1 ELSE 0 END) AS failures,
        SUM(CASE WHEN h.run_status = 1 THEN 1 ELSE 0 END) AS successes,
        MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)) AS last_run,
        MAX(h.run_duration) AS longest_run_hhmmss
FROM    msdb.dbo.sysjobs j
JOIN    msdb.dbo.sysjobhistory h ON h.job_id = j.job_id AND h.step_id = 0
WHERE   h.run_date >= CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(DAY,-14,GETDATE()), 112))
GROUP BY j.name
ORDER BY failures DESC, last_run DESC;

Security and encryption T-SQL

Who holds sysadmin, the state of the sa account, which surface-area features are switched on, and whether encryption is in place where the data warrants it.

-- Server role membership — sysadmin first
SELECT  r.name AS server_role, m.name AS member_name, m.type_desc,
        m.is_disabled, m.create_date, m.modify_date
FROM    sys.server_role_members rm
JOIN    sys.server_principals r ON r.principal_id = rm.role_principal_id
JOIN    sys.server_principals m ON m.principal_id = rm.member_principal_id
ORDER BY CASE WHEN r.name = 'sysadmin' THEN 0 ELSE 1 END, r.name, m.name;

-- The sa account: renamed? disabled? password age?
SELECT  name, is_disabled, create_date, modify_date,
        LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set,
        LOGINPROPERTY(name, 'IsExpired')           AS is_expired,
        LOGINPROPERTY(name, 'IsLocked')            AS is_locked
FROM    sys.server_principals
WHERE   sid = 0x01;

-- Logins with no password policy, and SQL logins generally
SELECT  sl.name, sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked,
        LOGINPROPERTY(sl.name,'PasswordLastSetTime') AS password_last_set
FROM    sys.sql_logins sl
ORDER BY sl.is_policy_checked, sl.name;

-- Surface area: anything non-zero here needs a justification
SELECT name, value_in_use
FROM   sys.configurations
WHERE  name IN ('xp_cmdshell','Ole Automation Procedures','Ad Hoc Distributed Queries',
                'clr enabled','Database Mail XPs','remote access','cross db ownership chaining')
ORDER BY name;

-- Transparent Data Encryption
SELECT  DB_NAME(dek.database_id) AS database_name,
        CASE dek.encryption_state
             WHEN 0 THEN 'No key' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress'
             WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress'
             WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress'
        END AS encryption_state,
        dek.key_algorithm, dek.key_length, dek.percent_complete
FROM    sys.dm_database_encryption_keys dek;

-- Are connections actually encrypted in transit?
SELECT  encrypt_option, auth_scheme, net_transport, COUNT(*) AS connections
FROM    sys.dm_exec_connections
GROUP BY encrypt_option, auth_scheme, net_transport;

-- Orphaned database users (run per database)
-- USE [YourDatabase];
-- SELECT dp.name, dp.type_desc, dp.create_date
-- FROM sys.database_principals dp
-- LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
-- WHERE dp.type IN ('S','U','G') AND sp.sid IS NULL AND dp.principal_id > 4;

Sending us the results

There is a script that runs all of this for you. It collects every check on this page and writes a single zip file, so there is nothing to copy or paste. Ask for it on the free health check page and it is yours straight away.

If you would rather run the queries by hand, save each result set to CSV or copy the grid output into a spreadsheet. Either way, send the results to healthcheck@onsys.com.au, quoting your company name. If you would rather not send anything at all, we can run these together on a screen share and take notes as we go.

What happens next: we analyse the output, rate each of the twenty points, and send a written report with a prioritised remediation list within 7 business days of receiving your results. We then book a Teams call to walk you through it, within 2 weeks of receiving them. The call is free and there is no obligation. You keep the report either way.