Sample SQL Server Health Check Recommendations

Executive summary (Top priorities)

  1. Run DBCC CHECKDB for all user databases immediately
    Critical

    • Databases with no recorded CHECKDB: CRM, DigitsSolver, SQLStorm, tpcc, tpch, WideWorldImporters (last run shown as Jan 1, 1900).
    • Operational risk: silent corruption can turn recoverable issues into data loss.
  2. Address tempdb write latency and log consumer waits
    Critical

    • tempdb data files show ~51 ms avg write latency (overall ~38–39 ms), disproportionately high vs other databases (~0–2 ms).
    • Top wait: PWAIT_DIRECTLOGCONSUMER_GETNEXT (~21.6%): commonly points to log/I/O throughput or log rate-limiting behavior under heavy logging (often tied to tempdb/workload logging).
  3. Reduce parallelism coordination overhead and improve plan stability controls
    High

    • Notable waits: CXPACKET + CXCONSUMER (~9.1% total), plus CXSYNC_PORT (~1.2%).
    • Focus on right-sizing MAXDOP/cost threshold and preventing “wide” parallelism for low-benefit queries.
  4. Query Store: keep enabled, reduce overhead noise, ensure it can be used for troubleshooting
    Medium

    • Large waits: QDS_PERSIST_TASK_MAIN_LOOP_SLEEP and QDS_ASYNC_QUEUE are benign “sleep/idle” style waits in many cases, but they dominate totals here due to uptime and low workload.
    • Most DBs show minimal QDS usage (1–3 MB). Ensure settings are aligned for your workload and avoid excessive capture.
  5. Security posture is good; keep it that way
    Good

    • Connections: 100% encrypted (shared memory, local); sysadmin members: 1.
    • Maintain least privilege and consider enforcing encryption for remote TCP clients if/when applicable.

1) Performance improvements (CPU, memory, I/O)

Primary bottlenecks

  1. tempdb I/O (writes) is the standout performance limiter
    Critical

    • tempdb data files: avg write latency ~51 ms across all 8 data files; reads ~12–13 ms.
    • Other databases show ~0–2 ms, strongly suggesting tempdb is either on slower storage, experiencing contention, or impacted by OS/host factors.
    Recommendation: move tempdb to faster storage (preferably dedicated SSD/NVMe), ensure the underlying volume is not overcommitted, and verify host/VM storage policies (write caching, power plan, hypervisor storage latency).
    -- Validate tempdb file layout and growth settings
SELECT
  DB_NAME(database_id) AS database_name,
  name AS logical_name,
  type_desc,
  physical_name,
  size/128.0 AS size_mb,
  growth,
  is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type_desc, name;
  2. Logging-related waits (PWAIT_DIRECTLOGCONSUMER_GETNEXT)
    High

    • This wait can surface when log generation/consumption is throttled or waiting on I/O.
    • Given tempdb write latency, it is consistent with log-related throughput constraints or bursts of tempdb-heavy activity (sorts, spills, version store, temp tables).
    -- Check current log write pressure for tempdb and other DBs
SELECT TOP (20)
  DB_NAME(vfs.database_id) AS database_name,
  mf.name AS file_logical_name,
  mf.type_desc,
  mf.physical_name,
  vfs.num_of_writes,
  vfs.io_stall_write_ms,
  CASE WHEN vfs.num_of_writes = 0 THEN 0
       ELSE vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id = mf.file_id
ORDER BY avg_write_latency_ms DESC;
  3. Parallelism overhead is present (CXPACKET/CXCONSUMER/CXSYNC_PORT)
    Medium

    • MAXDOP=8 on a 16 logical CPU machine is often reasonable, but CX waits suggest some queries may be going parallel unnecessarily.
    • Approach: increase cost threshold for parallelism and confirm MAXDOP aligns to physical cores/NUMA (single-socket here).

Memory posture (informational)

  • Max server memory: 23168 MB on a ~32.5 GB machine is generally reasonable for a workstation/dev host.
  • Committed target memory (~10.4 GB) suggests SQL Server is not currently under memory pressure.

