Server Health Report – RockyPC – 2026-09-20

Executive summary

Health posture

Detailed prioritized recommendations

1. Run CHECKDB and establish recurring integrity maintenance. Treat all Jan 1, 1900 entries as missing, and run DigitsSolver immediately because its last recorded run was 161 days ago. Schedule weekly CHECKDB for active databases, with a less frequent cadence only where database size and risk justify it. Use a restored-copy or dedicated validation environment for the largest databases if production impact is material.
2. Repair backup and log-management coverage. Create full backup jobs for every database requiring recovery, and log-backup jobs for FULL databases. tpch has 9,172.5 MB active of 10,504 MB and truncation holdup LOG_BACKUP; begin log backups immediately. Investigate the active transaction in WideWorldImporters and the OLDEST_PAGE holdups in tpch10 and SQLStormCodeReview. Do not switch recovery models solely to hide an operational gap.
3. Apply SQL Server 2022 CU25 or the current approved CU. The supplied Recommendation is “Upgrade to the latest CU to ensure stability and security.” The instance is exactly identified as build 16.0.1200.5 versus latest supplied build 16.0.4255.1. Status “Outdated” means two or more CUs behind and requires upgrading. Lifecycle is separate: the version remains supported, so the lifecycle badge is green even though CU currency is red.
4. Tune the highest-cost queries in SQLStorm. Query hash 8E0DB20F8C836D8C averages 1,139,186,444 microseconds CPU and 13,117,331 logical reads. Query hashes 563E43D720E73157, 0EAB7945FE874674, 3D5EA98A3F806AA8, and 29216C0CE50B44B8 show large grants and spills. Break multi-join aggregates into independently pre-aggregated CTEs or temporary result sets, reduce COUNT(DISTINCT) fan-out, avoid repeated scans, and review predicates such as LIKE '%...%' on tags, which cannot use a conventional leading-key seek.
5. Implement and validate the highest-value missing indexes. Start with SQLStorm indexes on Badges, PostHistory, Users, Votes, and Comments, then SQLStormRealistic.Votes. Run the comprehensive Index Tuning goal for both databases before broad deployment because missing-index DMVs do not account for write overhead or overlapping indexes.
6. Reduce compilation churn. The 37.1% single-use-plan ratio and near one-to-one batch/compilation rate indicate ad hoc or unstable SQL generation. Prefer parameterized commands and stored procedures where appropriate. Evaluate enabling optimize for ad hoc workloads after confirming that the workload is ad hoc-heavy; this setting reduces first-use plan memory but does not fix excessive compilations.
7. Control memory-grant risk. Although no sessions currently wait on RESOURCE_SEMAPHORE, grants up to 2,474,136 KB and spills up to 725,895 indicate latent pressure. Fix cardinality and join fan-out issues before using Resource Governor. If pressure persists, classify the reporting workload into a pool with an appropriate maximum grant percentage.
8. Correct SQLStorm-family statistics configuration. Auto-create and auto-update statistics are disabled in SQLStorm, SQLStormCodeReview, SQLStormOltp, SQLStormQueryTuner, and SQLStormRealistic. Enable both in those databases and refresh important statistics after the change. Consider asynchronous updates for latency-sensitive workloads only after testing, because asynchronous updates can temporarily use stale statistics.
9. Standardize file growth and investigate file capacity. Six SQLStorm-family log files use 10% growth. Replace percent growth with fixed increments appropriate to workload, commonly 256 MB or 512 MB for these small logs after sizing them for normal activity. WideWorldImporters FILESTREAM file growth is disabled and can cause out-of-space failures; establish a capacity-controlled growth policy.
10. Maintain tempdb as configured. Sixteen logical CPUs and eight equal 18,696 MB data files satisfy the recommendation to start with eight files for servers above eight CPUs. All data files use the same 64 MB growth, so no proportional-fill issue is evident. Consider larger fixed growth increments only if repeated growth events are observed; pre-size files and keep all data files equal.
11. Secure connections and preserve least privilege. Ignore the unencrypted shared-memory telemetry connection as minimal risk because it is local telemetry and does not expose user data. For network clients, enforce TLS with a trusted certificate and Encrypt=True. One sysadmin member is reasonable; audit its continuing need and grant database-level permissions instead of server-wide privilege wherever possible.
12. Investigate the dominant waits by workload context. PWAIT_DIRECTLOGCONSUMER_GETNEXT, QDS_ASYNC_QUEUE, and QDS_PERSIST_TASK_MAIN_LOOP_SLEEP each represent about 28% of recorded wait time and have very low signal waits, suggesting asynchronous resource waits rather than CPU starvation. Correlate them with Query Store persistence, log consumers, backup, replication, or other enabled features before changing settings. Do not treat CXCONSUMER alone as a defect.

