AI SQL Tuner Recommendations

Tuning Goal: Server Health
Server: RockyPC
Database Context: master
Version: SQL Server 2022 (16.x) RTM-GDR, 16.0.1170.5
Edition: Developer Edition (64-bit) / Engine Edition: Enterprise

Executive Summary

Top PriorityDatabase integrity and recoverability are the primary risks on this instance. Multiple user databases show no valid recorded DBCC CHECKDB history, and several databases in FULL recovery model have no backup history or no log backup history. This is the most urgent issue because corruption could go undetected and recovery objectives are not currently supported.

Operational RiskThe instance was started recently and has 0 days uptime, so the current wait and workload data represent a very short sample. The observed waits are dominated by background/benign activity rather than a clear production bottleneck. Plan cache data also mostly reflects the data collection queries themselves.

Current HealthI/O latency is excellent across data and log files, memory pressure is not evident, and there are no recent deadlocks. Query Store is enabled and writable for all listed user databases, which is a strong foundation for ongoing performance management.

Top Priorities

  1. Immediately establish integrity checks for all user databases, then schedule recurring DBCC CHECKDB.
  2. Implement a real backup strategy with full backups for all databases and log backups for databases in FULL recovery.
  3. Patch from SQL Server 2022 RTM-GDR to a current SQL Server 2022 CU after validation, to gain important engine fixes and Query Store/performance stability improvements.
  4. Retain current core performance settings for now: MAXDOP = 8 and cost threshold for parallelism = 30 are reasonable for this hardware and current evidence.
  5. Enable optimize for ad hoc workloads to reduce plan cache waste if this instance runs ad hoc or tooling-heavy workloads.
  6. Standardize Query Store settings and consider upgrading WideWorldImporters compatibility level from 130 to 160 after testing to leverage SQL Server 2022 features.
  7. No immediate index tuning action is required because no high-value missing indexes were detected.
  8. No deadlock remediation is currently required; no deadlocks were detected in the last 7 days.
  9. No urgent encryption action is required based on current data because the only unencrypted connection is local shared memory telemetry, which is minimal risk and does not expose user data.
Overall Confidence: 86%
High for integrity, backup, configuration, and Query Store recommendations; moderate for workload-specific tuning due to short uptime and limited query sample.
Environment Note:
Developer Edition on Windows 10 Home under a hypervisor is suitable for development/test, but it is not a preferred production platform.

Detailed Prioritized Recommendations

  1. Critical: Run and schedule DBCC CHECKDB for all user databases immediately.
    • Databases with effectively missing/unknown CHECKDB history: DigitsSolver, WideWorldImporters, SQLStorm, tpcc, tpch, CRM, tpch10.
    • The reported date of Jan 1 1900 indicates CHECKDB has not been captured as having run.
    • Confidence: 99%
  2. Critical: Implement backup and log backup jobs.
    • No full backup history exists for master, model, msdb, DigitsSolver, tpcc, CRM, tpch10.
    • FULL recovery databases lacking log backups: model, DigitsSolver, tpcc, tpch, CRM, tpch10.
    • If point-in-time recovery is not required for some user databases, switch them to SIMPLE recovery instead of leaving them in FULL without log backups.
    • Confidence: 99%
  3. High: Patch to a current SQL Server 2022 cumulative update.
    • The instance is on SQL Server 2022 RTM-GDR. CU levels generally include many engine, Query Store, optimizer, and stability fixes not present in RTM.
    • Validate application behavior first, then patch in a controlled maintenance window.
    • Confidence: 95%
  4. High: Turn on optimize for ad hoc workloads.
    • This is low risk and often beneficial on mixed-use or development instances where many one-time queries can bloat the plan cache.
    • The current workload sample is too limited to prove plan cache bloat, but enabling it is generally safe and beneficial.
    • Confidence: 83%
  5. Medium: Standardize and refine Query Store configuration.
    • All listed user databases have Query Store enabled and writable, which is good.
    • WideWorldImporters differs from the others: compatibility level 130, flush interval 50 minutes, stats interval 15 minutes, max plans per query 1000.
    • Standardize settings unless there is a deliberate reason not to.
    • Confidence: 91%
  6. Medium: Upgrade database compatibility where appropriate.
    • WideWorldImporters is at compatibility level 130 while the engine is SQL Server 2022.
    • After testing, move to 160 to take advantage of SQL Server 2022 optimizer and Intelligent Query Processing features, including Parameter Sensitive Plan optimization and DOP feedback.
    • Confidence: 88%
  7. Medium: Keep current parallelism settings for now.
    • MAXDOP = 8 on a 16 logical CPU single-socket server is reasonable.
    • cost threshold for parallelism = 30 is acceptable; no evidence currently supports changing it.
    • Confidence: 80%
  8. Medium: Treat current wait stats as non-actionable baseline data.
    • The top waits are PWAIT_DIRECTLOGCONSUMER_GETNEXT and QDS_PERSIST_TASK_MAIN_LOOP_SLEEP.
    • These do not indicate a clear CPU, I/O, or concurrency bottleneck in the current sample.
    • Because uptime is near zero, collect a longer baseline before making workload-specific changes.
    • Confidence: 84%
  9. Medium: No immediate index tuning action is required.
    • No high-value missing indexes were detected across the instance.
    • Do not add indexes speculatively based only on this dataset.
    • Confidence: 94%
  10. Low: No immediate remediation is required for connection encryption based on current evidence.
    • The only unencrypted session is SQLServerCEIP over shared memory.
    • Shared memory should be ignored for encryption risk assessment, and telemetry traffic is minimal risk since user data is not exposed.
    • Still, for remote client traffic, use TLS and encrypted connection strings as a general best practice.
    • Confidence: 97%
  11. Low: Sysadmin membership is appropriately limited.
    • Only RockyPC\k_a_f is a sysadmin.
    • This aligns well with least privilege principles.
    • Confidence: 98%

