Marlon Ribunal SQL Server DBA · Performance Tuning · Production Support

Client: Contoso Ltd (sample)

Instance: PRODSQL01

Version: SQL Server 2022 CU12

Date: July 2026

SQL Server Health Check

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

/ 100

Health Score

Critical risk

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

0 · CriticalAt riskFairHealthy · 100

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.