Server Health Report – RockyPC – 2026-06-16

Executive Summary

Health Overview

Detailed Prioritized Recommendations

1. Implement immediate integrity validation and recurring CHECKDB jobs.
   • Critical: WideWorldImporters, SQLStorm, tpcc, tpch, CRM, and tpch10 show the sentinel date 1900-01-01, which effectively means no recorded CHECKDB.
   • DigitsSolver is also overdue at 64 days.
   • Recommendation: run full DBCC CHECKDB now, then schedule weekly for all user databases. For smaller development systems, weekly full checks are reasonable.
   • Confidence: 99%
2. Establish a valid backup strategy, especially for FULL recovery databases.
   • No full backups recorded for master, model, msdb, DigitsSolver, tpcc, CRM, and tpch10.
   • tpch, tpcc, DigitsSolver, CRM, tpch10, and model are in FULL recovery without log backups recorded.
   • tpch has 168 VLFs with 149 active and truncation holdup LOG_BACKUP; this is operationally significant even though VLF count itself is not excessive.
   • Recommendation: take immediate full backups; for FULL recovery databases, begin recurring log backups or switch to SIMPLE if point-in-time recovery is not required.
   • Confidence: 98%
3. Patch the SQL Server instance to the latest SQL Server 2022 CU.
   • Lifecycle remains healthy: 🟢 Green.
   • CU currency is separate and currently poor: 🔴 Outdated.
   • Installed Build: 16.0.1180.1; Latest Build: 16.0.4255.1.
   • Status meaning: Outdated means two or more CUs behind; upgrade to the latest CU for stability and security.
   • Honor the provided recommendation: “Upgrade to the latest CU to ensure stability and security.”
   • Confidence: 97%
4. Reduce ad hoc compilation overhead.
   • Batch Requests/sec = 4,611 and SQL Compilations/sec = 4,568 is an unusually high ratio.
   • Plan cache single-use ratio is 31.8%, not extreme in size terms, but the compile rate indicates constant generation of new plans.
   • Recommendation: enable optimize for ad hoc workloads and review applications generating literal-heavy dynamic SQL.
   • Confidence: 95%
5. Investigate parallel query behavior, but do not change MAXDOP blindly.
   • MAXDOP = 8 and cost threshold for parallelism = 30 are reasonable starting values for 16 logical CPUs.
   • CXCONSUMER, CXPACKET, and CXSYNC_PORT together indicate active parallel workload, but not necessarily misconfiguration.
   • Recommendation: focus first on compile reduction, query/index tuning, and current CU adoption before adjusting parallelism settings.
   • Confidence: 83%
6. Review tempdb workload drivers rather than tempdb layout.
   • tempdb has 8 equally sized data files for a 16-CPU server, matching best-practice starting guidance for more than 8 logical CPUs.
   • Growth is fixed at 64 MB and uniform across files, which is good.
   • tempdb is the main I/O hotspot with 17-18 ms average latency across data files; investigate sorts, hashes, spills, version-store usage, and temp table workloads.
   • Confidence: 91%
7. Validate and tune missing index opportunities database by database.
   • At least 17 high-impact missing index requests span 6 databases: CRM, SQLStorm, tpcc, tpch, tpch10, and WideWorldImporters.
   • Some reported “tables” look like constraints or internal object names, so these DMV suggestions must be validated before creation.
   • Recommendation: run a comprehensive Index Tuning analysis for each affected database and consolidate overlapping suggestions.
   • Confidence: 88%
8. Use Query Store as the primary tuning control plane and modernize compatibility where safe.
   • Query Store is enabled and writable in all listed user databases, which is excellent.
   • WideWorldImporters remains at compatibility level 130, so it does not benefit from SQL Server 2022 features like Parameter Sensitive Plan optimization and DOP feedback.
   • Recommendation: test and raise compatibility to 160 for WideWorldImporters if application validation succeeds.
   • Confidence: 90%
9. Maintain current security posture, but do not overreact to the telemetry connection.
   • The only unencrypted session shown is NT SERVICE\SQLTELEMETRY over shared memory.
   • Shared memory is local-only, and telemetry does not expose user data; this is minimal risk and should not be treated as a user-data encryption gap.
   • One sysadmin login is reasonable and aligns with least privilege.
   • Confidence: 96%
10. Address isolated storage configuration risk.
    • WideWorldImporters file WWI_InMemory_Data_1 has autogrowth disabled. If it fills, operations can fail.
    • Recommendation: enable controlled growth or ensure strong proactive capacity monitoring.
    • Confidence: 92%

Performance Improvements

Configuration Optimizations

Index Optimization Opportunities

Query Store Optimization

Database Integrity Maintenance

Security Enhancements

Concurrency and Locking

Operational Best Practices

Proactive Maintenance

Scripts

Enable optimize for ad hoc workloads and backup checksum default to reduce compile waste and strengthen backup validation

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

Run immediate DBCC CHECKDB for overdue user databases

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 system and user databases lacking recorded full backups

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

Begin transaction log backups for FULL recovery databases that need point-in-time recovery

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

Switch selected databases to SIMPLE recovery if point-in-time recovery 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

Enable controlled autogrowth on the WideWorldImporters FILESTREAM container to reduce out-of-space risk

USE [master];
GO
ALTER DATABASE [WideWorldImporters]
MODIFY FILE
(
    NAME = N'WWI_InMemory_Data_1',
    FILEGROWTH = 256MB
);
GO

Validate high-value missing index candidates before implementation

USE [master];
GO
SELECT
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS object_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    CAST((migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS DECIMAL(18,2)) AS impact_score
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
WHERE DB_NAME(mid.database_id) IN (N'CRM', N'SQLStorm', N'tpcc', N'tpch', N'tpch10', N'WideWorldImporters')
ORDER BY impact_score DESC;
GO

Raise WideWorldImporters compatibility level to 160 after application testing to unlock SQL Server 2022 optimizer features

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

Optional emergency command if lock manager memory ever becomes excessive

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