Server Health Report – RockyPC – 2026-05-21

Executive summary

Detailed prioritized recommendations

1. Run overdue or missing CHECKDB validations immediately and schedule recurring integrity checks.
2. Implement consistent full, differential, and log backup routines aligned to recovery models.
3. Validate and tune high-value missing indexes for the six affected databases using a comprehensive index analysis workflow.
4. Enable safe server-wide settings that reduce plan cache waste and improve backup reliability.
5. Standardize Query Store behavior and align compatibility levels where appropriate to SQL Server 2022 features.
6. Monitor parallelism and tempdb-heavy workloads, but avoid aggressive changes because current storage and memory indicators are healthy.
7. Maintain least privilege and continue keeping sysadmin membership minimal.
8. Adopt proactive monitoring for integrity, backup age, waits, and index drift.

1) Performance improvements

Recommendations

1. Do not treat current storage as the bottleneck. There is no evidence that slower disks are driving performance issues. Confidence: 0.96.
2. Keep MAXDOP at 8 for now. This matches common guidance for a 16 logical CPU single-socket system and does not appear obviously mis-set. Confidence: 0.84.
3. Consider increasing cost threshold for parallelism to 40-50 if monitoring continues to show excess parallel plans with modest query cost. Current value 30 is reasonable but still permissive on modern hardware. Confidence: 0.73.
4. Leave tempdb file count as-is unless you observe PAGELATCH contention. Current file balance and latency are good. Confidence: 0.95.
5. Use Query Store and workload capture to identify actual expensive user queries. The current top plan cache entries are diagnostic collection queries, not business workload queries. Confidence: 0.94.

2) Configuration optimizations

Version and edition notes

• This instance is SQL Server 2022 Developer Edition with Enterprise capabilities available for testing and tuning.
• The build is RTM-GDR 16.0.1180.1. Apply current SQL Server 2022 servicing when appropriate to pick up optimizer, Query Store, and engine fixes. Confidence: 0.88.
• WideWorldImporters is still at compatibility level 130, so it is not benefiting from SQL Server 2022 optimizer features such as Parameter Sensitive Plan optimization and DOP feedback.

3) Index optimization opportunities

Highest priority databases

1. SQLStorm — repeated high-impact candidates on dbo.Badges, dbo.PostHistory, dbo.Users, dbo.Votes, and dbo.Comments. Confidence: 0.87.
2. WideWorldImporters — multiple candidates, but object names suggest validation is essential before DDL changes. Also review compatibility-level upgrade potential first. Confidence: 0.79.
3. CRM — multiple high-impact candidates, several with suspicious object naming; likely a strong candidate for full index and schema review. Confidence: 0.80.
4. tpch, tpch10, tpcc — likely benchmark or test-style workloads, but still worth tuning if these databases matter operationally. Confidence: 0.70.

Recommendations

• Consolidate overlapping missing index requests before any creation.
• Validate actual table names, row counts, workload patterns, and duplication against existing indexes.
• Favor tested, workload-backed nonclustered indexes over direct DMV script generation.
• Use Query Store to confirm which query shapes would benefit most before adding write-amplifying indexes.

4) Query Store optimization

Recommendations

1. Keep Query Store enabled on all user databases. No database in scope needs Query Store to be enabled because it already is. Confidence: 0.99.
2. Standardize WideWorldImporters Query Store settings unless its workload specifically requires the current nonstandard profile. Lowering MAX_PLANS_PER_QUERY from 1000 to a more typical range such as 200 can reduce unnecessary plan accumulation. Confidence: 0.81.
3. After testing, raise WideWorldImporters compatibility level to 160 to unlock SQL Server 2022 optimizer features. Confidence: 0.89.
4. Use Query Store to drive plan stabilization before making broad parallelism or indexing changes. Confidence: 0.92.

5) Database integrity maintenance

Recommendations

1. Run DBCC CHECKDB immediately for all user databases listed above. Confidence: 0.99.
2. Adopt a recurring integrity schedule:
   • Production or important databases: weekly full DBCC CHECKDB.
   • Very large databases: at least weekly or structured rotation with documented exception handling.
   • After storage issues, forced shutdowns, or restore operations: run integrity checks promptly.
3. Capture and retain CHECKDB history centrally so “never run” and overdue conditions are visible. Confidence: 0.94.

6) Security enhancements

Recommendations

1. No urgent remediation is required for the observed unencrypted shared-memory telemetry session. Confidence: 0.98.
2. For any TCP/IP client access, require encryption. Use TLS certificates, Encrypt=True in connection strings, and Force Encryption at the SQL Server network layer if remote connectivity exists. Confidence: 0.90.
3. Preserve minimal sysadmin membership. One member is acceptable; review periodically and prefer delegated fixed-database or custom server roles where possible. Confidence: 0.96.
4. Keep high-risk surface area features disabled unless explicitly needed. Current posture here is generally good. Confidence: 0.93.

