Server Health Report – RockyPC – 2026-06-14

Executive Summary

Top Priorities

1. Patch SQL Server 2022 to a current CU.
   Confidence: High
   • Patch status is explicitly 🔴 Red.
   • Current build is 395 days old relative to the supplied reference.
   • On SQL Server 2022, cumulative updates can materially improve optimizer, Query Store, and engine stability.
2. Establish immediate and recurring DBCC CHECKDB coverage for all user databases.
   Confidence: High
   • Most databases show the default 1900-01-01 pattern, effectively meaning no recorded CHECKDB.
   • DigitsSolver is also overdue at 63 days.
   • This is a material integrity risk.
3. Fix backup and recovery strategy mismatches.
   Confidence: High
   • Several databases in FULL recovery have no log backups recorded.
   • Some databases have no full backups recorded at all.
   • tpch shows log truncation hold-up LOG_BACKUP, confirming missing log backup activity is operationally relevant.
4. Reduce plan cache pollution and compilation churn.
   Confidence: High
   • Single-use plans are 51.4% of plan count and ~97 MB of cache.
   • Compilations/sec (9,521) are unusually close to batch requests/sec (11,367).
   • optimize for ad hoc workloads is disabled and should be enabled.
5. Review WideWorldImporters compatibility level and In-Memory file autogrowth risk.
   Confidence: Medium
   • WideWorldImporters remains at compatibility level 130 on a SQL Server 2022 instance.
   • The FILESTREAM/In-Memory file WWI_InMemory_Data_1 has autogrowth disabled, which can cause an outage if capacity is exhausted.

Health Overview

Performance & Capacity

Observed bottlenecks

• No major active bottleneck is visible in waits, I/O, or memory grants.
• Top waits are QDS_PERSIST_TASK_MAIN_LOOP_SLEEP, PWAIT_DIRECTLOGCONSUMER_GETNEXT, and QDS_ASYNC_QUEUE. These are background/idle-style waits and do not indicate a production pressure point by themselves.
• I/O latency is excellent, generally 0-4 ms.
• Committed memory is only ~1 GB versus target ~7 GB, indicating the engine is not under memory stress.

Plan cache and compilation efficiency

• Plan cache pollution is notable: 722 of 1,404 plans are single-use (51.4%).
• SQL Compilations/sec at 9,521 versus Batch Requests/sec at 11,367 suggests frequent compilation overhead.
• optimize for ad hoc workloads is disabled and is recommended here.
• Top plan-cache queries appear dominated by metadata/telemetry style queries rather than user workload memory-grant or spill problems.

Memory posture

• RESOURCE_SEMAPHORE: none.
• Top memory grant: only 1,024 KB.
• Lock manager cache: 1.47 MB, far below the 20-25% critical threshold.
• Buffer pool: ~179 MB, consistent with low current working-set demand, not with memory pressure.
• No red flag combination of high lock-manager memory plus shrinking buffer pool is present.

I/O findings

• All listed files show healthy latency characteristics.
• No storage subsystem problem is indicated from the provided file statistics.
• Because I/O is already strong, priority should stay with patching, integrity, and operational maintenance rather than storage tuning.

Configuration

Confidence: High for optimize for ad hoc workloads and backup checksum recommendation; Medium for remote DAC as an operational preference.

Query Store

• Query Store is enabled and writable on all listed user databases.
• Current settings are generally healthy: AUTO capture, AUTO cleanup, sufficient max size, and compatibility level 160 on most databases.
• WideWorldImporters is an exception: compatibility level 130, Query Store READ_WRITE, flush interval 50 minutes, stats interval 15 minutes, max plans per query 1000.

Recommendations

• Keep Query Store enabled everywhere.
• After regression testing, consider moving WideWorldImporters to compatibility level 160 to use SQL Server 2022 features such as Parameter Sensitive Plan optimization, IQP enhancements, and DOP feedback.
• Reduce WideWorldImporters Query Store MAX_PLANS_PER_QUERY from 1000 to a lower operationally safer value such as 200 unless there is a specific reason to retain many plan variants.
• On low-volume databases with only 1 MB used, no storage pressure is present.

Confidence: Medium. Compatibility changes should always be tested for plan regressions.

Integrity, CHECKDB & Backups

Integrity risk

Backup and recovery findings

• No full backup recorded: master, model, msdb, DigitsSolver, tpcc, CRM, tpch10.
• FULL recovery but no log backups recorded: model, DigitsSolver, tpcc, tpch, CRM, tpch10.
• tpch has LOG_BACKUP as truncation holdup and 9,164 MB active log out of 10,504 MB, which is expected when log backups are not occurring.
• WideWorldImporters last full backup is extremely old (2022-10-07).
• Backup chain quality is insufficient for reliable restore assurance across much of the instance.

Recommended maintenance policy

• Run DBCC CHECKDB for all user databases immediately.
• Then establish a recurring schedule:
  • Critical OLTP / important user DBs: weekly
  • Lower-tier dev/test: weekly or at minimum monthly
• Align recovery model with business need:
  • If point-in-time recovery is required, keep FULL and start regular log backups.
  • If not required, change those databases to SIMPLE after confirming requirements.

Confidence: High.

Security

Connection encryption

• 2 total user connections were observed.
• 1 encrypted, 1 unencrypted.
• The unencrypted session is SQLServerCEIP over shared memory using NT SERVICE\SQLTELEMETRY.
• Per risk context, this is minimal risk: shared memory is local-only and telemetry does not expose user data.

Recommendations

• No urgent remediation is required for the current sampled unencrypted shared-memory telemetry connection.
• For any non-shared-memory client connectivity, standardize on encrypted connections using Encrypt=True.
• Consider Force Encryption with a proper server certificate if this instance will be accessed remotely over TCP.

