Server Health Report – RockyPC – 2026-05-16

Executive Summary

This report analyzes the overall health of RockyPC, a single-instance SQL Server 2022 Developer Edition running on a 16-core Windows 10 workstation with 32 GB RAM. The instance hosts several databases including SQLStorm, WideWorldImporters, tpch, tpch10, tpcc, CRM, and DigitsSolver.

The server has been online for less than one day, so wait statistics and I/O metrics reflect a short observation window. Despite this, several significant operational, integrity, and configuration concerns were identified. The most urgent findings are:

Top Priorities

1. Performance Improvements

2. Configuration Optimizations

3. Index Optimization Opportunities

4. Query Store Optimization

5. Database Integrity Maintenance (CHECKDB)

6. Security Enhancements

7. Concurrency Issues

8. Backup & Recovery

9. Operational Best Practices

Wait Analysis Reference

Interpretation: The top three waits are entirely driven by Query Store background activity. The overwhelming dominance of QDS waits (97%) in the wait statistics immediately after server startup is expected — QDS initializes its log consumer threads, persistence tasks, and async queues on startup. These waits will normalize as user workload accumulates. No performance bottleneck is indicated.

I/O Analysis Reference

All files are performing at sub-millisecond to 1 ms latency, consistent with NVMe SSD or high-performance local storage. No I/O tuning is required.

Scripts

The following scripts implement the recommendations in this report. Each script is a complete, runnable T-SQL batch. Review and test in a non-production environment before execution. Scripts that require a service restart are clearly marked.

1. Enable Optimize for Ad Hoc Workloads (Recommendation 2.1)

-- Enable 'optimize for ad hoc workloads' to reduce single-use plan cache bloat.
-- This is a dynamic setting; no service restart required.
-- Applies to: RockyPC (server-wide)

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Verify
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';
GO

2. Enable Backup Compression and Checksum Defaults (Recommendations 2.2 and 2.3)

-- Enable backup compression default and backup checksum default.
-- These are dynamic settings; no service restart required.
-- Applies to: RockyPC (server-wide)

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Enable backup compression (reduces backup size by ~50-70%)
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Enable backup checksum (validates integrity during backup and restore)
EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Verify
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN ('backup compression default', 'backup checksum default');
GO

3. Enable Blocked Process Threshold (Recommendations 2.7 / 7.2)

-- Enable blocked process threshold monitoring (fires when blocking exceeds 5 seconds).
-- This is a dynamic setting; no service restart required.
-- Captures blocked process reports via system health extended event session.
-- Applies to: RockyPC (server-wide)

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'blocked process threshold (s)', 5;
RECONFIGURE WITH OVERRIDE;
GO

-- Verify
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name = 'blocked process threshold (s)';
GO

4. Enable Memory-Optimized TempDB Metadata (Recommendation 1.1)

-- Enable memory-optimized tempdb metadata to reduce system table latch contention.
-- *** IMPORTANT: Requires SQL Server service restart to take effect. ***
-- Plan a maintenance window before applying this change.
-- Applies to: RockyPC (server-wide, SQL Server 2019+ Enterprise/Developer)

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'tempdb metadata memory-optimized', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- Verify current setting (change takes effect after service restart)
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name = 'tempdb metadata memory-optimized';
GO

-- After restarting the SQL Server service, verify with:
-- SELECT CASE WHEN OBJECTPROPERTY(OBJECT_ID('tempdb.sys.sysrowsets'),'TableIsMemoryOptimized') = 1
--             THEN 'Memory-Optimized TempDB Metadata: ENABLED'
--             ELSE 'Memory-Optimized TempDB Metadata: DISABLED'
--        END AS status;
GO

5. Upgrade WideWorldImporters Compatibility Level to 160 (Recommendation 2.4)

-- Upgrade WideWorldImporters database compatibility level from 130 to 160.
-- This enables SQL Server 2022 Intelligent Query Processing features.
-- *** TEST IN STAGING FIRST — query plan changes may affect performance. ***
-- Uses Query Store to capture plan regressions (Query Store is already enabled).

USE master;
GO

-- Step 1: Ensure Query Store is capturing baseline plans BEFORE upgrade
--         (already READ_WRITE, so baseline is being captured)