2) Configuration optimizations

  1. Enable optimize for ad hoc workloads to reduce plan cache bloat
    Medium

    • Currently: optimize for ad hoc workloads = 0.
    • Helps when many single-use ad hoc statements occur (common in tooling/ORM/dev environments).
    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
  2. Parallelism tuning: raise cost threshold for parallelism (CTFP)
    Medium

    • Currently: cost threshold for parallelism = 30 (already above default 5).
    • Given CX waits and likely mixed workload, consider 50 as a safer baseline to reduce low-value parallelism.
    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
  3. tempdb metadata memory-optimized
    Medium

    • Currently: tempdb metadata memory-optimized = 0.
    • Enable if you observe tempdb metadata contention (PAGELATCH_* on tempdb system tables). It won’t fix slow storage, but can reduce tempdb metadata bottlenecks.
    -- Requires a SQL Server restart to take effect
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'tempdb metadata memory-optimized', 1;
RECONFIGURE;
  4. Backups: enable backup checksum and (optionally) compression by default
    Medium

    • Currently: backup checksum default = 0, backup compression default = 0.
    • Checksums reduce the risk of undetected backup corruption; compression typically reduces I/O and storage (CPU tradeoff).
    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE;

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

3) Index optimization opportunities

  1. No high-value missing indexes detected
    Good

    • No immediate missing-index action required based on the provided dataset.
    • Continue routine index maintenance and review Query Store for regressions rather than relying solely on missing-index DMVs.

4) Query Store optimization

Observation: All listed user databases have Query Store READ_WRITE. Storage utilization is low (mostly 0.1–3%).
Waits: QDS_PERSIST_TASK_MAIN_LOOP_SLEEP and QDS_ASYNC_QUEUE dominating totals is commonly benign (background task sleep/queue), especially with short uptime and light workload.
  1. Standardize Query Store configuration for SQL Server 2022 databases (compat level 160)
    Medium

    • Current capture mode: AUTO everywhere (reasonable).
    • Stats collection interval: mostly 60 min; consider 15 min for better resolution when troubleshooting performance regressions.
    • Max plans per query: 200 is typically fine; avoid very high values unless needed.
    -- Apply per database as needed (example template)
ALTER DATABASE CURRENT
SET QUERY_STORE = ON;

ALTER DATABASE CURRENT
SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  DATA_FLUSH_INTERVAL_SECONDS = 900,              -- 15 minutes
  INTERVAL_LENGTH_MINUTES = 15,                   -- stats collection interval
  MAX_PLANS_PER_QUERY = 200,
  QUERY_CAPTURE_MODE = AUTO
);
  2. WideWorldImporters: align Query Store to modern defaults (optional)
    Low

    • Compatibility level is 130 (SQL 2016 behavior). If this DB is used for testing, consider raising to 160 to benefit from SQL Server 2022 optimizer/IQP features (evaluate application compatibility first).
    -- If appropriate for your use case (test first):
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;

5) Database integrity maintenance (CHECKDB)

  1. Immediately establish and execute CHECKDB for all databases with “Jan 1 1900” last run
    Critical

    • Databases impacted: CRM, DigitsSolver, SQLStorm, tpcc, tpch, WideWorldImporters.
    • Risk: unknown corruption state; backups may also contain corruption if never validated.
    -- Run during low-usage windows; repeat per database