7) Concurrency issues

Recommendations

1. No deadlock emergency action is needed. Confidence: 0.99.
2. If blocking becomes visible later, use Extended Events and Query Store together rather than enabling broad intrusive tracing. Confidence: 0.88.
3. Keep monitoring tempdb and parallel query patterns for latch or exchange-related regressions as workload grows. Confidence: 0.81.

8) Operational best practices

Recommendations

1. Back up system databases immediately: master, msdb, and model. Confidence: 0.99.
2. For FULL recovery databases, either start log backups or change recovery model intentionally. Databases in FULL without log backups are operationally inconsistent. Confidence: 0.98.
3. Standardize retention, verification, and restore testing. A backup that has not been restored in testing is not proven. Confidence: 0.95.
4. Patch the SQL Server 2022 instance to a current supported servicing level after validation. Confidence: 0.88.

9) Proactive maintenance suggestions

1. Create a weekly health review covering waits, backup age, CHECKDB age, Query Store size, and failed jobs. Confidence: 0.93.
2. Track plan cache behavior after enabling ad hoc optimization to confirm reduced single-use plan waste. Confidence: 0.82.
3. Review compatibility level 160 adoption for databases still below SQL Server 2022 capability, especially WideWorldImporters. Confidence: 0.89.
4. Use Query Store baselines before and after index changes so regressions can be detected quickly. Confidence: 0.91.
5. Maintain documented standards for recovery model selection, backup cadence, CHECKDB cadence, and index review criteria. Confidence: 0.94.

Scripts

Enable safe server-wide configuration improvements for plan cache efficiency, backup reliability, and DAC access

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;
EXEC sys.sp_configure N'backup compression default', 1;
EXEC sys.sp_configure N'remote admin connections', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', 0;
RECONFIGURE;
GO

Optionally raise cost threshold for parallelism to reduce low-value parallel plans

USE [master];
GO
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', 50;
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', 0;
RECONFIGURE;
GO

Run immediate DBCC CHECKDB against all overdue user databases identified in this assessment

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

Review current backup posture and age for all databases to implement the backup recommendations

USE [msdb];
GO
WITH backup_summary AS
(
    SELECT
        d.name,
        d.recovery_model_desc,
        MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full_backup,
        MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff_backup,
        MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log_backup
    FROM master.sys.databases AS d
    LEFT JOIN msdb.dbo.backupset AS b
        ON b.database_name = d.name
    GROUP BY d.name, d.recovery_model_desc
)
SELECT
    name,
    recovery_model_desc,
    last_full_backup,
    last_diff_backup,
    last_log_backup,
    DATEDIFF(DAY, last_full_backup, SYSDATETIME()) AS days_since_full,
    DATEDIFF(HOUR, last_log_backup, SYSDATETIME()) AS hours_since_log
FROM backup_summary
ORDER BY name;
GO

Standardize Query Store settings for WideWorldImporters and prepare for SQL Server 2022 feature usage

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

Collect validated missing index candidates for CRM to support the Index Tuning recommendation

USE [CRM];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Collect validated missing index candidates for SQLStorm to support the Index Tuning recommendation

USE [SQLStorm];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Collect validated missing index candidates for tpcc to support the Index Tuning recommendation

USE [tpcc];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Collect validated missing index candidates for tpch to support the Index Tuning recommendation

USE [tpch];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Collect validated missing index candidates for tpch10 to support the Index Tuning recommendation

USE [tpch10];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Collect validated missing index candidates for WideWorldImporters to support the Index Tuning recommendation

USE [WideWorldImporters];
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,
    CONVERT(decimal(18,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS impact_score
FROM sys.dm_db_missing_index_details AS mid
JOIN sys.dm_db_missing_index_groups AS mig
    ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY impact_score DESC;
GO

Audit current user connection encryption state while ignoring shared memory sessions

USE [master];
GO
SELECT
    s.session_id,
    c.net_transport,
    c.encrypt_option,
    c.auth_scheme,
    s.login_name,
    s.host_name,
    s.program_name,
    c.client_net_address,
    c.local_net_address,
    c.local_tcp_port
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE s.is_user_process = 1
  AND c.net_transport <> N'Shared memory'
ORDER BY c.encrypt_option, s.session_id;
GO

Audit sysadmin membership to support ongoing least-privilege review

USE [master];
GO
SELECT
    sp.name AS login_name,
    sp.type_desc,
    sp.is_disabled
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS sp
    ON srm.member_principal_id = sp.principal_id
JOIN sys.server_principals AS rp
    ON srm.role_principal_id = rp.principal_id
WHERE rp.name = N'sysadmin'
ORDER BY sp.name;
GO