Performance and resource pressure

CPU, I/O, and workload shape

The host has 16 logical CPUs but only one physical CPU reported, with a hyperthread ratio of 16. This is a highly oversubscribed or virtualized topology and can magnify parallel-query latency. MAXDOP 8 and cost threshold 30 are reasonable starting points, but the query workload is dominated by large scans and aggregations rather than obvious scheduler starvation.

Reported I/O latency is healthy for most files: SQLStorm data averages 12–13 ms, WideWorldImporters user data 19 ms, and tempdb 2 ms overall. The highest meaningful data-file latency is WideWorldImporters user data at 19 ms; investigate storage placement if this persists. Tempdb generated approximately 464 GB of reads and 463 GB of writes across its data files but retained low latency, so the primary issue is workload volume, not current storage latency.

Memory and grants

Max server memory is 23,168 MB on a 32,527 MB machine, leaving approximately 9.4 GB for the operating system and other processes. The supplied memory-clerk extract totals only a portion of SQL Server memory, so it does not prove buffer-pool shrinkage. Tempdb occupies 3,234.72 MB of the reported buffer pool, while SQLStorm has 488.19 MB. The reported lock manager cache is 11.96 MB and is nowhere near the critical threshold of 20–25% of total SQL Server memory. Therefore, there is no evidence of critical OBJECTSTORE_LOCK_MANAGER pressure or the specific red flag of high lock memory with a low buffer pool.

ROWLOCK hints, disabled page/row locks, huge scans, and fan-out joins can balloon lock memory, but no such hints or lock-heavy query text were supplied. No deadlocks occurred in the last seven days and only one database lock was reported for session 52. If lock memory becomes excessive, the emergency option is DBCC FREESYSTEMCACHE ('Lock Manager : Node 0'); use it only during an incident because it is remediation, not a root-cause fix.

Configuration review

Index optimization opportunities

All reported high-value candidates affect SQLStorm except one SQLStormRealistic candidate. The supplied row counts and existing index sizes are used directly. Because column widths, nullability, clustering keys, compression, and fill factor were not supplied, size estimates are ranges rather than exact values. Estimates assume an 8 KB page, approximately 10–20% combined key/included-column and page overhead, the existing clustered key carried at the leaf, and no compression. Validate overlap and write cost with the Index Tuning goal.

Storage summary: the six SQLStorm consolidated designs are estimated at approximately 56–118 MB gross new storage. The SQLStormRealistic candidate adds approximately 12–26 MB gross. Total estimated gross addition is approximately 68–144 MB. No dropped index is recommended from the supplied data, so reclaimed storage is 0 MB and net storage change is approximately +68 to +144 MB. For the SQLStormRealistic modification/consolidation decision, before size is 23.4 MB for existing nonclustered indexes, after size is unknown, and net change is therefore an estimated range of approximately −11.4 to +2.6 MB only if the new index replaces equivalent existing coverage; do not drop an existing index without usage and definition validation.

Run Index Tuning for SQLStorm and SQLStormRealistic before broad deployment. This is especially important because several candidate indexes overlap on UserId and Votes, and the reported SQLStorm table data sizes are small enough that query-shape and write overhead should be tested rather than assumed.

Query Store and plan cache

Query Store is READ_WRITE and in AUTO capture mode for the writable databases. SQLStorm is only 2.4% full, so the 1,000 MB quota is adequate. The read-only benchmark databases correctly report actual READ_ONLY because the databases themselves are read-only; this is not a Query Store capacity failure. WideWorldImporters uses compatibility level 130, a 50-minute flush interval, 15-minute statistics interval, and 1,000 plans per query; review whether its older compatibility level is intentional before changing it.

The dominant Query Store waits are asynchronous queue and persistence-loop waits, each approximately 28% of total waits. Because signal waits are low, investigate Query Store persistence and storage behavior rather than raising MAXDOP or blaming CPU. Maintain AUTO cleanup, monitor Query Store size, and consider a lower plan-per-query limit for workloads producing many low-value variants. Do not disable Query Store to remove waits without first addressing the high compilation and plan-volume pattern.

The plan cache contains 550 plans, 204 single-use plans, and 44.1 MB of single-use cache. The highest-value remediation is parameterization and query simplification. SQL Server 2022 compatibility level 160 also provides Parameter Sensitive Plan optimization and other Intelligent Query Processing enhancements; retain compatibility level 160 in the SQLStorm-family databases unless regression testing identifies a specific issue.

