Server Health Report – RockyPC – 2026-09-27

Executive summary

Top priorities

1. Apply the latest SQL Server 2022 CU. Lifecycle is 🟢 Green through 2028-01-12, but CU currency is 🔴 Outdated. The supplied recommendation is to upgrade to CU25, build 16.0.4255.1, from installed build 16.0.1200.5.
2. Establish integrity and recovery protection. Run CHECKDB for every database with a missing or stale result, and create full backups for databases reporting “Never.” Full-recovery databases without log backups cannot provide the intended point-in-time recovery chain.
3. Investigate the seven deadlocks. The supplied XML is truncated and does not expose database IDs, object names, statements, or lock modes. Run the Fix Deadlocks goal using the captured graphs.
4. Resolve transaction-log holdups. tpch has 9,173.2 MB active of 10,504.0 MB with LOG_BACKUP; WideWorldImporters has 158.0 MB active of 164.0 MB with ACTIVE_TRANSACTION.
5. Reduce compilation pressure. The measured 13,063 SQL compilations/sec against 13,065 batch requests/sec and 37.5% single-use plans indicate likely ad hoc or highly variable statement generation. Parameterization and application-side plan reuse should be reviewed.

Confidence: high for supplied measurements and explicit status fields; medium for causal interpretations because the observation window is only 37.4 hours and data was reset at the last restart.

Environment and posture

Detailed prioritized recommendations

1. Emergency / immediate: protect recovery and integrity.
   • Run the CHECKDB batch in the Scripts section. The input reports 1900-01-01 for WideWorldImporters, SQLStorm, tpcc, tpch, CRM, tpch10, PhotoCleanup, TunerBench, multiple benchmark databases, SQLStormRealistic, SQLStormOltp, SQLStormQueryTuner, SQLStormCodeReview, and DeadlockDemo; this is a missing/sentinel result rather than evidence that a check actually ran on that date.
   • DigitsSolver last ran 2026-04-12 and is reported as 168 days old. Run CHECKDB immediately and schedule at least weekly checks for active databases, with frequency adjusted to workload and restore-test requirements.
   • Create full backups for master, model, msdb, DigitsSolver, tpcc, tpch, CRM, tpch10, PhotoCleanup, TunerBench, DeadlockDemo, and databases explicitly reporting no full backup. Backup paths and retention requirements were not supplied, so no backup command is generated.
2. Immediate: update the engine.

   Honor the supplied CU recommendation: upgrade from installed build 16.0.1200.5 to latest CU25 build 16.0.4255.1. The status means the instance is two or more CUs behind. Servicing model is Active CU, so apply CU25 during a controlled maintenance window. This is distinct from lifecycle status: lifecycle remains green.

3. Immediate: address log reuse problems.
   • For tpch, establish regular log backups because the database is FULL recovery, has no log backups recorded, and has 9,173.2 MB active of 10,504.0 MB with LOG_BACKUP.
   • For WideWorldImporters, identify and resolve the active transaction holding 158.0 MB of 164.0 MB. The input does not identify the session or transaction.
   • For SQLStormOltp and SQLStormQueryTuner, inspect the oldest page holdup. The supplied data lacks the responsible database/session context.
   • tpcc has 14,664.0 MB total log but only 0.3 MB active and 233 VLFs; VLF count is below the approximate 1,000-risk threshold, but the log is substantially larger than current active usage. Do not shrink routinely; size it deliberately after workload and recovery requirements are established.
4. High: analyze and remediate deadlocks.

   Seven deadlocks occurred in the past seven days, including three within approximately 12 minutes on 2026-09-26. Run the Fix Deadlocks goal. The supplied graph fragments do not reveal the involved databases, objects, statements, or lock modes, so database attribution cannot be made safely. The displayed top grant text includes an explicit transaction and update against Application.StateProvinces; it is a candidate for review, not proof that it caused the deadlocks.

   2026-09-26 09:32:37 UTC  victim: process1b29446a8c8
   2026-09-26 09:32:32 UTC  victim: process1b29446a8c8
   2026-09-26 09:20:44 UTC  victim: process1b2944628c8
   2026-09-24 18:46:44 UTC  victim: process235ca46aca8
   2026-09-20 21:09:57 UTC  victim: process235fb64c108
   2026-09-20 21:08:04 UTC  victim: process235ca46aca8
   2026-09-20 21:07:41 UTC  victim: process235fb7604e8

   Caption: available deadlock victim identifiers and timestamps. The input does not contain enough graph detail to draw a reliable holder/waiter resource diagram.

5. High: reduce compilation and plan-cache churn.

   There are 24 total plans, 9 single-use plans, 4.1 MB plan cache, and 1.5 MB single-use cache. A 37.5% single-use ratio is material, but the observation period is only 37.4 hours. Enable optimize for ad hoc workloads only after confirming ad hoc cache pressure and testing application behavior; prioritize parameterized commands, stored procedures where appropriate, and consistent data types. The supplied top plans are diagnostic/reporting queries executed once and are not runaway plans: maximum reported grant is 1,624 KB and maximum spills are zero.

