SQL Server Health Check — Demo Instance

SQL Server Health Check — Sample Report

Marlon Ribunal SQL Server DBA · Performance Tuning · Production Support

Client: {{CLIENT NAME}}

Instance: {{SERVER\INSTANCE}}

Version: SQL Server 2022 CU12

Date: {{DATE}}

SQL Server Health Check

A read-only assessment of configuration, reliability, performance, and safety. No changes were made to your server.

/ 100

Health Score

Your data is at risk today. This database is in FULL recovery mode but has never had a transaction-log backup. That means two things right now: you cannot recover to a point in time if something goes wrong, and the log file will grow until it fills the disk and stops the database.

Two more issues will bite under load: the server can consume all system memory and destabilize the machine, and no corruption checks are running — damage would go unnoticed until it’s too late.

12 findings total. The three above are the ones I’d fix first. Full detail follows, grouped by severity.

2 Critical 4 High 4 Medium 2 Low

Critical — fix now

No transaction-log backups (data-loss risk)

Critical

Category: Backups / Disaster Recovery · Check J1

What I found: Database HealthCheckDemo is in FULL recovery model with a full backup but zero log backups. log_reuse_wait_desc = LOG_BACKUP.
Why it costs you: You can only restore to your last full backup — everything since is lost if the server fails. And the transaction log grows without limit until it fills the disk and the database stops accepting writes. This is a “3am outage” waiting to happen.
Recommended fix: Add scheduled log backups (e.g. every 15 min) to match FULL recovery, or switch to SIMPLE recovery if point-in-time restore isn’t required. Right-size the log afterward.

No recent integrity check (undetected corruption)

Critical

Category: Backups / DR · Check J2 (DBCC CHECKDB)

What I found: No record of a successful DBCC CHECKDB for user databases.
Why it costs you: Disk-level corruption stays silent until a query hits the bad page — often months later, and often after your good backups have already rolled off. You find out when it’s unrecoverable.
Recommended fix: Schedule weekly DBCC CHECKDB and alert on failure. Verify backups are restorable, not just running.

High — fix soon

Max server memory is uncapped

High

Category: Memory · Check C1

What I found: max server memory (MB) = 2147483647 (the default “take everything”).
Why it costs you: SQL Server can starve Windows/Linux of memory, forcing paging and making the whole box unstable or unresponsive under load.
Recommended fix: Cap max memory, leaving headroom for the OS and any other services on the box.

CPU parallelism settings at unsafe defaults

High

Category: CPU · Check B1

What I found: cost threshold for parallelism = 5 (a 1998-era default) and priority boost = 1 (Microsoft advises against this).
Why it costs you: Trivial queries needlessly fan out across CPUs and starve the queries that matter; priority boost can destabilize the whole instance. Symptom: “the server is randomly slow.”
Recommended fix: Raise cost threshold to ~25–50, set priority boost = 0, and set MAXDOP to guidance for the core/NUMA layout.

Transaction log is heavily fragmented (VLF bloat)

High

Category: Databases · Check I1

What I found: HealthCheckDemo log contains ~1,400 virtual log files, caused by many tiny 1 MB autogrowth events (threshold: >200 = Medium, >1000 = High).
Why it costs you: Slower crash recovery and failover, and slower log backups. If the server restarts hard, it comes back online slower than the business expects.
Recommended fix: Shrink the log, regrow in large fixed chunks, and set a sensible fixed autogrowth so it doesn’t recur.

Auto-shrink is enabled

High

Category: Databases + model · Check I2 / H1

What I found: Auto-shrink is ON for HealthCheckDemo — and on the model database, so every new database inherits it.
Why it costs you: Auto-shrink runs a shrink/grow loop that massively fragments indexes and burns IO, quietly degrading performance for no benefit.
Recommended fix: Disable auto-shrink on all databases and on model.

Medium — worth addressing

No page-verification (corruption goes undetected)

Medium

Category: Databases + model · Check I2 / H1

What I found: PAGE_VERIFY = NONE on HealthCheckDemo and on model.
Why it costs you: SQL Server won’t notice torn or corrupted pages, so corruption spreads silently before anyone sees an error.
Recommended fix: Set PAGE_VERIFY CHECKSUM on all databases and model.

Data file uses percentage autogrowth

Medium

Category: IO / Storage · Check E3

What I found: The data file grows by 10% each time instead of a fixed size.
Why it costs you: Growth events get bigger and slower as the file grows, freezing writes for a moment each time and fragmenting the file — intermittent stalls nobody can explain.
Recommended fix: Switch to a fixed growth increment; enable Instant File Initialization for data files.

Missing index on a frequently-queried table

Medium

Category: Indexes · Check K1

What I found: A frequent filter on CustomerId, Status on dbo.Orders has no supporting index — surfaced by SQL Server’s missing-index data with a high improvement estimate.
Why it costs you: Common lookups read far more data than needed, wasting CPU and IO and slowing the app as the table grows.
Recommended fix: Add a targeted nonclustered index, validated against the real workload (not just the DMV suggestion). Confirm the table’s clustering separately.

Query Store is off (SQL Server 2022/2025)

Medium

Category: Query Store · Check L5

What I found: HealthCheckDemo has Query Store disabled.
Why it costs you: You’re leaving free, built-in performance history and regression detection on the table — the exact data you’d want the day something slows down.
Recommended fix: Enable Query Store with a sensible retention/capture policy.

Low — hygiene

Dangerous surface area enabled

Low

Category: Security · Check L3

What I found: xp_cmdshell and Ole Automation Procedures are enabled.
Why it costs you: Both widen the attack surface: a compromised login gains more reach than the task needs. Low as isolated hygiene, higher if the instance is internet-adjacent.
Recommended fix: Disable unless there’s a documented, reviewed need.

Ad hoc plan cache not optimized

Low

Category: Memory · Check C1 / C6

What I found: optimize for ad hoc workloads = 0 with many single-use plans cached.
Why it costs you: Memory that could cache data is wasted on plans that run once — a small, easy reclaim.
Recommended fix: Enable optimize for ad hoc workloads.

Appendix — Top query offenders

Raw workload detail (top 15 in a full run). Not scored — this is the tuning backlog behind the findings above. Cumulative since each plan was cached; read alongside server uptime.

Top CPU consumers — by total CPU, then avg logical reads

Query	DB	Execs	Total CPU (ms)	Avg logical reads
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerId=@p1 AND Status=@p2	HealthCheckDemo	40	18,220	2,146
SELECT o.* FROM dbo.Orders o WHERE o.OrderDate >= @p1	HealthCheckDemo	1,204	9,910	512
UPDATE dbo.Orders SET Status=@p1 WHERE OrderId=@p2	HealthCheckDemo	8,530	6,050	8
… 12 more rows in a full run …

Top memory consumers — by grant, then avg logical reads

Query	DB	Grant (MB)	Used (MB)	Avg logical reads
SELECT … FROM dbo.Orders ORDER BY Amount DESC	HealthCheckDemo	96	21	2,146
SELECT CustomerId, SUM(Amount) FROM dbo.Orders GROUP BY CustomerId	HealthCheckDemo	64	58	1,890
… 13 more rows in a full run …

Note the first memory row: a 96 MB grant that only used 21 MB — the optimizer over-estimated, a stats/plan problem worth tuning. That kind of read is the value a human DBA adds over a script.

Where this leaves you

Two of these findings put your data at real risk, and four more are quietly costing you performance every day. Most can be fixed in a short, low-risk engagement. I’d be glad to walk through the plan and handle the remediation with you.