Server Health Report – RockyPC – 2026-05-31

Executive summary

Detailed prioritized recommendations

The recommendations below are ordered by operational risk first, then by performance benefit, then by hardening and maintainability.

1) Performance improvements

Priority 1 — Tune the highest-cost analytical queries in SQLStorm

• The workload is dominated by large CTE-heavy reporting queries with Execution Count = 1, very high logical reads, and large memory grants.
• Most expensive examples:
  • EC28C61EF14FD8A3: ~2,246 seconds avg CPU, 17.8M logical reads.
  • 558D53CC7558B81F: ~339 seconds avg CPU, 4.2M logical reads, 38,164 spills.
  • 57CAE2375FFE5085: 281,928 KB grant.
  • F37272E929AEF449: 1,792,816 KB max grant.
• Common anti-patterns visible in the query text:
  • Large fan-out joins across Users, Posts, Votes, Badges, Comments, and PostHistory.
  • COUNT(DISTINCT ...), ranking functions, and wide aggregations across large rowsets.
  • Correlated scalar subqueries against Comments and Votes.
  • LIKE '%' + TagName + '%' pattern in tag matching, which prevents efficient seeks and drives scans.

Confidence: 94%

Priority 2 — Address spill-prone and oversized memory grant plans

• No current RESOURCE_SEMAPHORE waiters exist, so there is no acute memory-grant queueing right now.
• However, several plans request much more memory than they use. Example: session 60 was granted 170,440 KB and used only 28,544 KB.
• The plan for query hash 558D53CC7558B81F spilled 38,164 times, indicating poor cardinality estimation, unsupported join/sort patterns, or missing indexes.
• Oversized grants and spills together suggest unstable memory grant quality, not server-wide memory starvation.

Actions:

• Create supporting indexes on Votes(UserId), Votes(PostId), Badges(UserId), Comments(PostId), and PostHistory(PostId) in SQLStorm.
• Update statistics with full scan for the hottest large tables if data volatility is high.
• Break very wide queries into staged temp tables where it materially improves row estimation and reduces repeated scans.

Confidence: 92%

Priority 3 — Reduce compile overhead and plan cache churn

• Batch Requests/sec: 31,225
• SQL Compilations/sec: 16,650
• Single-use ratio: 82.8%

This is a classic ad hoc workload signature. A large fraction of requests are being compiled rather than reusing stable cached plans.

Actions:

• Enable optimize for ad hoc workloads.
• Parameterize application-generated analytical SQL where possible.
• Favor stored procedures or sp_executesql with parameters instead of unique literal-heavy statements.

Confidence: 95%

Priority 4 — Tempdb is the primary I/O hot area, but storage is currently acceptable

• Tempdb files are well balanced across eight data files.
• Average tempdb latency is about 5 ms for reads and 15 ms for writes.
• Those numbers are not alarming, but tempdb dominates buffer pool usage at ~5.0 GB, which indicates the workload is tempdb-intensive.

Actions:

• Do not add more tempdb files right now; the current file count already matches a common best-practice pattern for 16 schedulers.
• Focus on query rewrites and indexes that reduce sorts, hashes, spills, and worktable usage.
• Consider enabling memory-optimized tempdb metadata only if you observe tempdb metadata contention; no evidence of that is present in the supplied data.

Confidence: 84%

Priority 5 — Parallelism settings are reasonable; do not change blindly

• MAXDOP = 8 on 16 logical CPUs is reasonable.
• cost threshold for parallelism = 30 is also reasonable.
• CXCONSUMER and CXPACKET are present but represent a small share of total waits compared with mostly benign background waits.

Recommendation: keep current parallelism settings unless deeper plan analysis shows excessive parallel branches or skew for the top SQLStorm queries.

Confidence: 86%

2) Configuration optimizations

Enable optimize for ad hoc workloads

• This is the clearest server-level configuration change justified by the data.
• It will reduce wasted cache space from one-time plans and lower pressure from excessive plan cache churn.

Confidence: 97%

Keep current max server memory, but monitor headroom

• Total physical memory: 32.5 GB.
• Max server memory: 23,168 MB.
• Committed SQL memory is only ~9.9 GB, so SQL Server is not currently memory starved.

The current cap leaves reasonable OS headroom on a desktop-class Windows 10 host. No change is required based on supplied evidence.

Confidence: 89%

Enable backup checksum default

• backup checksum default = 0.
• Given the lack of strong backup hygiene, enabling checksums adds validation value with minimal downside.

Confidence: 93%

Consider enabling blocked process threshold for diagnostics