1) Performance Improvements (CPU, Memory, I/O)

  1. Do not tune against the current waits as if they are bottlenecks.
    • QDS_PERSIST_TASK_MAIN_LOOP_SLEEP is a Query Store background sleep wait and is benign.
    • PWAIT_DIRECTLOGCONSUMER_GETNEXT in this short sample does not, by itself, show distress.
    • Recommendation: collect a 24-hour or multi-business-cycle baseline before making wait-driven workload changes.
    • Confidence: 87%
  2. No storage remediation is required at this time.
    • Observed I/O latency is very good: most data files are at 0–2 ms and log reads are also low.
    • There is no sign of an I/O subsystem bottleneck from the provided metrics.
    • Confidence: 96%
  3. Memory configuration is acceptable; keep max server memory = 23168 MB unless host pressure is observed.
    • Total RAM is ~32.5 GB, leaving roughly 9 GB for Windows and non-buffer-pool use, which is reasonable.
    • Current SQL committed memory is low, so there is no evidence of memory pressure.
    • Confidence: 90%
  4. Keep MAXDOP = 8 and cost threshold for parallelism = 30 for now.
    • These settings are sensible on 16 logical CPUs.
    • No CXPACKET/CXCONSUMER or CPU-pressure evidence was provided to justify a change.
    • Confidence: 82%
  5. Enable optimize for ad hoc workloads.
    • Particularly useful on developer/test or tooling-heavy instances that compile many one-off statements.
    • Confidence: 83%

2) Configuration Optimizations

  1. Patch SQL Server 2022 to a current CU.
    • Reason: important engine and optimizer fixes beyond RTM-GDR.
    • Confidence: 95%
  2. Enable optimize for ad hoc workloads. 
    EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;
  3. Leave the following settings unchanged for now:
    • max degree of parallelism = 8
    • cost threshold for parallelism = 30
    • max server memory (MB) = 23168
  4. Consider enabling backup checksum by default.
    • This improves backup validation safety with minimal overhead in most environments.
    • Confidence: 89%
    EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'backup checksum default', 1;
RECONFIGURE;
  5. Consider enabling remote admin connections for break-glass troubleshooting.
    • This is optional but often valuable for operational resilience.
    • Confidence: 72%
    EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'remote admin connections', 1;
RECONFIGURE;

3) Index Optimization Opportunities

  1. No high-value missing indexes were detected across all databases.
    • Recommendation: do not create speculative indexes from this dataset.
    • Confidence: 94%
  2. No Index Tuning goal is currently required.
    • Because no high-value missing indexes were detected, there is no database-specific index remediation to prioritize from this input.
    • Confidence: 94%
  3. Continue routine index maintenance based on fragmentation and workload, not on generic thresholds alone.
    • Use reorganize/rebuild decisions based on page count, fragmentation, and write overhead.
    • Confidence: 84%

4) Query Store Optimization

  1. Keep Query Store enabled in all listed user databases.
    • CRM, DigitsSolver, SQLStorm, tpcc, tpch, tpch10, WideWorldImporters are all in READ_WRITE.
    • This is the correct state for performance troubleshooting and plan stability features in SQL Server 2022.
    • Confidence: 98%
  2. Standardize Query Store settings across databases unless there is a deliberate exception.
    • The first six user databases use a consistent model: 15-minute flush, 60-minute stats interval, AUTO capture, AUTO cleanup, max plans per query 200.
    • WideWorldImporters differs materially.
    • Confidence: 91%
  3. For WideWorldImporters, consider reducing MAX_PLANS_PER_QUERY from 1000 to 200–300 unless high plan variability is intentional.
    • This helps control Query Store growth and noise.
    • Confidence: 78%
  4. After testing, raise WideWorldImporters compatibility level from 130 to 160.
    • This unlocks SQL Server 2022 optimizer behaviors and features.
    • Confidence: 88%
    ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;
  5. Consider standardizing WideWorldImporters Query Store options. 
    ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
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,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1000,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

