SQL Server health assessment: T-SQL queries and PowerShell scripts

A health assessment starts with context

An instance can be available while still carrying recovery risk, gradual saturation or an unknown dependency. Document the owner, application, criticality, RPO/RTO, peak hours, maintenance windows, recent changes and observed symptoms.

The examples are read-only, except DBCC CHECKDB, which performs intensive work without changing data when no REPAIR option is used. Adapt them to the version, edition and hosting model.

1. Inventory the instance and databases

Collect exact build, edition, Always On availability, last startup, database state, recovery model, compatibility and PAGE_VERIFY. Compare the build with Microsoft's update catalog; the major version alone is insufficient.

Several DMVs restart with the service. AUTO_CLOSE or AUTO_SHRINK on production requires an explicit reason, but do not automatically change every deviation without context.

SELECT @@SERVERNAME AS instance_name,
 SERVERPROPERTY('ProductVersion') AS product_version,
 SERVERPROPERTY('ProductLevel') AS product_level,
 SERVERPROPERTY('Edition') AS edition,
 SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled,
 sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT name,state_desc,recovery_model_desc,compatibility_level,
 page_verify_option_desc,is_auto_close_on,is_auto_shrink_on
FROM sys.databases ORDER BY name;

2. Prove that backups can be restored

msdb history proves that a backup was recorded, not that it is usable or meets the RPO. Validate files, encryption and keys, protected copies, CHECKSUM where appropriate, and then restore in an isolated environment.

RESTORE VERIFYONLY is not a restore test. A useful test opens the restored database, performs integrity checks and validates a business transaction.

SELECT d.name,
 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_diff,
 MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name=d.name
WHERE d.database_id>4 GROUP BY d.name ORDER BY d.name;

• Microsoft — SQL Server backup and restore ↗

3. Schedule DBCC CHECKDB

CHECKDB can consume CPU, memory, I/O, tempdb and space for its internal snapshot. Start with ESTIMATEONLY. PHYSICAL_ONLY can run more often on large databases, but does not replace periodic full checks.

Running the full check on a recent restore reduces production impact. REPAIR_ALLOW_DATA_LOSS remains a last resort; restoring a known-good backup is the recommended approach.

DBCC CHECKDB (N'MyDatabase') WITH ESTIMATEONLY;
DBCC CHECKDB (N'MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'MyDatabase') WITH PHYSICAL_ONLY, NO_INFOMSGS;

• Microsoft — DBCC CHECKDB ↗

4. Jobs, logs, files and growth

Find disabled jobs, failures and abnormal duration. Review SQL Server Error Log and Windows Event Log for errors 823/824/825, corruption, dumps, login failures and failovers.

Measure file size, location and growth. Size fixed growth according to observed workload. Interpret log usage with the recovery model, log backups, long transactions, replication and Always On. Avoid recurring SHRINK maintenance.

SELECT j.name,j.enabled,
 msdb.dbo.agent_datetime(h.run_date,h.run_time) AS last_run,
 h.run_status,h.message
FROM msdb.dbo.sysjobs j
OUTER APPLY (SELECT TOP(1) run_date,run_time,run_status,message
 FROM msdb.dbo.sysjobhistory WHERE job_id=j.job_id AND step_id=0
 ORDER BY instance_id DESC) h
ORDER BY h.run_status,j.name;

SELECT DB_NAME(mf.database_id) AS database_name,mf.type_desc,
 mf.physical_name,mf.size/128.0 AS size_mb,
 CASE WHEN mf.is_percent_growth=1 THEN CONCAT(mf.growth,'%')
 ELSE CONCAT(mf.growth/128.0,' MB') END AS growth
FROM sys.master_files mf ORDER BY database_name,mf.type_desc;

SELECT DB_NAME(database_id) AS database_name,
 total_log_size_in_bytes/1048576.0 AS log_size_mb,
 used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

5. tempdb, memory, CPU and I/O

Review consistent tempdb sizing and growth, available space and contention. Correlate memory and CPU with workload instead of imposing one universal threshold.

sys.dm_io_virtual_file_stats latency is cumulative since startup or reset. Compare data and log files over equivalent periods; averages can hide spikes.

SELECT DB_NAME(v.database_id) AS database_name,m.type_desc,m.physical_name,
 CASE WHEN v.num_of_reads=0 THEN NULL ELSE 1.0*v.io_stall_read_ms/v.num_of_reads END AS avg_read_ms,
 CASE WHEN v.num_of_writes=0 THEN NULL ELSE 1.0*v.io_stall_write_ms/v.num_of_writes END AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) v
JOIN sys.master_files m ON m.database_id=v.database_id AND m.file_id=v.file_id
ORDER BY avg_read_ms DESC;

• Microsoft — sys.dm_io_virtual_file_stats ↗

6. Waits, blocking and deadlocks

Wait statistics describe what tasks waited for; they do not prescribe a fix. Capture a delta during a representative period. Do not clear statistics without agreement because that removes context used by others.

Short blocking is normal. For intermittent incidents, use the system_health Extended Events session and inspect the deadlock graph. NOLOCK is not a universal remedy: it can return inconsistent results.