• Current value is 0.
• Because a deadlock has already occurred, setting a modest threshold such as 15 seconds can improve incident visibility without being intrusive.

Confidence: 82%

Remote admin connections

• remote admin connections = 0.
• On a non-clustered developer box this is optional, not urgent.

Recommendation: leave unchanged unless this instance is used for remote support or unattended administration.

Confidence: 72%

Do not flag lock manager memory

• Lock manager cache is 3.02 MB.
• This is nowhere near the critical threshold of 20–25% of total SQL memory.
• There is no red flag involving lock memory versus buffer pool size.

Confidence: 99%

3) Index optimization opportunities

Run comprehensive index tuning on these databases first

1. SQLStorm
2. CRM
3. tpch10
4. WideWorldImporters
5. tpch
6. tpcc

This prioritization is based on alignment between observed heavy plan-cache workload and missing-index patterns. SQLStorm is the highest-value starting point.

Important cautions:

• Consolidate overlapping missing-index requests before creating indexes.
• Validate existing indexes to avoid duplication.
• Prefer covering indexes only where supported by actual workload frequency and row counts.
• Use online index operations if table size and workload justify it; this edition supports that capability.

Recommendation: run the Index Tuning goal for each affected database with missing indexes, especially SQLStorm.

Confidence: 91%

4) Query Store optimization

• Query Store is enabled and writable on all listed user databases. That is good.
• For compat level 160 databases, Query Store can support SQL Server 2022 plan governance features effectively.
• Usage is currently tiny in most databases, which means it is available but not yet collecting much useful history.

Keep Query Store enabled everywhere

No database in the supplied list should have Query Store disabled. Current state is good. Confidence: 98%

Standardize SQLStorm Query Store for analysis depth

• SQLStorm is the most active workload database and should retain enough history to compare pre/post tuning.
• Current SQLStorm settings are acceptable, but can be tightened for more granular diagnostics.

Recommended SQLStorm changes:

• Reduce INTERVAL_LENGTH_MINUTES from 60 to 15 for richer performance baselining during tuning.
• Keep QUERY_CAPTURE_MODE = AUTO unless workload remains mostly one-off; if tuning sessions require broader capture, temporarily use CUSTOM or ALL during controlled testing.
• Retain MAX_STORAGE_SIZE_MB = 1000; current usage is only 20 MB.

Confidence: 87%

Standardize WideWorldImporters settings if it is actively used

• WideWorldImporters is on compatibility level 130, not 160.
• Its Query Store settings differ materially: 50-minute flush interval, 15-minute stats interval, and max plans per query 1000.

Recommendation: if WideWorldImporters is still used for testing, raise compatibility level only after validation and align Query Store settings with the 160 databases. If it is just a sample database, leave as-is and deprioritize.

Confidence: 78%

5) Database integrity maintenance (CHECKDB schedules)

Immediate action required

• This is the most severe technical risk in the report.
• Without regular integrity checks, corruption may remain undetected until restore time or business impact.

Recommended schedule:

• Small or non-production databases: weekly DBCC CHECKDB.
• Production-like user databases with active change: weekly full checks at minimum.
• System databases: at least weekly, and after significant storage or upgrade events.

Confidence: 99%

6) Security enhancements

Connection encryption posture is acceptable

• 9 of 10 visible connections are encrypted.
• The only unencrypted connection is NT SERVICE\SQLTELEMETRY using shared memory for SQLServerCEIP.
• Per guidance, this is minimal risk because it is local shared-memory telemetry and does not expose user data in network transit.
• Shared-memory connections should not be treated as a network encryption problem.

Recommendation: no urgent action is required for the observed connection mix. For future remote/client connectivity, prefer TLS with certificate-based encryption and Force Encryption if this instance will accept network clients.

Confidence: 96%

Sysadmin membership is appropriately constrained

• Only one sysadmin member exists: RockyPC\k_a_f.
• This is consistent with least privilege and is not excessive.

Recommendation: maintain the current low sysadmin count, document administrative use, and avoid adding application or service accounts to sysadmin. Periodically review explicit server-level permissions as well.

Confidence: 98%

Authentication mode and auditing

• Integrated Security Only = No, so mixed authentication is enabled.
• That is not inherently unsafe, but it increases the need for login review and password policy compliance.

Recommendation: if SQL logins are not required, prefer Windows-only authentication. Otherwise audit SQL-authenticated logins, disable unused accounts, and enforce strong password policies.

Confidence: 85%

7) Concurrency issues

Deadlock detected — investigate promptly