6. High: correct database options.

   SQLStorm, SQLStormCodeReview, SQLStormOltp, SQLStormQueryTuner, and SQLStormRealistic have both AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS disabled. Enable both unless a documented workload-specific reason exists. All audited databases use CHECKSUM, have AUTO_CLOSE and AUTO_SHRINK off, and therefore show no issue for those options.

7. Medium: fix file-growth policies.

   SQLStorm, SQLStormCodeReview, SQLStormOltp, SQLStormQueryTuner, SQLStormRealistic, and tpch10 log files use 10% growth. Replace percentage growth with fixed increments selected from measured workload. The generated script uses 256 MB for SQLStorm, 64 MB for SQLStormRealistic, and 64 MB for the smaller listed logs; these are operational starting points, not measured workload requirements. WideWorldImporters FILESTREAM file WWI_InMemory_Data_1 has growth disabled and can fail with out-of-space errors; its appropriate growth policy cannot be safely scripted without the required storage design.

8. Medium: maintain tempdb deliberately.

   There are 8 equally sized 8.0 MB data files, each with 64.0 MB growth, on a 16-logical-CPU server. The supplied rule recommends starting with 8 files for more than 8 CPUs, so the file count and equal sizing are aligned. The files are extremely small for sustained workloads; pre-size them based on observed peak usage rather than repeatedly growing from 8 MB. The log is also 8 MB with 64 MB growth. No uneven sizing or mixed growth setting is shown.

9. Medium: improve encryption and privilege controls.

   Six of seven reported connections are encrypted shared-memory sessions. Ignore shared memory for network-encryption risk as directed. The one unencrypted connection is SQLTELEMETRY using shared memory; this is minimal risk because it is local telemetry and user data is not exposed. For remote clients, require TLS with a certificate, Force Encryption where appropriate, and Encrypt=True in client connection strings. One sysadmin member, RockyPC\k_a_f, is a reasonable count; nevertheless audit whether that login requires permanent sysadmin and replace broad privilege with least-privilege roles where possible.

Performance and resource health

Wait statistics

These three waits account for approximately 99.26% of the supplied total wait time. No CPU wait profile, runnable-task history, or user-facing latency data was supplied, so CPU starvation cannot be concluded.

I/O

SQLStormOltp is the clearest I/O candidate: 15,008 reads, 932 MB read, and 20 ms average latency. WideWorldImporters WWI_UserData reports 9 ms average read latency. SQLStorm reports 4 ms, while the remaining listed row files generally report 1–2 ms. The observation window is only since the 37.4-hour startup; validate against longer-term workload telemetry before changing storage.

Memory and grants

• Lock Manager cache is 1.38 MB, far below the critical threshold of 20–25% of total SQL Server memory. No critical lock-manager condition is present.
• No sessions are waiting on RESOURCE_SEMAPHORE. The reported grants are small: 1,192 KB requested/granted for session 65 and 1,024 KB for session 52. Session 65 used 32 KB and session 52 used 0 KB at capture time; this indicates possible over-granting for those statements but not grant starvation.
• The largest listed clerk is MEMORYCLERK_SOSNODE at 78.40 MB; the supplied clerk breakdown does not show OBJECTSTORE_LOCK_MANAGER. Therefore the required lock-manager percentage calculation is unavailable, but the direct lock-manager cache measurement is low.
• Buffer-pool entries are small, with the largest shown 6.91 MB unknown and 3.56 MB for SQLStormOltp and SQLStormCodeReview. Because committed target memory is only 5,762 MB and the observation followed a restart, this is not sufficient evidence of buffer-pool shrinkage. The red-flag condition of low buffer pool plus high lock-manager memory is not met.

Configuration optimizations

Index optimization and plan cache

No high-impact missing indexes were detected across all databases. Do not add indexes based solely on this report. Run the Index Tuning goal for each workload database if query-level evidence is needed, especially SQLStormOltp and WideWorldImporters.

The input supplies no table row counts, key widths, included columns, existing-index sizes, usage statistics, or index operational statistics. Consequently, no index add, drop, or modification is recommended and no storage estimate can be responsibly produced. Net storage change from index recommendations is therefore 0 MB for this report, meaning no index change was proposed—not that the databases contain no index opportunity.

The supplied plan-cache statements are mostly health-report queries with one execution each. No huge-table scan, ROWLOCK hint, disabled page/row locks, runaway memory grant, or plan with spills is identified in the available text. Those patterns should be checked during the Fix Deadlocks and Index Tuning analyses.

Query Store optimization

Query Store is READ_WRITE for all listed writable databases. The benchmark databases are physically read-only and therefore show actual READ_ONLY; this is consistent with their state and should not be treated as a Query Store configuration failure.

Database integrity and recovery maintenance

CHECKDB status