Database integrity and recovery

CHECKDB status

The supplied Jan 1, 1900 date for WideWorldImporters, SQLStorm, tpcc, tpch, CRM, tpch10, PhotoCleanup, TunerBench, tpch10_bench_pristine, SQLStorm_bench_sqlstorm, SQLStormRealistic, SQLStormRealistic_bench_sqlstorm-realistic, SQLStormOltp, SQLStormOltp_bench_sqlstorm-oltp, SQLStormQueryTuner, SQLStormQueryTuner_bench_sqlstorm-query-tuner, SQLStormCodeReview, and SQLStormCodeReview_bench_sqlstorm-code-review indicates no recorded CHECKDB run. DigitsSolver was last checked on April 12, 2026, 161 days before the report date. Run CHECKDB immediately and schedule it at least weekly for active databases.

Recovery and log findings

• tpch: FULL recovery, no log backups, 149 active VLFs of 168, 9,172.5 MB active, holdup LOG_BACKUP. This is the most urgent log-management issue.
• WideWorldImporters: SIMPLE recovery, active transaction holdup, 155.4 MB active of 164 MB. Identify and resolve the transaction before the log fills.
• tpch10 and SQLStormCodeReview: OLDEST_PAGE holdup. Investigate long-running readers, indirect checkpoint behavior, or delayed page flush activity.
• tpcc: 14,664 MB log with only 0.7 MB active and 233 VLFs. VLF count is not yet above the approximately 1,000 concern threshold, but future growth should use appropriately sized fixed increments.
• model: FULL recovery with no recorded full or log backup. Because model is a system database, confirm whether FULL recovery is intentional; normally system databases have tailored recovery and backup procedures.

No database exceeds roughly 1,000 VLFs, so no VLF rebuild is currently mandated. Rebuild a log only when VLF fragmentation, repeated growth, or recovery performance justifies it; size the log first and grow in fixed, planned increments.

Security

• Encryption: sessions 52 and 53 use shared memory and are encrypted; shared memory is local and should not be treated as a network encryption gap. The unencrypted SQLTELEMETRY shared-memory connection is minimal risk because telemetry does not expose user data. For TCP clients, use a trusted TLS certificate, enforce encryption in SQL Server Configuration Manager where operationally appropriate, and set client connection strings to Encrypt=True.
• Privilege: only RockyPC\k_a_f is sysadmin. This is a reasonable count, but least privilege still requires periodic review, separation of administration from application identities, and database-scoped roles where possible.
• Surface area: xp_cmdshell, OLE Automation, CLR, external scripts, Ad Hoc Distributed Queries, and Database Mail are disabled. Keep them disabled unless a documented business requirement exists.
• Authentication: Integrated Security Only is false, so SQL authentication is available. Audit SQL logins, disable unused accounts, enforce strong password policy, and prefer contained or Windows authentication where appropriate.

Concurrency, locking, and blocking

No deadlocks were detected in the past seven days, so no deadlock graph or blocking-tree diagram is available. If deadlocks appear later, run the detailed Fix Deadlocks goal and identify the databases, statements, lock modes, and access-order inversion. Current lock-manager cache is only 11.96 MB, and session 52 reported one database lock, so there is no current evidence of runaway locking.

The main concurrency risk is indirect: large scans, ROWLOCK or lock-disabling hints if present in application code, multi-table fan-out aggregates, and long analytical transactions can increase lock duration and memory. Review the top query hashes before adding isolation changes. RCSI is enabled for PhotoCleanup and tpch; evaluate version-store impact in tempdb before enabling it elsewhere.

Operational best practices

• Instant file initialization: enabled for NT Service\MSSQLSERVER. No change is required. Remember that IFI accelerates data-file growth and restores; log files never use IFI.
• Tempdb: eight equally sized 18,696 MB data files, all with 64 MB growth, meet the CPU-based starting recommendation. Keep file sizes and growth increments equal.
• Autogrowth: replace 10% growth on SQLStorm, SQLStormRealistic, SQLStormOltp, SQLStormQueryTuner, SQLStormCodeReview, and tpch10 log files with fixed increments. Correct disabled FILESTREAM growth for WideWorldImporters after establishing capacity limits.
• Backups: verify backup jobs through msdb history and periodically perform restore tests. Full databases without log backups cannot meet point-in-time recovery objectives.
• Statistics: update statistics after enabling automatic statistics in SQLStorm-family databases, then use a targeted maintenance policy rather than indiscriminate nightly full scans.
• Patch process: apply CU25 or the latest approved CU in a maintenance window, test compatibility level 160 workloads, and document the installed build afterward.
• Monitoring: alert on log-used percentage, LOG_BACKUP and ACTIVE_TRANSACTION truncation holdups, CHECKDB age, backup age, Query Store read-only transitions, RESOURCE_SEMAPHORE waits, memory grants, compilation rate, and tempdb growth.