• There was 1 deadlock in the past 7 days at 2026-05-31 15:37:34 UTC.
• The supplied graph payload is truncated, so lock holder/waiter resource details cannot be reconstructed reliably from the provided input.

Confidence: 88%

No active lock-memory crisis

• Lock manager cache: 3.02 MB.
• Session lock counts are very small.
• No evidence of runaway lock accumulation, large blocking chains, or lock-memory ballooning is present.

Confidence: 97%

No current RESOURCE_SEMAPHORE waiters

• There is no active memory-grant queueing event at collection time.
• Still, large grants and spills warrant query tuning to prevent future concurrency degradation under higher load.

Confidence: 95%

Emergency lock manager remediation option

No emergency action is currently warranted, but if lock manager memory becomes excessive in the future, the emergency cache-clear option is:

DBCC FREESYSTEMCACHE ('Lock Manager : Node 0')

This should only be used as a controlled emergency measure after investigating the root cause.

Confidence: 93%

8) Operational best practices

Backups require immediate correction

• No full backup recorded: master, model, msdb, DigitsSolver, tpcc, CRM, tpch10.
• FULL recovery with no log backups: model, DigitsSolver, tpcc, tpch, CRM, tpch10.
• WideWorldImporters and SQLStorm have very old full backups.

Recommendations:

• Take immediate full backups of all databases lacking them.
• For databases that do not need point-in-time restore, switch to SIMPLE recovery after business confirmation.
• For databases that must remain in FULL, start regular log backups immediately to establish a valid chain.
• Back up system databases as part of baseline server protection.

Confidence: 99%

Patch posture

• The instance is SQL Server 2022 RTM-GDR build 16.0.1180.1.
• This is not current servicing level by 2026 standards.

Recommendation: plan an update to the latest supported CU/GDR after regression testing, especially to benefit from optimizer, Query Store, and stability fixes.

Confidence: 90%

Edition and feature capability

• This Developer/Enterprise-capable environment can use online index operations, compression, partitioning, and SQL Server 2022 intelligent features.
• Use those features where they simplify maintenance and reduce downtime, but only after validating workload value.

Confidence: 84%

9) Proactive maintenance suggestions

1. Baseline top SQLStorm query hashes in Query Store before and after index and rewrite changes. Confidence: 94%
2. Implement regular index and statistics maintenance, prioritizing heavily queried large tables rather than blanket rebuilds. Confidence: 90%
3. Review compatibility level for WideWorldImporters if it matters operationally; otherwise exclude sample databases from routine tuning focus. Confidence: 76%
4. Capture deadlocks and blocked process reports continuously via Extended Events. Confidence: 89%
5. Track memory grants over time; oversized but underused grants are currently a query-shape problem, not a server-cap problem. Confidence: 91%
6. For query patterns using LIKE '%tag%', consider schema redesign such as normalized tag bridge tables or full-text/search alternatives where appropriate. Confidence: 88%
7. Audit backup success, CHECKDB completion, and Query Store size growth weekly. Confidence: 97%

Scripts

Enable optimize for ad hoc workloads and backup checksum default

Implements the configuration recommendations to reduce single-use plan cache waste and improve 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

Set blocked process threshold to improve blocking and deadlock diagnostics

Implements the concurrency monitoring recommendation.

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

Standardize Query Store settings for SQLStorm for deeper tuning analysis

Implements the Query Store optimization recommendation for the most active workload database.

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

Create prioritized supporting indexes in SQLStorm

Implements the highest-confidence index recommendations aligned to the observed workload and missing-index signals.

USE [SQLStorm];
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_Badges_UserId_INC_Class_Name'
      AND object_id = OBJECT_ID(N'dbo.Badges')
)
BEGIN
    CREATE INDEX IX_Badges_UserId_INC_Class_Name
    ON dbo.Badges (UserId)
    INCLUDE (Class, Name);
END
GO

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

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

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

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

Run DBCC CHECKDB for overdue user databases

Implements the critical integrity recommendation for databases with overdue or missing checks.

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 databases missing backup history

Implements the critical backup remediation recommendation. Update backup paths as needed before execution.

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

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

Implements the backup chain recommendation for FULL recovery databases. Execute only for databases that should remain in FULL recovery.

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

Optional: switch selected databases to SIMPLE recovery if point-in-time restore is not required

Implements the operational recommendation to avoid broken log chains and uncontrolled log growth. Validate recovery requirements before execution.

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

Emergency-only: clear lock manager cache for NUMA node 0 if lock memory becomes excessive

Implements the emergency remediation option referenced in the concurrency guidance. Do not use routinely.

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