Server Health Report – RockyPC – 2026-05-30

Executive summary

1. Establish integrity and recoverability immediately. Multiple user databases have effectively never recorded DBCC CHECKDB execution, and several databases in FULL recovery have no full or log backups. This is the highest-risk finding because corruption or data loss could go undetected and unrecoverable.
2. Tune the handful of very expensive analytical queries in SQLStorm. The largest plans show extreme CPU time, millions of logical reads, and memory grants up to 762,088 KB. The most obvious offenders scan large tables, use window functions heavily, and include non-SARGable predicates such as LIKE '%' + t.TagName + '%'.
3. Enable better plan-cache hygiene. optimize for ad hoc workloads is disabled while many high-cost plans appear to be single-use. Enabling it is a safe server-level improvement for this workload pattern.
4. Improve operational observability. There are no current deadlocks or memory-grant waits, but blocked-process reporting is disabled and backup/checkdb discipline is weak. Add baseline monitoring so concurrency and integrity issues are caught earlier.
5. Security posture is currently good. All current user connections are encrypted, all are local shared-memory sessions, and there is only one sysadmin login. Continue least-privilege discipline and certificate-backed TLS for any future TCP remote access.

Health snapshot

Server Health Overview
----------------------
Performance : Query-bound   [########--]
I/O         : Healthy       [##--------]
Memory      : Stable now    [###-------]
Integrity   : At risk       [##########]
Recovery    : At risk       [##########]
Security    : Good current  [##--------]
Concurrency : Good current  [##--------]
          

Detailed prioritized recommendations

1. Implement a formal CHECKDB schedule for every user database immediately. Priority 1
   • Critical databases with effectively missing CHECKDB history: WideWorldImporters, SQLStorm, tpcc, tpch, CRM, tpch10.
   • DigitsSolver is also overdue at 48 days.
   • Recommendation: run DBCC CHECKDB now during the next safe maintenance window, then schedule at least weekly for non-critical dev/test and daily-to-weekly depending on business value. For larger databases, offload to a restored backup copy if needed, but still ensure regular execution.
2. Fix backup and log-backup coverage before doing further tuning. Priority 1
   • No full backups recorded for master, model, msdb, DigitsSolver, tpcc, CRM, tpch10.
   • FULL recovery without log backups for model, DigitsSolver, tpcc, tpch, CRM, tpch10.
   • If point-in-time recovery is not required for some dev databases, switch them to SIMPLE recovery deliberately; otherwise start full + log backup chains immediately.
3. Tune the worst SQLStorm queries with emphasis on join patterns, search predicates, and pre-aggregation. Priority 2
   • The heaviest plans are dominated by large scans, complex CTE chains, repeated aggregation, and window functions across tables such as Posts, Votes, Users, Badges, and Comments.
   • The standout anti-pattern is p.Tags LIKE '%' + t.TagName + '%', which is non-SARGable and prevents efficient indexing.
   • Several plans request very large memory grants but use only a fraction of them, indicating estimation or shape inefficiency.
   • Refactor to normalize tags into a bridge table, pre-aggregate vote/comment counts once, avoid repeated correlated subqueries, and reduce wide intermediate rowsets before ranking.
4. Enable optimize for ad hoc workloads. Priority 2
   • This server shows many large, single-execution plans in cache.
   • Enabling the setting reduces wasted plan cache memory from one-off ad hoc compilations and is low risk.
5. Keep current MAXDOP and cost threshold under review, but do not change urgently. Priority 3
   • MAXDOP = 8 on a 16-logical-CPU host is reasonable.
   • cost threshold for parallelism = 30 is also reasonable and better than default.
   • No evidence here suggests these are the primary cause of the current pain.
6. Retain Query Store everywhere and tighten settings for more actionable history in SQLStorm. Priority 3
   • Query Store is enabled and writable across the listed user databases, which is good.
   • For SQLStorm, current usage is only 19 MB of 1000 MB. Consider switching capture mode from AUTO to CUSTOM or keeping AUTO while lowering noise through targeted thresholds, and reduce analysis intervals where you need finer trend visibility.
   • WideWorldImporters is at compatibility level 130. Because the instance is SQL Server 2022, evaluate raising compatibility to 160 after testing to unlock SQL Server 2022 optimizer features such as PSP optimization and DOP feedback.
7. No emergency lock-memory remediation is required now, but keep guardrails documented. Priority 3
   • OBJECTSTORE_LOCK_MANAGER is not elevated; the lock manager cache is only 1.65 MB, far below the 20–25% critical threshold.
   • No RESOURCE_SEMAPHORE waiters are present.
   • Current lock counts are modest; only session 57 shows object/page locks and not in alarming volume.
   • If this changes later and lock memory becomes excessive, emergency remediation can include DBCC FREESYSTEMCACHE ('Lock Manager : Node 0'), but only as a temporary relief measure after identifying the offending workload.
8. Continue least-privilege and encryption practices. Priority 3
   • All current connections are encrypted and shared memory; no unencrypted TCP clients are present.
   • Only one sysadmin exists, which is appropriate for a workstation/developer host.
   • For any future remote access, deploy a trusted TLS certificate and force encryption at the server endpoint.

Performance improvements

1. Target query rewrites before infrastructure changes.
   • I/O latency is excellent; storage is not the bottleneck.
   • Wait stats are not pointing to CPU scheduler pressure, latch storms, or memory-grant queueing.
   • The evidence points to inefficient query patterns causing large scans and grants.
2. Prioritize these plan-cache offenders for tuning in SQLStorm.

   Query Hash	Primary Symptom	Why It Matters	Recommendation
   558D53CC7558B81F	341s avg CPU, 4.1M logical reads, 458MB grant	Large ranking and aggregate pipeline	Pre-aggregate votes, index join/filter columns, trim columns before windowing
   872C7773968C6AAF	213s avg CPU, 866k reads, 577MB grant	Non-SARGable tag search with LIKE '%tag%'	Normalize tags to bridge table; avoid wildcard-leading predicate
   87EB726133317ADD	114s avg CPU, 6.6M reads	Large multi-table aggregates	Aggregate per table once, then join smaller result sets
   57CAE2375FFE5085	83s avg CPU, 5.1M reads, 282MB grant	Distinct count + wide joins + ranking	Stage into temp tables with supporting indexes
   83371E96D4DD244A	762MB memory grant	Largest grant on server sample	Reduce row width/cardinality early; inspect estimates vs actuals in Query Store

3. Query-shape recommendations for this workload.
   • Replace repeated scalar subqueries against Votes and Comments with grouped derived tables or temp-table staging.
   • Avoid joining Users to raw Posts, Votes, Comments, and Badges all at once before aggregation; that can explode row counts and distort grants.
   • When ranking, compute the smallest necessary set first, then apply ROW_NUMBER, RANK, or DENSE_RANK.
   • For reporting procedures, consider temporary result materialization with narrow indexes if the same intermediate sets are reused.
4. Memory assessment.
   • Committed memory is 5.3 GB against a target of 8.5 GB, well below max server memory. SQL Server is not currently consuming its ceiling.
   • Buffer pool is 4.23 GB and MEMORYCLERK_SQLQERESERVATIONS is 565 MB, consistent with query memory reservations but not an active server-wide crisis.
   • No red flag exists for lock-manager pressure because lock memory is tiny relative to total SQL memory.

Configuration optimizations

1. Enable optimize for ad hoc workloads.
   • Recommended because many costly plans show execution count = 1.
   • Expected benefit: reduced plan cache bloat from single-use statements.
2. Consider enabling backup checksum default and backup compression default.
   • Both are currently disabled.
   • On modern hardware, compressed backups usually reduce elapsed time and storage while checksums improve backup validation confidence.
3. Optionally enable remote admin connections for break-glass administration if remote TCP access is used.
   • Current value is 0. On a local-only dev workstation this is acceptable, so this is optional rather than urgent.
4. Consider tempdb metadata optimization only if tempdb contention emerges.
   • tempdb metadata memory-optimized is off.
   • No contention symptoms are shown here, so do not change preemptively.
5. Do not change max server memory urgently.
   • 23,168 MB on a 32 GB host leaves room for OS and tools, which is sensible on a workstation.

Index optimization opportunities

1. No high-value missing indexes were detected by the supplied workload sample.
   • This does not prove indexing is optimal.
   • In this case, missing-index DMVs likely under-report because the biggest problem is query shape and non-SARGable predicates, not just absent indexes.
2. Likely candidate indexing areas in SQLStorm after query refactoring.
   • Posts(OwnerUserId, CreationDate) supporting per-user latest/ranked post logic.
   • Posts(PostTypeId, CreationDate) for frequent question filtering.
   • Votes(PostId, VoteTypeId) for upvote/downvote aggregation.
   • Comments(PostId) and Badges(UserId) for count aggregation.
3. Recommendation: run a comprehensive index analysis using the Index Tuning goal for SQLStorm first, because that is where the expensive plans are concentrated.

Query Store optimization

1. Keep Query Store enabled on all listed user databases.
   • Current state is healthy: all listed user databases are READ_WRITE.
2. Optimize settings for SQLStorm.
   • Current settings are acceptable, but this appears to be the active tuning target and can benefit from more focused capture.
   • Recommended starting point: keep size at 1000 MB, retain auto cleanup, and consider shorter stats interval if you want more granular benchmarking during active tuning.
   • Use Query Store to compare runtime stats before and after each rewrite and to force only if a known good plan is stable and reproducible.
3. Review WideWorldImporters compatibility level.
   • It is at level 130 while the engine is SQL Server 2022.
   • After validation, raising compatibility to 160 would enable modern optimizer features and improve Query Store insight usefulness for current engine behavior.

Database integrity maintenance

1. Databases overdue or effectively missing CHECKDB:
   • Critical / effectively never recorded: WideWorldImporters, SQLStorm, tpcc, tpch, CRM, tpch10
   • Critical / stale: DigitsSolver (48 days)
2. Recommended schedule.
   • System databases: weekly minimum.
   • Important user databases: weekly minimum; daily for highly valuable or frequently changing databases.
   • Large databases: consider offloading CHECKDB to a restored copy, but keep cadence documented and verified.
3. Recovery recommendations.
   • Take immediate full backups of databases with none recorded.
   • For FULL recovery databases, start log backups or change recovery model to SIMPLE if point-in-time recovery is unnecessary.
   • Run restore tests regularly; backup existence alone is not sufficient.

Security enhancements

1. Encryption status is good.
   • All 10 current user connections are encrypted.
   • All are shared-memory local sessions; these may be ignored from network-risk perspective as requested.
   • No unencrypted telemetry concern is present in the supplied data.
2. Least privilege is currently appropriate.
   • Only one sysadmin account is present: RockyPC\k_a_f.
   • Retain this minimal posture and avoid adding service, app, or personal accounts to sysadmin unless absolutely necessary.
3. Forward-looking hardening.
   • For remote TCP connections, configure a trusted server certificate and force encryption.
   • Periodically audit login membership, disabled logins, and unused elevated permissions.

Concurrency issues

1. No deadlocks detected in the last 7 days.
   • No deadlock graph analysis is required from the supplied data.
   • If deadlocks appear later, run a dedicated detailed analysis using the Fix Deadlocks goal.
2. Current locking is not excessive.
   • Session 57 has 45 object locks and 8 page locks, which is not alarming by itself.
   • No evidence of runaway lock memory, ROWLOCK misuse, or page/row lock disabling was supplied.
3. Improve visibility into blocking.
   • blocked process threshold is 0, so blocked-process reports are disabled.
   • Enable a modest threshold such as 15 seconds if you want proactive blocking diagnostics.

Operational best practices

1. Patch planning: the instance is SQL Server 2022 RTM-GDR. Stay current on supported CUs/GDRs after validating for your tooling and workload.
2. Document intended recovery models per database so FULL recovery databases without log backups do not persist by accident.
3. Baseline key DMVs daily or weekly: waits, file stats, memory grants, top Query Store regressions, backup age, CHECKDB age, and failed jobs.
4. Use Query Store as the primary tuning ledger for SQLStorm and capture before/after runtime changes for each rewrite.
5. Where analytical procedures are experimental, consider running them in isolated windows to reduce cache churn and memory-grant variability.

Confidence levels

• High confidence: integrity risk from missing/stale CHECKDB history; recovery risk from absent full/log backups; healthy current encryption posture; low lock-manager risk; healthy I/O subsystem.
• Medium-high confidence: primary performance bottleneck is query design rather than server configuration or storage.
• Medium confidence: recommended indexing areas in SQLStorm, because missing-index DMVs did not surface strong candidates and final design should follow query refactoring and execution-plan review.
• Medium confidence: Query Store setting refinements beyond current configuration, because current settings are already broadly healthy and further tuning depends on desired retention granularity.

Scripts

Enable optimize for ad hoc workloads and improve backup defaults (implements configuration recommendations)

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
EXEC sys.sp_configure N'backup checksum default', 1;
EXEC sys.sp_configure N'backup compression default', 1;
RECONFIGURE;
GO

Enable blocked process reporting at 15 seconds (implements concurrency observability recommendation)

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'blocked process threshold (s)', 15;
RECONFIGURE;
GO

Run DBCC CHECKDB for overdue user databases now (implements integrity recommendation)

USE [master];
GO
DBCC CHECKDB (N'WideWorldImporters') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'SQLStorm') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'tpcc') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'tpch') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'CRM') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'tpch10') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
DBCC CHECKDB (N'DigitsSolver') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

Take immediate full backups for databases with no recorded full backup (implements recovery recommendation)

USE [master];
GO
BACKUP DATABASE [master] TO DISK = N'C:\SQLBackups\master_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [model] TO DISK = N'C:\SQLBackups\model_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [msdb] TO DISK = N'C:\SQLBackups\msdb_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [DigitsSolver] TO DISK = N'C:\SQLBackups\DigitsSolver_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [tpcc] TO DISK = N'C:\SQLBackups\tpcc_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [CRM] TO DISK = N'C:\SQLBackups\CRM_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP DATABASE [tpch10] TO DISK = N'C:\SQLBackups\tpch10_FULL.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO

Start log backup chain for FULL recovery databases that should remain in FULL (implements recovery recommendation)

USE [master];
GO
BACKUP LOG [model] TO DISK = N'C:\SQLBackups\model_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP LOG [DigitsSolver] TO DISK = N'C:\SQLBackups\DigitsSolver_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP LOG [tpcc] TO DISK = N'C:\SQLBackups\tpcc_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP LOG [tpch] TO DISK = N'C:\SQLBackups\tpch_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP LOG [CRM] TO DISK = N'C:\SQLBackups\CRM_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO
BACKUP LOG [tpch10] TO DISK = N'C:\SQLBackups\tpch10_LOG.trn' WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;
GO

Switch selected non-production databases to SIMPLE recovery if point-in-time restore is not required (implements recovery model alignment recommendation)

USE [master];
GO
ALTER DATABASE [DigitsSolver] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [tpcc] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [tpch] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [CRM] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [tpch10] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

Adjust Query Store settings for SQLStorm for active tuning work (implements Query Store recommendation)

USE [master];
GO
ALTER DATABASE [SQLStorm] SET QUERY_STORE = ON;
GO
ALTER DATABASE [SQLStorm] SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 15,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);
GO

Raise WideWorldImporters compatibility level to 160 after validation (implements version-capability recommendation)

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;
GO

Emergency only: clear Lock Manager cache if lock memory becomes excessive (implements emergency remediation option)

USE [master];
GO
DBCC FREESYSTEMCACHE ('Lock Manager : Node 0');
GO