5) Database Integrity Maintenance (CHECKDB Schedules)

  1. Run DBCC CHECKDB immediately for these overdue databases:
    • DigitsSolver
    • WideWorldImporters
    • SQLStorm
    • tpcc
    • tpch
    • CRM
    • tpch10

    Confidence: 99%

  2. Establish a recurring CHECKDB schedule.
    • Recommended baseline for this environment:
      • Weekly full DBCC CHECKDB for all user databases
      • Monthly CHECKDB for system databases if not covered separately
      • For larger databases, use an off-hours schedule and consider dedicated maintenance windows
    • Confidence: 96%
  3. If time-constrained, run the most business-relevant databases first, but complete the entire set.
DBCC CHECKDB (N'DigitsSolver') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'WideWorldImporters') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'SQLStorm') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpcc') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpch') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'CRM') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpch10') WITH NO_INFOMSGS, ALL_ERRORMSGS;

6) Security Enhancements (Encryption, Least Privilege, Account Auditing)

  1. No urgent remediation is required for the reported unencrypted session.
    • The unencrypted session is SQLServerCEIP over shared memory.
    • Shared memory connections should be ignored for practical encryption risk assessment.
    • This telemetry connection is minimal risk because user data is not exposed.
    • Confidence: 97%
  2. For future remote client connections, enforce encryption.
    • Install a valid SQL Server TLS certificate.
    • Enable Force Encryption in SQL Server Configuration Manager if organizational policy requires it.
    • Use Encrypt=True and TrustServerCertificate=False in connection strings where possible.
    • Confidence: 92%
  3. Sysadmin membership is appropriately constrained.
    • Only one sysadmin principal exists: RockyPC\k_a_f.
    • Maintain this least-privilege posture and review regularly.
    • Confidence: 98%
  4. Perform periodic privileged account audits.
    • Review members of sysadmin, securityadmin, and database ownership assignments quarterly.
    • Confidence: 89%

7) Concurrency Issues (Deadlocks, Locking Patterns)

  1. No deadlock remediation is currently required.
    • No deadlocks were detected in the last 7 days.
    • Therefore, there is no database-specific deadlock target at this time.
    • Confidence: 98%
  2. Do not enable blocked process threshold unless you intend to capture and review blocked process reports.
    • Current value is 0, which is reasonable when no blocking diagnostics workflow is in place.
    • Confidence: 80%
  3. If blocking emerges later, use Extended Events and Query Store first.
    • Given the absence of deadlocks and the short uptime, no immediate locking intervention is justified.
    • Confidence: 84%

8) Operational Best Practices

  1. Implement a complete maintenance regimen.
    • Backups
    • Integrity checks
    • Statistics maintenance
    • Index maintenance as needed
    • Agent alerting and job failure notifications

    Confidence: 98%

  2. Validate whether databases in FULL recovery truly need point-in-time restore.
    • If not, change them to SIMPLE to avoid unmanaged log growth and broken recovery expectations.
    • Likely candidates to review: DigitsSolver, tpcc, tpch, CRM, tpch10.
    • Confidence: 93%
  3. Do not over-interpret plan cache results yet.
    • The top cached statements are primarily instance-inspection queries used to collect this report.
    • Workload-specific tuning requires a longer-running sample after uptime stabilizes.
    • Confidence: 95%
  4. Treat this host as development/test unless proven otherwise.
    • Developer Edition on Windows 10 Home under a hypervisor is not a preferred production deployment profile.
    • Confidence: 96%

9) Proactive Maintenance Suggestions

  1. Establish daily backup jobs immediately.
    • Full backups for all important databases.
    • Differentials if needed.
    • Frequent log backups for databases remaining in FULL recovery.
    • Confidence: 99%
  2. Establish weekly integrity checks.
    • Track CHECKDB completion dates centrally.
    • Confidence: 98%
  3. Capture a stable performance baseline after at least several days of uptime.
    • Baseline waits, file latency, top CPU queries, top reads, and memory usage.
    • Confidence: 90%
  4. Use Query Store as the primary source for regression detection.
    • Track plan changes, regressed queries, and forced plans only when regressions are confirmed.
    • Confidence: 92%
  5. Review database compatibility and recovery models quarterly.
    • Particularly important for WideWorldImporters compatibility level and all user databases currently in FULL recovery without log backups.
    • Confidence: 91%

Recommended Scripts

Enable optimize for ad hoc workloads

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;

Standardize Query Store for user databases already enabled

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

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

ALTER DATABASE [SQLStorm] SET QUERY_STORE = ON;
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 = 60,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1000,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

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

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

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

Optional recovery model corrections for databases that do not require point-in-time restore

-- Review business requirements before running.
-- Example pattern only:
ALTER DATABASE [DigitsSolver] SET RECOVERY SIMPLE;
ALTER DATABASE [tpcc] SET RECOVERY SIMPLE;
ALTER DATABASE [tpch] SET RECOVERY SIMPLE;
ALTER DATABASE [CRM] SET RECOVERY SIMPLE;
ALTER DATABASE [tpch10] SET RECOVERY SIMPLE;

Immediate CHECKDB execution

DBCC CHECKDB (N'DigitsSolver') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'WideWorldImporters') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'SQLStorm') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpcc') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpch') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'CRM') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'tpch10') WITH NO_INFOMSGS, ALL_ERRORMSGS;