Privileged access

• Only one sysadmin member exists: RockyPC\k_a_f.
• This is a reasonable and low-risk posture.
• Continue least-privilege practice and periodic review of local administrator and SQL sysadmin overlap.

Confidence: High.

Concurrency & Locking

• No deadlocks detected in the last 7 days.
• No active locks were present at collection time.
• Lock manager memory is very low at 1.47 MB.
• No blocking tree or lock-memory emergency exists from the supplied evidence.

Assessment

• No indication of runaway plans causing excessive locks or memory grants.
• No need for emergency remediation such as DBCC FREESYSTEMCACHE ('Lock Manager : Node 0') based on current data.
• If future blocking arises, prioritize finding long transactions, large scans, and explicit locking hints before considering cache flush actions.

Confidence: High.

Storage, Files & Logs

tempdb

• 16 logical CPUs with 8 tempdb data files is an appropriate starting configuration.
• All 8 data files are equally sized at 8 MB with identical fixed growth of 64 MB.
• This is good from a proportional-fill standpoint.
• No uneven file size or mixed autogrowth problem is present.

Database file autogrowth

• Only one explicit file-level risk is reported:
  • WideWorldImporters - WWI_InMemory_Data_1 (FILESTREAM): autogrowth disabled.
• If this filegroup is actively used by memory-optimized objects, exhaustion can produce hard failures.

Transaction log health

• No database exceeds the rough 1,000 VLF risk threshold.
• tpcc has 233 VLFs and tpch10 has 170; not critical, but these logs are large relative to active usage.
• tpch has 168 VLFs and most of the log is active due to LOG_BACKUP.
• WideWorldImporters truncation holdup is ACTIVE_TRANSACTION; investigate long-running work if it persists.

Instant File Initialization

• IFI is enabled for NT Service\MSSQLSERVER.
• This is good and requires no change.

Confidence: High.

Detailed Prioritized Recommendations

1. Patch SQL Server to a current SQL Server 2022 CU.
   • Priority: Critical
   • Reason: Patch posture is explicitly red and older than 1 year.
   • Benefit: Stability, security hardening, optimizer fixes, Query Store reliability, and supportability.
   • Confidence: High
2. Run DBCC CHECKDB for every user database immediately and schedule it weekly.
   • Priority: Critical
   • Reason: Most databases show no valid CHECKDB history.
   • Benefit: Integrity assurance and earlier corruption detection.
   • Confidence: High
3. Implement a real backup strategy and align recovery models with restore requirements.
   • Priority: Critical
   • Reason: Missing full backups and FULL recovery without log backups create restore risk and log growth issues.
   • Benefit: Recoverability, controlled log reuse, and operational resilience.
   • Confidence: High
4. Enable optimize for ad hoc workloads.
   • Priority: High
   • Reason: 51.4% single-use plan ratio and very high compilation rate.
   • Benefit: Reduced plan-cache waste and lower compile overhead.
   • Confidence: High
5. Enable backup checksum by default.
   • Priority: High
   • Reason: Improves backup validation safety with minimal downside.
   • Benefit: Better corruption detection during backup operations.
   • Confidence: High
6. Test and raise WideWorldImporters compatibility level from 130 to 160.
   • Priority: Medium
   • Reason: Current server is SQL Server 2022, but this database is not using the newest optimizer behaviors.
   • Benefit: Access to SQL Server 2022 intelligent query processing features.
   • Confidence: Medium
7. Reduce WideWorldImporters Query Store max plans per query unless there is a known need for 1000.
   • Priority: Medium
   • Reason: Helps limit unnecessary Query Store growth and plan clutter.
   • Benefit: Cleaner plan history and lower metadata overhead.
   • Confidence: Medium
8. Review the autogrowth-disabled In-Memory FILESTREAM container.
   • Priority: Medium
   • Reason: Growth disabled can become an outage condition if capacity is reached.
   • Benefit: Prevents hard space exhaustion for memory-optimized checkpoint files.
   • Confidence: Medium
9. Investigate persistent ACTIVE_TRANSACTION in WideWorldImporters log truncation if it recurs.
   • Priority: Medium
   • Reason: If sustained, it prevents log reuse.
   • Benefit: Avoids unnecessary log growth and recovery delays.
   • Confidence: Medium
10. Keep current MAXDOP, cost threshold, tempdb layout, sysadmin count, IFI, and Query Store enablement.
    • Priority: Informational
    • Reason: These areas are currently healthy.
    • Benefit: Prevents unnecessary change risk.
    • Confidence: High

Scripts

Enable optimize for ad hoc workloads to reduce single-use plan cache waste

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 for safer backup operations

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

Run DBCC CHECKDB for all user databases that are overdue or missing history

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

Raise WideWorldImporters compatibility level to 160 and normalize Query Store settings after testing

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

Investigate log truncation blockers and active transactions in WideWorldImporters

USE [WideWorldImporters];
GO
DBCC OPENTRAN;
GO
SELECT
    dt.database_transaction_begin_time,
    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    st.session_id,
    es.login_name,
    es.host_name,
    es.program_name,
    er.status,
    er.command,
    er.wait_type,
    er.blocking_session_id,
    SUBSTRING(txt.text,
        (er.statement_start_offset / 2) + 1,
        ((CASE er.statement_end_offset
            WHEN -1 THEN DATALENGTH(txt.text)
            ELSE er.statement_end_offset
          END - er.statement_start_offset) / 2) + 1) AS running_statement
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_database_transactions dt
    ON at.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_tran_session_transactions st
    ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions es
    ON st.session_id = es.session_id
LEFT JOIN sys.dm_exec_requests er
    ON st.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) txt
WHERE dt.database_id = DB_ID();
GO