Scripts

1. Run CHECKDB for all reported databases with missing or overdue integrity evidence

USE [master];
GO
DBCC CHECKDB ([WideWorldImporters]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStorm]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([tpcc]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([tpch]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([CRM]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([tpch10]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([PhotoCleanup]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([TunerBench]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([tpch10_bench_pristine]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStorm_bench_sqlstorm]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormRealistic]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormRealistic_bench_sqlstorm-realistic]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormOltp]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormOltp_bench_sqlstorm-oltp]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormQueryTuner]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormQueryTuner_bench_sqlstorm-query-tuner]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormCodeReview]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([SQLStormCodeReview_bench_sqlstorm-code-review]) WITH NO_INFOMSGS;
GO
DBCC CHECKDB ([DigitsSolver]) WITH NO_INFOMSGS;
GO

2. Enable automatic statistics in the SQLStorm-family databases

USE [SQLStorm];
GO
ALTER DATABASE [SQLStorm] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [SQLStormCodeReview];
GO
ALTER DATABASE [SQLStormCodeReview] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormCodeReview] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [SQLStormOltp];
GO
ALTER DATABASE [SQLStormOltp] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormOltp] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [SQLStormQueryTuner];
GO
ALTER DATABASE [SQLStormQueryTuner] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormQueryTuner] SET AUTO_UPDATE_STATISTICS ON;
GO

USE [SQLStormRealistic];
GO
ALTER DATABASE [SQLStormRealistic] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormRealistic] SET AUTO_UPDATE_STATISTICS ON;
GO

3. Replace percent log autogrowth with fixed 256 MB growth

USE [SQLStorm];
GO
ALTER DATABASE [SQLStorm] MODIFY FILE
(
    NAME = [SQLStorm_log],
    FILEGROWTH = 256MB
);
GO

USE [SQLStormRealistic];
GO
ALTER DATABASE [SQLStormRealistic] MODIFY FILE
(
    NAME = [SQLStorm_log],
    FILEGROWTH = 256MB
);
GO

USE [SQLStormOltp];
GO
ALTER DATABASE [SQLStormOltp] MODIFY FILE
(
    NAME = [SQLStorm_log],
    FILEGROWTH = 256MB
);
GO

USE [SQLStormQueryTuner];
GO
ALTER DATABASE [SQLStormQueryTuner] MODIFY FILE
(
    NAME = [SQLStorm_log],
    FILEGROWTH = 256MB
);
GO

USE [SQLStormCodeReview];
GO
ALTER DATABASE [SQLStormCodeReview] MODIFY FILE
(
    NAME = [SQLStorm_log],
    FILEGROWTH = 256MB
);
GO

USE [tpch10];
GO
ALTER DATABASE [tpch10] MODIFY FILE
(
    NAME = [tpch10_log],
    FILEGROWTH = 256MB
);
GO

4. Create consolidated high-value indexes in SQLStorm

USE [SQLStorm];
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'[dbo].[Badges]')
      AND name = N'IX_Badges_UserId_Class_Name'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Badges_UserId_Class_Name]
    ON [dbo].[Badges] ([UserId])
    INCLUDE ([Class], [Name]);
END;
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

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'[dbo].[Users]')
      AND name = N'IX_Users_Reputation'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Users_Reputation]
    ON [dbo].[Users] ([Reputation]);
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'[dbo].[Votes]')
      AND name = N'IX_Votes_UserId_VoteTypeId_BountyAmount'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_UserId_VoteTypeId_BountyAmount]
    ON [dbo].[Votes] ([UserId])
    INCLUDE ([VoteTypeId], [BountyAmount]);
END;
GO

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

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'[dbo].[Comments]')
      AND name = N'IX_Comments_PostId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId]
    ON [dbo].[Comments] ([PostId]);
END;
GO

5. Create the high-value VoteTypeId index in SQLStormRealistic

USE [SQLStormRealistic];
GO

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

Confidence and limitations

Reported date values are internally inconsistent with the supplied analysis timestamp and server start time. The Jan 1, 1900 CHECKDB values are therefore treated as missing evidence, not as literal historical executions.