DBCC CHECKDB ([CRM]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB ([DigitsSolver]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB ([SQLStorm]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB ([tpcc]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB ([tpch]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

DBCC CHECKDB ([WideWorldImporters]) WITH NO_INFOMSGS, ALL_ERRORMSGS;
  2. Implement a recurring integrity schedule
    High

    • Recommendation baseline: weekly CHECKDB for critical OLTP databases; at minimum monthly for larger/less critical DBs.
    • For very large databases: consider PHYSICAL_ONLY more frequently + full CHECKDB less frequently (acknowledging reduced coverage).
    -- Example: weekly full CHECKDB job step (repeat per DB)
-- (Use SQL Agent job scheduling in msdb)
DBCC CHECKDB (N'YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  3. Pair integrity checks with backup verification
    Medium

    • Use RESTORE VERIFYONLY plus backup checksums to reduce restore-time surprises.
    -- Example (after enabling backup checksums):
BACKUP DATABASE [YourDatabase]
TO DISK = N'C:\Backups\YourDatabase.bak'
WITH CHECKSUM, COMPRESSION;

RESTORE VERIFYONLY
FROM DISK = N'C:\Backups\YourDatabase.bak'
WITH CHECKSUM;

6) Security enhancements (encryption, least privilege, account auditing)

  1. Maintain least privilege for sysadmin
    Good

    • Current sysadmin members: 1 (RockyPC\k_a_f) — appropriate.
    • Recommendation: keep sysadmin to a minimum; use dedicated admin accounts and avoid using sysadmin for application logins.
  2. Encryption posture is good; enforce for TCP if remote connections are introduced
    Good

    • All observed sessions are encrypted and use shared memory (local). Shared memory connections are acceptable and were ignored for risk.
    • If you later allow remote clients: configure a server certificate and set “Force Encryption” for the instance, and ensure clients validate the certificate.
  3. Reduce surface area where possible (informational)
    Low

    • allow filesystem enumeration = 1 can expose directory listing metadata to some principals; set to 0 if not needed.
    • remote access = 1 is legacy and typically unnecessary; disable if not required by your environment.
    EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'allow filesystem enumeration', 0;
RECONFIGURE;

EXEC sp_configure 'remote access', 0;
RECONFIGURE;

7) Concurrency issues (deadlocks, locking patterns)

  1. No deadlocks detected in the last 7 days
    Good

    • No deadlock-specific remediation required based on provided data.
  2. tempdb performance can indirectly impact concurrency
    Medium

    • Slow tempdb writes can prolong sorts/spills/version store operations, increasing transaction duration and lock hold times.
    • Fixing tempdb I/O often reduces perceived blocking without changing application logic.

8) Operational best practices

  1. Implement/validate SQL Agent maintenance jobs (backups, CHECKDB, index/statistics)
    High

    • Database Mail XPs are disabled (Database Mail XPs = 0); enable if you need alerting.
    • Set up job notifications for failures (Agent is enabled: Agent XPs = 1).
    -- Enable Database Mail XPs if you plan to use mail alerts
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
  2. Ensure tempdb is correctly sized with sensible autogrowth
    High

    • Multiple tempdb data files are present (8), which is good for allocation contention.
    • Confirm equal sizing and fixed MB growth (not percent) to avoid uneven growth and frequent growth events.
  3. Keep the instance patched beyond RTM where possible
    Medium

    • Product level shows RTM; ensure you are on the latest SQL Server 2022 CU/GDR appropriate for your environment.

9) Proactive maintenance suggestions

  1. Baseline and monitor tempdb and log I/O latency
    High

    • Alert when tempdb data file write latency exceeds ~10–15 ms sustained (threshold varies by workload; your observed ~51 ms is high).
    • Track host storage metrics if running under a hypervisor.
  2. Use Query Store for regression detection and plan control
    Medium

    • With SQL Server 2022, leverage Query Store + features like Parameter Sensitive Plan optimization (compat 160) for plan stability.
    • Keep capture mode AUTO; consider CUSTOM if capture becomes noisy on busy systems.
  3. Establish routine statistics maintenance
    Medium

    • Even with auto stats, periodic UPDATE STATISTICS on large/volatile tables helps avoid suboptimal plans.

Confidence levels

  • DBCC CHECKDB overdue/never run: Very high confidence (data explicitly indicates “Jan 1 1900”).
  • tempdb I/O bottleneck (write latency): High confidence (latency is materially higher than other DB files).
  • Parallelism tuning opportunity: Medium confidence (CX waits present; needs workload context to optimize further).
  • Query Store waits interpretation (sleep/queue dominance): Medium confidence (often benign; wait breakdown can be skewed by low workload and uptime).
  • Security posture (encryption, sysadmin count): High confidence (explicitly provided).
Generated by AI SQL Tuner Studio using model gpt-5.2 with reasoning effort minimal.

https://aisqltuner.com