All supplied CHECKDB results except none are current by normal weekly maintenance standards. The 1900-01-01 values are sentinel values and should be treated as missing. DigitsSolver has a reported last run of 2026-04-12 18:33 and 168 days since check. Run the supplied CHECKDB script immediately, capture output, and schedule recurring checks. CHECKDB on production-sized databases should normally run against a restored copy where possible to reduce production impact.

Backup status

WideWorldImporters has a full backup dated 2026-07-17, 72 days old; SQLStorm and tpch10 have full backups dated 2026-09-13, 14 days old. tpch has a full backup dated 2025-11-22, 309 days old. Full-recovery databases with no log backups recorded include model, DigitsSolver, tpcc, tpch, CRM, tpch10, PhotoCleanup, TunerBench, tpch10_bench_pristine, and DeadlockDemo. Create and test backup chains according to recovery objectives.

VLF health

No database exceeds roughly 1,000 VLFs. tpcc has the highest supplied count at 233, followed by tpch at 168. VLF rebuild is not recommended from this snapshot. If a future rebuild is required, use a deliberate log-size change with appropriately sized fixed growth increments; do not use repeated shrink/grow cycles.

Security enhancements

• Shared-memory sessions are local and should be ignored for network-encryption risk. The SQLTELEMETRY shared-memory connection is minimal risk under the supplied condition that user data is not exposed.
• Require encryption for remote application connections using a properly trusted TLS certificate, SQL Server Configuration Manager Force Encryption where required, and client connection settings such as Encrypt=True. The certificate provisioning and endpoint inventory are not supplied, so no runnable T-SQL change is included.
• Audit RockyPC\k_a_f for permanent sysadmin necessity. One member is not excessive, but least privilege still applies.
• Integrated Security Only is False, so SQL authentication is available. Review SQL logins, disabled accounts, password policies, and ownership chaining according to the application inventory; those details were not supplied.

Concurrency and deadlocks

Seven deadlocks are confirmed. Sessions 65 and 64 each hold small numbers of OBJECT, PAGE, KEY, and DATABASE locks at capture time; this is not enough to identify a blocking chain. The provided session 65 text begins a transaction, selects joined city/state/country data, updates Application.StateProvinces, and commits. Keep transactions short, access shared objects in a consistent order, avoid user interaction inside transactions, and ensure predicates are supported by appropriate indexes after the Index Tuning analysis.

Run the Fix Deadlocks goal with the complete XML graphs. If lock memory later exceeds 20–25% of total SQL Server memory, investigate immediately; the supplied 1.38 MB cache does not approach that condition. As an emergency-only measure for excessive lock-manager memory, the supplied remediation is DBCC FREESYSTEMCACHE ('Lock Manager : Node 0'); it is not recommended for the current snapshot and is intentionally not included as a change script.

Operational best practices

• Record a longer observation period after the next restart because wait, plan, index, and usage statistics currently cover only 37.4 hours.
• Monitor SQL compilations/sec, batch requests/sec, single-use plan ratio, Query Store persistence, log reuse wait descriptions, deadlock count, file-growth events, and SQLStormOltp read latency.
• Use fixed-size file growth, pre-size tempdb and database files from measured peak usage, and keep data files equally sized where proportional fill is intended.
• Review the Developer Edition deployment restriction before any production use. Developer Edition is for development and test; the input separately reports Enterprise engine capabilities, which do not change the licensing restriction.
• Instant File Initialization is enabled for NT Service\MSSQLSERVER. No IFI change is required. IFI accelerates data-file growth and restores; transaction-log files never use IFI.

Scripts

Enable automatic statistics for databases where both statistics options are disabled

USE [master];
GO
ALTER DATABASE [SQLStorm] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [SQLStormCodeReview] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormCodeReview] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [SQLStormOltp] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormOltp] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [SQLStormQueryTuner] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormQueryTuner] SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [SQLStormRealistic] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormRealistic] SET AUTO_UPDATE_STATISTICS ON;
GO

Replace percentage log autogrowth with fixed increments

USE [master];
GO
ALTER DATABASE [SQLStorm]
MODIFY FILE (NAME = N'SQLStorm_log', FILEGROWTH = 256MB);
ALTER DATABASE [SQLStormCodeReview]
MODIFY FILE (NAME = N'SQLStorm_log', FILEGROWTH = 64MB);
ALTER DATABASE [SQLStormOltp]
MODIFY FILE (NAME = N'SQLStorm_log', FILEGROWTH = 64MB);
ALTER DATABASE [SQLStormQueryTuner]
MODIFY FILE (NAME = N'SQLStorm_log', FILEGROWTH = 64MB);
ALTER DATABASE [SQLStormRealistic]
MODIFY FILE (NAME = N'SQLStorm_log', FILEGROWTH = 64MB);
ALTER DATABASE [tpch10]
MODIFY FILE (NAME = N'tpch10_log', FILEGROWTH = 64MB);
GO

Run CHECKDB for databases with missing or stale reported results

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

Supporting scripts (not part of the change)

No supporting diagnostic, rollback, validation, monitoring, or measurement scripts are included. The report intentionally separates change scripts from non-change tooling.