Server Health Report – RockyPC – 2026-06-04

Executive Summary

Health Dashboard

Detailed Prioritized Recommendations

1. Immediately establish integrity validation. Run DBCC CHECKDB for all user databases, then schedule at least weekly for critical databases and monthly at minimum for lower-value databases.
2. Fix backup and log-backup coverage. Databases in FULL recovery without log backups are at risk of unbounded log growth and broken recovery objectives.
3. Patch to the latest SQL Server 2022 CU. This is the most important platform-level maintenance item after backup/integrity.
4. Reduce compilation overhead. Enable optimize for ad hoc workloads; then monitor compilation ratio and single-use plans.
5. Validate and implement missing indexes, starting with SQLStorm. Use targeted index review in SQLStorm, CRM, tpcc, tpch, tpch10, and WideWorldImporters.
6. Investigate the recent deadlock. One deadlock in 7 days is not a crisis, but it is a clear sign of concurrency contention needing root-cause review.
7. Retain current MAXDOP/CTFP unless workload testing suggests otherwise. MAXDOP 8 and cost threshold 30 are reasonable for a 16-logical-CPU system.
8. Keep Query Store enabled everywhere; tune only selected settings. Current Query Store posture is generally good.
9. Optionally enable blocked process threshold for troubleshooting. Helpful if deadlocks/blocking recur.

Performance

Findings

• Top waits are dominated by PWAIT_DIRECTLOGCONSUMER_GETNEXT and Query Store sleep waits such as QDS_PERSIST_TASK_MAIN_LOOP_SLEEP and QDS_ASYNC_QUEUE. These are typically benign background or idle waits and are not primary bottlenecks.
• CXCONSUMER and CXPACKET are present but low as a share of total waits. They do not currently indicate severe parallelism problems.
• I/O latency is healthy overall. tempdb is the busiest I/O consumer, but its observed row-file latency around 7 ms overall is acceptable.
• Memory pressure is low right now:
  • No active RESOURCE_SEMAPHORE waiters.
  • Top memory grant is only 1,024 KB.
  • Lock manager cache is only 3.42 MB, far below the 20-25% critical threshold.
• Compilation load is high:
  • Batch Requests/sec: 61,175
  • SQL Compilations/sec: 32,646
  • Compilation rate is roughly 53% of batch rate, which is too high for efficient plan reuse.
• Plan cache pollution is moderate:
  • Single-use plans: 8 of 26 plans (30.8%)
  • optimize for ad hoc workloads is currently disabled
• Buffer pool distribution query timed out, so exact per-database buffer residency could not be confirmed.

Recommendations

1. Enable optimize for ad hoc workloads. This is the clearest configuration improvement based on the observed compilation churn and single-use plan ratio.
2. Do not treat current waits as evidence of major storage or memory distress. The server’s present bottleneck is more likely query/plan efficiency than raw infrastructure.
3. Continue monitoring tempdb. Current tempdb file layout looks good: 8 evenly utilized data files for 16 schedulers, and latency is acceptable.
4. Retain MAXDOP 8 and cost threshold 30 for now. These values are reasonable for this hardware and SQL Server 2022 unless workload-specific testing proves a better alternative.

Configuration

Positive Configuration Notes

• max server memory is capped at 23,168 MB on a 32.5 GB machine, leaving room for the OS.
• max degree of parallelism = 8 is appropriate for 16 logical CPUs.
• cost threshold for parallelism = 30 is reasonable and much better than the default 5.
• clr enabled, xp_cmdshell, Ole Automation Procedures, and external scripts are disabled, which reduces attack surface.

Configuration Issues to Address

• optimize for ad hoc workloads = 0 should be changed to 1.
• backup checksum default = 0 should be enabled to improve backup validation quality.
• blocked process threshold = 0 limits troubleshooting visibility for blocking/deadlock analysis.
• remote admin connections = 0 is acceptable for local/lab use, but enabling DAC can help during severe production incidents.
• tempdb metadata memory-optimized = 0 is not automatically a problem; only consider enabling if you observe metadata contention, which is not shown here.

Patch and Lifecycle

• 🟢 Lifecycle Green SQL Server 2022 remains in mainstream support until 2028-01-12.
• 🔴 Patch Red The installed patch level is materially stale. Apply the latest SQL Server 2022 CU after regression testing.

Index Optimization

Findings

• There are 9+ high-impact missing index suggestions across 6 databases.
• The strongest candidate in the provided data is:
  • SQLStorm.dbo.PostHistory on equality column PostId including PostHistoryTypeId
  • Impact score: 70,054,034
• Several other DMV suggestions appear suspicious because they reference objects that look like constraints or oddly mismatched schemas. Those should be validated carefully before implementation.

Recommended next step: run a comprehensive Index Tuning analysis for CRM, SQLStorm, tpcc, tpch, tpch10, and WideWorldImporters.

Query Store

Assessment