-- Step 2: Upgrade compatibility level
ALTER DATABASE [WideWorldImporters]
SET COMPATIBILITY_LEVEL = 160;
GO

-- Step 3: Verify
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'WideWorldImporters';
GO

-- Step 4: If plan regressions occur after upgrade, use Query Store
--         to force the old plan while investigating:
-- USE [WideWorldImporters];
-- EXEC sp_query_store_force_plan @query_id = , @plan_id = ;
GO

6. Standardize WideWorldImporters Query Store Settings (Recommendation 4.1)

-- Standardize WideWorldImporters Query Store configuration to match other databases.
-- Changes flush interval to 15 min, stats interval to 60 min,
-- max plans per query to 200, and max storage to 1000 MB.

USE master;
GO

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE = ON
(
    OPERATION_MODE            = READ_WRITE,
    CLEANUP_POLICY            = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,      -- 15 minutes (was 50 min = 3000 sec)
    INTERVAL_LENGTH_MINUTES   = 60,         -- Stats collection interval (was 15 min)
    MAX_STORAGE_SIZE_MB       = 1000,       -- Storage ceiling (was 500 MB)
    QUERY_CAPTURE_MODE        = AUTO,
    MAX_PLANS_PER_QUERY       = 200,        -- Max plans (was 1000)
    SIZE_BASED_CLEANUP_MODE   = AUTO
);
GO

-- Verify
SELECT
    d.name,
    qs.desired_state_desc,
    qs.actual_state_desc,
    qs.data_flush_interval_seconds / 60.0 AS flush_interval_min,
    qs.query_capture_mode_desc,
    qs.max_storage_size_mb,
    qs.stale_query_threshold_days,
    qs.max_plans_per_query
FROM sys.databases d
JOIN sys.database_query_store_options qs ON d.database_id = qs.database_id
WHERE d.name = 'WideWorldImporters';
GO

7. Run DBCC CHECKDB on All User Databases (Recommendation 5.1 — Quick Pass)

-- Run DBCC CHECKDB with PHYSICAL_ONLY on all user databases for rapid corruption detection.
-- PHYSICAL_ONLY reduces duration significantly on large databases.
-- Run the full check (without PHYSICAL_ONLY) during off-hours on a schedule.
-- *** This may take several minutes per database depending on size. ***

USE master;
GO