SELECT TOP(15) wait_type,waiting_tasks_count,wait_time_ms,signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','SLEEP_SYSTEMTASK','LAZYWRITER_SLEEP')
ORDER BY wait_time_ms DESC;

SELECT r.session_id,r.blocking_session_id,r.status,r.wait_type,r.wait_time,
 DB_NAME(r.database_id) AS database_name,t.text AS batch_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id<>@@SPID AND (r.blocking_session_id<>0 OR r.wait_type IS NOT NULL)
ORDER BY r.blocking_session_id DESC,r.session_id;

• Microsoft — sys.dm_os_wait_stats ↗

7. Expensive queries and Query Store

Query Store retains queries, plans and interval statistics. Confirm status, size and retention. Rank by CPU, duration, reads and variability, then consider frequency and business value.

One expensive query run once may matter less than a small query executed millions of times. Do not force a plan without monitoring and a removal procedure.

SELECT TOP(20) qt.query_sql_text,
 SUM(rs.count_executions) AS executions,
 SUM(rs.avg_cpu_time*rs.count_executions)/NULLIF(SUM(rs.count_executions),0) AS avg_cpu_us
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id=qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id=q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id=rs.runtime_stats_interval_id
WHERE i.start_time>=DATEADD(day,-7,SYSUTCDATETIME())
GROUP BY qt.query_sql_text ORDER BY avg_cpu_us DESC;

• Microsoft — Query Store ↗

8. Indexes and statistics with judgment

Missing-index suggestions are cumulative and omit some write costs. Consolidate them, review existing indexes and measure benefit.

Fragmentation alone does not justify a blanket rebuild. Consider size, storage, load and maintenance windows. Update statistics selectively and retain before/after plans, duration, CPU and reads.

• Do not apply one threshold to every database.
• Allow for transaction-log space and replication or AG impact.
• Avoid unvalidated automatic remediation.

9. Configuration, security and permissions

Inventory max server memory, MAXDOP, cost threshold, privileged accounts, owners, authentication, encryption and network exposure. Correct values depend on hardware and workload.

SQL Server 2022 uses VIEW SERVER PERFORMANCE STATE for several performance DMVs that previously required VIEW SERVER STATE. Use a dedicated least-privilege collector and never put a SQL secret in clear text.

SELECT name,value_in_use FROM sys.configurations
WHERE name IN ('backup checksum default','cost threshold for parallelism',
'max degree of parallelism','max server memory (MB)','optimize for ad hoc workloads');

SELECT name,type_desc,is_disabled,create_date,modify_date
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%'
ORDER BY type_desc,name;

10. Always check Always On when present

Review role, connection, synchronization, send and redo queues, quorum, listeners, backup preference and failover tests. HEALTHY does not prove that the application reconnects.

This query is a local overview. Add per-database state and cluster logs. Do not initiate a failover without an approved change.

SELECT ag.name AS ag_name,ar.replica_server_name,rs.role_desc,
 rs.connected_state_desc,rs.synchronization_health_desc
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;

11. Automate lightweight collection with PowerShell

The SqlServer module supports multi-instance collection with Windows authentication, gMSA or a token depending on the platform. The example handles timeouts and errors without requesting a secret; adapt Encrypt to your certificate policy.

Schedule under a least-privilege identity, protect outputs and set retention. Keep CHECKDB, restore tests and expensive analysis in separate jobs.

# instances.txt: one instance per line
Import-Module SqlServer
$Out=Join-Path $PSScriptRoot ('SQLHealth-'+(Get-Date -Format yyyyMMdd-HHmmss))
New-Item -ItemType Directory -Path $Out -ErrorAction Stop | Out-Null
$Query="SELECT @@SERVERNAME InstanceName, CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)) ProductVersion, CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) Edition, (SELECT COUNT(*) FROM sys.databases WHERE state_desc<>'ONLINE') DatabasesNotOnline;"
$Results=foreach($Instance in Get-Content "$PSScriptRoot\instances.txt"){
 try { Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query $Query -ConnectionTimeout 15 -QueryTimeout 60 -Encrypt Optional -AbortOnError -ErrorAction Stop | Select-Object *,@{n='CollectedAt';e={Get-Date}},@{n='Status';e={'OK'}} }
 catch { [pscustomobject]@{InstanceName=$Instance;CollectedAt=Get-Date;Status='ERROR';Error=$_.Exception.Message} }
}
$Results|Export-Csv (Join-Path $Out 'instances.csv') -NoTypeInformation -Encoding UTF8
$Results|ConvertTo-Html -Title 'SQL Server health collection'|Set-Content (Join-Path $Out 'report.html') -Encoding UTF8

• Microsoft — Invoke-Sqlcmd ↗

Prioritize the report and action plan

Every finding needs evidence, impact, confidence, owner, due date, test and rollback. The checklist covers inventory, restore, CHECKDB, jobs, logs, capacity, tempdb, I/O, waits, blocking, Query Store, security and high availability.

These queries provide a collection baseline, not a complete automated assessment. Workload and application interpretation remain the most valuable part.

• Service — Infrastructure modernization →
• Guide — Immutable Veeam backups →