• Query Store is enabled and writable on all listed databases. This is good and should be retained.
• Storage utilization is low, so there is no current Query Store pressure.
• SQLStorm is well-positioned for plan regression analysis because it is on compatibility level 160 with Query Store active.
• WideWorldImporters is still at compatibility level 130 even though the instance is SQL Server 2022. This means it is not using newer optimizer behaviors such as Parameter Sensitive Plan optimization.
• WideWorldImporters has Max Plans Per Query = 1000, which is unnecessarily high for most workloads.

Recommendations

1. Keep Query Store enabled everywhere.
2. For production-like databases, consider reducing MAX_PLANS_PER_QUERY from 1000 to 200 unless there is a strong reason to keep the larger value.
3. Evaluate raising WideWorldImporters compatibility level from 130 to 160 if application testing permits, to use SQL Server 2022 optimizer features.
4. Use Query Store to validate the before/after effect of any new index or configuration changes.

Database Integrity

Findings

Recommendations

1. Run CHECKDB immediately on every user database.
2. Schedule recurring CHECKDB jobs and record results centrally.
3. If maintenance windows are tight, use full CHECKDB on a rotating basis and reserve PHYSICAL_ONLY for interim checks only; it is not a substitute for full logical consistency checks.

Security

Findings

• Only one sysadmin login exists: RockyPC\k_a_f. This aligns with least-privilege principles better than most environments.
• All user sessions shown are encrypted over shared memory.
• The only unencrypted connection is NT SERVICE\SQLTELEMETRY using shared memory for SQLServerCEIP. As requested, this is minimal risk and can be ignored because user data is not exposed and shared memory is local only.
• Integrated Security Only is disabled, so SQL authentication may be allowed. No specific login risk data was provided beyond sysadmin membership.

Recommendations

1. Retain the current small sysadmin surface; continue using least privilege.
2. If remote client connections will be used, standardize encrypted connections with TLS and consider Force Encryption where operationally appropriate.
3. Audit all SQL-authenticated logins and disable any that are unused.

Concurrency

Findings

• One deadlock was captured in the last 7 days.
• No blocking chain data was provided, and the deadlock graph payload is truncated, so a precise lock/resource diagram cannot be rendered reliably.
• Current lock counts by session are low and do not indicate runaway lock accumulation.
• Lock manager memory is tiny, so there is no sign of lock-memory pressure.

[Process A]  --waits on--> [Resource X held by Process B]
[Process B]  --waits on--> [Resource Y held by Process A]

Result: SQL Server chose one victim and terminated it.

Recommendations

1. Run a dedicated deadlock analysis using the Fix Deadlocks goal.
2. Enable blocked process threshold for better troubleshooting if blocking/deadlocks continue.
3. Use Query Store and deadlock XML together to identify the statement pair and apply targeted indexing or transaction-scoping fixes.

Operations & Recovery

Critical Backup / Recovery Findings

• No full backup recorded for: master, model, msdb, DigitsSolver, tpcc, CRM, tpch10.
• FULL recovery but no log backups recorded for: model, DigitsSolver, tpcc, tpch, CRM, tpch10.
• WideWorldImporters full backup is 1335 days old.
• SQLStorm full backup is 221 days old.

Recommendations

1. Start immediate full backups for all databases lacking them.
2. For FULL recovery databases, begin regular transaction log backups or switch to SIMPLE if point-in-time recovery is not required.
3. Enable backup checksum default.
4. Test restores; backup existence alone is not recovery readiness.
5. Because uptime is only 7 days, ensure startup, maintenance, and backup jobs are all validated after recent restart.

Confidence Levels

Scripts

Enable optimize for ad hoc workloads to reduce plan cache churn and compilation overhead

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;
RECONFIGURE;
GO

Enable backup checksum by default to improve backup validation quality

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'backup checksum default', 1;
RECONFIGURE;
GO

Enable blocked process threshold for troubleshooting blocking and deadlocks

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

Run DBCC CHECKDB for all user databases immediately to implement integrity validation

USE [master];
GO
DECLARE @sql nvarchar(max) = N'';

SELECT @sql = @sql + N'
DBCC CHECKDB (N''' + REPLACE(name,'''','''''') + N''') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
'
FROM sys.databases
WHERE database_id > 4
  AND state_desc = N'ONLINE';

EXEC sys.sp_executesql @sql;
GO

Switch selected databases from FULL to SIMPLE recovery if point-in-time restore is not required

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 [CRM] SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE [tpch10] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

Create a high-value candidate nonclustered index on SQLStorm.dbo.PostHistory

USE [SQLStorm];
GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.PostHistory')
      AND name = N'IX_PostHistory_PostId_PostHistoryTypeId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_PostHistoryTypeId]
    ON [dbo].[PostHistory] ([PostId])
    INCLUDE ([PostHistoryTypeId]);
END
GO

Reduce Query Store max plans per query for WideWorldImporters to a more controlled setting

USE [master];
GO
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (
    MAX_PLANS_PER_QUERY = 200
);
GO

Raise WideWorldImporters compatibility level to 160 to use SQL Server 2022 optimizer features

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

Emergency-only command to flush lock manager cache if lock-manager memory ever becomes excessive

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