PRINT '== Starting DBCC CHECKDB (PHYSICAL_ONLY) for all user databases ==';
PRINT 'Start time: ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([WideWorldImporters]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'WideWorldImporters: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([SQLStorm]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'SQLStorm: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([tpcc]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'tpcc: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([tpch]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'tpch: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([CRM]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'CRM: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([tpch10]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'tpch10: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

DBCC CHECKDB ([DigitsSolver]) WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'DigitsSolver: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

-- System databases
DBCC CHECKDB ([master])  WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB ([model])   WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB ([msdb])    WITH PHYSICAL_ONLY, NO_INFOMSGS;
GO
PRINT 'System databases: CHECKDB complete at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO

-- Verify last CHECKDB dates after completion
SELECT
    d.name AS database_name,
    DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS last_good_checkdb,
    DATEDIFF(DAY, CAST(DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS DATETIME), GETDATE()) AS days_since_checkdb
FROM sys.databases d
WHERE d.database_id > 0
  AND d.state_desc = 'ONLINE'
ORDER BY days_since_checkdb DESC;
GO

8. Schedule Weekly CHECKDB via SQL Agent Job (Recommendation 5.1)

-- Create a SQL Server Agent job to run full DBCC CHECKDB on all user databases weekly.
-- Scheduled for Sunday at 2:00 AM (adjust as needed).
-- Requires SQL Server Agent to be running.

USE msdb;
GO

-- Drop existing job if it exists (for re-runability)
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'Weekly DBCC CHECKDB - All Databases')
    EXEC msdb.dbo.sp_delete_job @job_name = N'Weekly DBCC CHECKDB - All Databases';
GO

EXEC msdb.dbo.sp_add_job
    @job_name = N'Weekly DBCC CHECKDB - All Databases',
    @enabled = 1,
    @description = N'Runs DBCC CHECKDB on all online user databases weekly to verify integrity.',
    @category_name = N'Database Maintenance';
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Weekly DBCC CHECKDB - All Databases',
    @step_name = N'Run CHECKDB on all databases',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @dbname SYSNAME;
DECLARE @sql NVARCHAR(500);
DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE state_desc = ''ONLINE''
      AND name NOT IN (''tempdb'')
    ORDER BY name;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N''DBCC CHECKDB(['' + @dbname + N'']) WITH NO_INFOMSGS, ALL_ERRORMSGS'';
    PRINT ''Running CHECKDB on: '' + @dbname + '' at '' + CONVERT(VARCHAR, GETDATE(), 120);
    EXEC sp_executesql @sql;
    FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
',
    @on_success_action = 1,
    @on_fail_action = 2;
GO

-- Schedule: Sunday at 02:00 AM
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Weekly Sunday 2AM',
    @freq_type = 8,            -- Weekly
    @freq_interval = 1,        -- Sunday
    @freq_recurrence_factor = 1,
    @active_start_time = 20000; -- 02:00:00
GO

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'Weekly DBCC CHECKDB - All Databases',
    @schedule_name = N'Weekly Sunday 2AM';
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'Weekly DBCC CHECKDB - All Databases',
    @server_name = N'(local)';
GO

PRINT 'Weekly DBCC CHECKDB job created successfully.';
GO

9. Full Backup All User Databases (Recommendation 8.1)

-- Perform immediate full backups of all user databases that lack recent backups.
-- Adjust @backup_path to a valid backup directory on your system.
-- Backups use WITH COMPRESSION and CHECKSUM (enabled by previous sp_configure scripts).

USE master;
GO

DECLARE @backup_path NVARCHAR(256) = N'C:\SQLBackups\';  -- *** CHANGE THIS PATH ***

-- Ensure backup directory exists (run in Windows or use xp_cmdshell if enabled)
-- EXEC xp_cmdshell 'mkdir C:\SQLBackups';

DECLARE @sql NVARCHAR(1000);
DECLARE @dbname SYSNAME;
DECLARE @filename NVARCHAR(500);
DECLARE @timestamp VARCHAR(20);

SET @timestamp = REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), '-', ''), ':', '');
SET @timestamp = REPLACE(@timestamp, ' ', '_');

-- Backup each user database
DECLARE backup_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name NOT IN ('tempdb')
      AND state_desc = 'ONLINE'
    ORDER BY name;

OPEN backup_cursor;
FETCH NEXT FROM backup_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = @backup_path + @dbname + N'_Full_' + @timestamp + N'.bak';
    SET @sql = N'BACKUP DATABASE [' + @dbname + N']
    TO DISK = N''' + @filename + N'''
    WITH COMPRESSION,
         CHECKSUM,
         STATS = 10,
         FORMAT,
         INIT,
         NAME = N''' + @dbname + N' Full Backup'';';

    PRINT 'Backing up: ' + @dbname + ' -> ' + @filename;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM backup_cursor INTO @dbname;
END;

CLOSE backup_cursor;
DEALLOCATE backup_cursor;
GO

-- Verify backups recorded in msdb
SELECT
    d.name AS database_name,
    bs.type AS backup_type,
    bs.backup_finish_date,
    CAST(bs.backup_size / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS backup_size_mb,
    bs.is_compressed
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
    ON d.name = bs.database_name
    AND bs.backup_finish_date >= DATEADD(HOUR, -2, GETDATE())
ORDER BY d.name, bs.backup_finish_date DESC;
GO

10. Fix Full Recovery Databases Without Log Backups (Recommendation 8.2)

-- For developer databases where point-in-time recovery is NOT required,
-- switch from FULL to SIMPLE recovery model to prevent log file growth.
-- *** REVIEW EACH DATABASE BEFORE RUNNING — choose SIMPLE only if PITR is not needed. ***
-- After switching to SIMPLE, take a new full backup to establish a new backup chain.

USE master;
GO

-- Option A: Switch to SIMPLE recovery (recommended for developer/test databases
--           where point-in-time recovery is not required)

-- Uncomment the databases you want to switch to SIMPLE:

-- ALTER DATABASE [tpcc]       SET RECOVERY SIMPLE WITH NO_WAIT;
-- ALTER DATABASE [tpch]       SET RECOVERY SIMPLE WITH NO_WAIT;
-- ALTER DATABASE [tpch10]     SET RECOVERY SIMPLE WITH NO_WAIT;
-- ALTER DATABASE [CRM]        SET RECOVERY SIMPLE WITH NO_WAIT;
-- ALTER DATABASE [DigitsSolver] SET RECOVERY SIMPLE WITH NO_WAIT;
-- ALTER DATABASE [model]      SET RECOVERY SIMPLE WITH NO_WAIT;  -- system DB caution

-- After switching to SIMPLE, shrink the log to reclaim space (one-time operation only):
-- DBCC SHRINKFILE (N'', 64);  -- Shrink to 64 MB

-- Option B: If FULL recovery IS required, create a log backup job.
--           First take a full backup (run Script #9 above), then create log backups.
--           Example for tpch (has an existing full backup from 2025-11-22):

-- BACKUP LOG [tpch]
-- TO DISK = N'C:\SQLBackups\tpch_Log_' + REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':','') + '.trn'
-- WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Verify recovery models after changes
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name NOT IN ('tempdb', 'master', 'msdb')
ORDER BY name;
GO

11. Audit SQL Logins and sa Account Status (Recommendation 6.3)

-- Audit all SQL Server logins, sysadmin members, and sa account status.
-- Review output for unexpected privileged accounts.

USE master;
GO

-- List all server logins with their roles and status
SELECT
    sp.name                     AS login_name,
    sp.type_desc                AS login_type,
    sp.is_disabled,
    sp.is_policy_checked,
    sp.is_expiration_checked,
    CASE WHEN srm.role_principal_id IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_sysadmin,
    sp.create_date,
    sp.modify_date,
    sp.default_database_name
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm
    ON sp.principal_id = srm.member_principal_id
   AND srm.role_principal_id = SUSER_ID('sysadmin')
WHERE sp.type IN ('S', 'U', 'G')  -- SQL, Windows user, Windows group
  AND sp.name NOT LIKE '##%'      -- Exclude internal accounts
ORDER BY is_sysadmin DESC, sp.name;
GO

-- Check sa account specifically
SELECT
    name,
    is_disabled,
    LOGINPROPERTY('sa', 'PasswordHash') AS has_password,
    LOGINPROPERTY('sa', 'BadPasswordCount') AS bad_password_count,
    LOGINPROPERTY('sa', 'IsLocked') AS is_locked
FROM sys.server_principals
WHERE name = 'sa';
GO

-- List all sysadmin members explicitly
SELECT
    ssp.name AS sysadmin_member,
    ssp.type_desc,
    ssp.is_disabled,
    ssp.create_date
FROM sys.server_role_members srm
JOIN sys.server_principals ssp ON srm.member_principal_id = ssp.principal_id
JOIN sys.server_principals srp ON srm.role_principal_id = srp.principal_id
WHERE srp.name = 'sysadmin'
ORDER BY ssp.name;
GO

12. Apply All Safe Server Configuration Changes in One Batch (Combined Script)

-- Combined script: Apply all safe, dynamic sp_configure recommendations at once.
-- No service restart required for these settings.
-- Settings included:
--   1. optimize for ad hoc workloads = 1
--   2. backup compression default    = 1
--   3. backup checksum default       = 1
--   4. blocked process threshold     = 5 seconds
-- Note: 'tempdb metadata memory-optimized' requires restart — see Script #4.

USE master;
GO

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'optimize for ad hoc workloads', 1;
EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'backup checksum default', 1;
EXEC sp_configure 'blocked process threshold (s)', 5;
GO

RECONFIGURE WITH OVERRIDE;
GO

-- Verify all changes
SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN (
    'optimize for ad hoc workloads',
    'backup compression default',
    'backup checksum default',
    'blocked process threshold (s)'
)
ORDER BY name;
GO

PRINT 'All configuration changes applied successfully at ' + CONVERT(VARCHAR, GETDATE(), 120);
GO