AI SQL Tuner Recommendations

Executive Summary

1. Establish integrity protection immediately. Every listed user database shows no valid DBCC CHECKDB history, with placeholder dates indicating CHECKDB has effectively never been recorded. This is the highest operational risk.
2. Implement a real backup strategy now. Several databases have never had a full backup, and multiple FULL recovery databases have no log backups. This creates major recovery exposure and can also cause unnecessary log growth.
3. Focus tuning on query design and indexing validation, not storage. I/O latency is very good across data, log, and tempdb files. Current evidence points to CPU-heavy and read-heavy query shapes, large memory grants, and some inefficient patterns in plan cache rather than disk bottlenecks.
4. Do not overreact to the top waits. The dominant waits are QDS_PERSIST_TASK_MAIN_LOOP_SLEEP and QDS_ASYNC_QUEUE, which are generally benign Query Store background waits, plus PWAIT_DIRECTLOGCONSUMER_GETNEXT, which is not by itself a clear performance problem here. These do not indicate critical server distress.
5. Query Store is enabled and healthy overall. This is good. However, standardizing Query Store settings across databases would improve consistency, especially on WideWorldImporters, which has materially different settings.
6. Security posture is mostly acceptable. Only one sysadmin login exists, which aligns with least privilege. The reported unencrypted sessions are all shared memory; these should be ignored per guidance, and the SQL telemetry shared-memory session is minimal risk because user data is not exposed.
7. No deadlocks were detected in the last 7 days. No immediate concurrency emergency is present. Continue monitoring, but no deadlock remediation is required from this dataset.
8. No high-value missing indexes were detected. Do not create speculative indexes based on this data alone. Since the expensive queries are clearly analytical and join-heavy, a more comprehensive workload-based index review is still warranted if these queries are important.

Detailed Prioritized Recommendations

1. Critical: run DBCC CHECKDB on all user databases and schedule recurring integrity checks. Impacted databases: DigitsSolver, WideWorldImporters, SQLStorm, tpcc, tpch, CRM. Confidence: Very High.
2. Critical: configure full backups for all databases and log backups for FULL recovery databases. Impacted databases include DigitsSolver, tpcc, tpch, CRM, model, plus any database with no current backup cadence. Confidence: Very High.
3. High: optimize the expensive analytical query patterns shown in plan cache. These queries are consuming high CPU, high logical reads, and large memory grants, with repeated wide joins, COUNT(DISTINCT), ROW_NUMBER(), RANK(), correlated subqueries, and non-SARGable predicates. Confidence: High.
4. High: enable optimize for ad hoc workloads. This is a safe server-wide improvement for environments with many one-off or low-reuse queries, which appears plausible here given numerous similar but distinct complex statements. Confidence: High.
5. High: standardize Query Store settings. Keep Query Store enabled everywhere and align capture/statistics/plan limits where practical. Confidence: High.
6. Medium: patch beyond RTM-GDR if approved. SQL Server 2022 RTM is functional, but cumulative updates generally provide optimizer, Query Store, and engine fixes. Confidence: Medium.
7. Medium: review memory grant efficiency. Multiple cached plans request very large grants; one plan spilled, meaning memory estimates or join/order operations may need attention. Confidence: Medium-High.
8. Medium: review FULL recovery usage for non-production/test databases. If tpcc, tpch, CRM, DigitsSolver do not require point-in-time recovery, consider SIMPLE recovery to reduce operational burden. Confidence: Medium.
9. Low: no immediate storage or tempdb remediation is required. Current file latencies are healthy, tempdb has eight data files, and no acute I/O symptom is visible. Confidence: High.

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

Priority Findings

• I/O is healthy. Most file latencies are near 0-6 ms, including tempdb and log files. There is no evidence of disk pressure as the primary bottleneck.
• The dominant waits are mostly benign.
  • QDS_PERSIST_TASK_MAIN_LOOP_SLEEP and QDS_ASYNC_QUEUE are Query Store background waits and typically not actionable performance bottlenecks.
  • PWAIT_DIRECTLOGCONSUMER_GETNEXT is not enough on its own here to diagnose a sustained transaction log throughput issue, especially with strong log I/O latency.
  • HTMEMO suggests some optimizer/memoization work and may correlate with very complex query compilation or optimization effort.
• Plan cache indicates query inefficiency. Several statements have:
  • Very high average CPU time
  • Very high logical reads
  • Large memory grants up to roughly 1.7 GB
  • One query with spills (160)
• Workload pattern: repeated analytical/reporting queries against StackOverflow-style schema objects such as Posts, Users, Votes, Comments, Badges, and PostHistory.

Recommendations

1. Tune the top CPU/read consumers first.
   • Rewrite non-SARGable patterns such as p.Tags LIKE '%' + t.TagName + '%'. This prevents efficient index usage and is a major red flag.
   • Reduce repeated correlated subqueries like:
     • (SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id ...)
     • (SELECT COUNT(*) FROM Comments c WHERE c.PostId = p.Id)
     Replace them with pre-aggregated joins or temp tables where appropriate.
   • Evaluate whether COUNT(DISTINCT), wide grouping, and multiple window functions can be staged in phases.
   Confidence: High.
2. Exploit SQL Server 2022 capabilities already available.
   • Compatibility level 160 databases can benefit from Intelligent Query Processing features, Parameter Sensitive Plan optimization, and Degree of Parallelism feedback.
   • Keep these databases at compatibility 160 unless application testing proves otherwise.
   Confidence: High.
3. Review memory grant heavy queries.
   • Large grants indicate expensive sorts, hashes, or cardinality uncertainty.
   • The spilling query (Query Hash 9D978A0253DCE155) deserves priority review because spills add tempdb work and elapsed time.
   Confidence: Medium-High.
4. No immediate storage changes are needed. Current evidence does not justify moving files, resizing tempdb layout, or changing storage tiers solely from this dataset. Confidence: High.

2) Configuration Optimizations

Good Current Settings

• MAXDOP = 8 on a 16 logical CPU system is reasonable.
• Cost Threshold for Parallelism = 30 is healthier than the default 5 and appropriate for mixed or analytical workloads.
• Max server memory = 23168 MB on a 32.5 GB machine leaves a reasonable OS reserve.
• CLR, xp_cmdshell, Ole Automation, external scripts are disabled, which reduces attack surface.

Recommended Changes

1. Enable optimize for ad hoc workloads.
   • Current value: 0
   • Why: the workload shows many large, possibly one-off analytical statements. This option reduces wasted plan cache space from single-use ad hoc plans.
   Confidence: High.
2. Enable backup checksum by default.
   • Current value: 0
   • Why: safer backups with corruption detection during backup operations.
   Confidence: High.
3. Enable backup compression by default if backup CPU overhead is acceptable.
   • Current value: 0
   • Why: typically reduces backup size and elapsed time, especially useful on non-trivial databases.
   Confidence: High.
4. Consider enabling remote admin connections.
   • Current value: 0
   • Why: helpful for emergency DAC access during severe server issues.
   Confidence: Medium.
5. Consider blocked process threshold for troubleshooting.
   • Current value: 0
   • Why: not a performance fix, but useful when diagnosing blocking in the future. A common starting point is 15 seconds.
   Confidence: Medium.
6. Stay current on SQL Server 2022 CUs.
   • You are on RTM-GDR. For performance and engine stability, a current CU is usually preferable after validation.
   Confidence: Medium.

3) Index Optimization Opportunities

Assessment

• No high-value missing indexes were detected across all databases.
• That means there is no safe, high-confidence server-wide missing index recommendation from the supplied DMVs.
• However, the query patterns still strongly suggest that performance may improve through:
  • targeted covering indexes
  • filtered indexes
  • summary tables or indexed staging
  • query rewrites that allow existing indexes to be used

Recommendations

1. Do not create speculative indexes based solely on this report. Confidence: High.
2. Prioritize code/query rewrites first for these patterns:
   • LIKE '%value%' joins against tag strings
   • multiple LEFT JOIN fan-outs combined with COUNT(DISTINCT)
   • repeated correlated count subqueries
   • broad windowing over large rowsets
   Confidence: High.
3. If business-critical performance remains poor, run a comprehensive per-database index analysis. Since no high-value missing indexes were detected, there is no database-specific missing-index escalation required from this dataset. Confidence: Medium.

4) Query Store Optimization

Assessment

• Query Store is enabled and READ_WRITE for all reported user databases.
• This is a strong positive and appropriate for SQL Server 2022.
• CRM, DigitsSolver, SQLStorm, tpcc, tpch share a consistent baseline:
  • Flush interval: 15 minutes
  • Stats collection interval: 60 minutes
  • Capture mode: AUTO
  • Max plans per query: 200
• WideWorldImporters differs:
  • Flush interval: 50 minutes
  • Stats collection interval: 15 minutes
  • Max plans per query: 1000

Recommendations

1. Keep Query Store enabled on all user databases. Confidence: Very High.
2. Standardize settings unless there is a deliberate reason not to.
   • For most databases, use consistent plan limits and flush intervals.
   • WideWorldImporters should likely be aligned to reduce noise and unnecessary plan retention unless it is intentionally used as a tuning lab.
   Confidence: High.
3. Retain compatibility level 160 where possible.
   • WideWorldImporters is still at compatibility 130.
   • If application validation allows, upgrade it to 160 to leverage SQL Server 2022 optimizer features.
   Confidence: Medium-High.
4. Use Query Store to track regressions and memory grant issues.
   • Focus on the top queries already identified in plan cache.
   • Consider plan forcing only after verifying plan stability and root cause.
   Confidence: High.

5) Database Integrity Maintenance (CHECKDB Schedules)

Critical Findings

The following databases are critically overdue or effectively missing valid CHECKDB history:

• DigitsSolver
• WideWorldImporters
• SQLStorm
• tpcc
• tpch
• CRM

The reported date Jan 1 1900 indicates no meaningful last-run record in this context.

Recommendations

1. Run DBCC CHECKDB on every user database immediately. Confidence: Very High.
2. Adopt a recurring schedule.
   • Production-like or important databases: weekly full CHECKDB at minimum
   • Smaller/lab databases: weekly or biweekly depending on change rate and criticality
   • System databases: include master, msdb, and model on a scheduled basis as well
   Confidence: High.
3. If maintenance windows are tight, use restore validation on another server plus scheduled CHECKDB there, but do not eliminate integrity checking entirely. Confidence: High.

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

Assessment

• Sysadmin membership is good. Only one sysadmin account exists: RockyPC\k_a_f.
• Connection encryption warning is not materially concerning here.
  • All reported unencrypted sessions use shared memory, which should be ignored per the stated guidance.
  • The SQLTELEMETRY shared-memory session is minimal risk because user data is not exposed.
• Because all observed connections are local shared-memory connections, there is no evidence here of unencrypted TCP client traffic.

Recommendations

1. Maintain least privilege.
   • Keep sysadmin membership tightly limited.
   • Continue with one primary administrative login unless operationally necessary to add a break-glass account.
   Confidence: Very High.
2. For future remote/client connectivity, standardize encrypted TCP connections.
   • Install a proper TLS certificate.
   • Use Force Encryption where appropriate.
   • Require Encrypt=True in connection strings.
   Confidence: High.
3. Audit privileged access periodically.
   • Review logins, server roles, disabled accounts, and service accounts quarterly.
   Confidence: High.

7) Concurrency Issues (Deadlocks, Locking Patterns)

Assessment

• No deadlocks detected in the past 7 days.
• There is no immediate evidence of a deadlock-driven concurrency problem.
• No blocking-focused waits dominate the dataset.

Recommendations

1. No deadlock remediation is required from this snapshot. Confidence: High.
2. Enable blocked-process monitoring if you want earlier blocking visibility. A 15-second threshold is a practical diagnostic starting point. Confidence: Medium.
3. Reduce long-running read-heavy query duration, since large scans and broad joins can still contribute to blocking indirectly even without observed deadlocks. Confidence: Medium-High.

8) Operational Best Practices

1. Implement formal backup standards immediately.
   • Databases with no full backup recorded should be backed up now.
   • FULL recovery databases without log backups should either start log backups or be switched to SIMPLE if point-in-time recovery is not required.
   Confidence: Very High.
2. Reassess recovery model alignment.
   • DigitsSolver, tpcc, tpch, CRM, model show FULL recovery concerns with no log backups.
   • If these are dev/test/lab databases, SIMPLE may be more appropriate.
   Confidence: High.
3. Document workload criticality by database.
   • SQLStorm shows the most visible I/O activity in this sample and is likely a higher-interest database to prioritize for query tuning and backup validation.
   Confidence: Medium.
4. Maintain tempdb as currently configured unless workload changes.
   • Eight tempdb data files with good latency is reasonable on this 16 logical CPU system.
   Confidence: High.

9) Proactive Maintenance Suggestions

1. Weekly integrity checks for all user and system databases. Confidence: Very High.
2. Daily full backups for important databases, with more frequent log backups for FULL recovery databases that matter operationally. Confidence: Very High.
3. Routine Query Store review for top duration, CPU, reads, and memory grant consumers. Confidence: High.
4. Statistics and index maintenance based on workload, not fixed superstition.
   • Update statistics where modification volume justifies it.
   • Rebuild/reorganize only where fragmentation and usage support it.
   Confidence: High.
5. Patch review cadence. Evaluate SQL Server 2022 cumulative updates quarterly or per change window. Confidence: Medium.
6. Baseline capture after restart.
   • Server uptime is 0 days, so current waits and plan cache are post-startup and may not fully represent steady-state workload.
   • Capture another baseline after normal workload has run for a meaningful period.
   Confidence: High.

Recommended Scripts

Enable safe server-level options

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;

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;
-- Optional for troubleshooting:
-- EXEC sys.sp_configure N'blocked process threshold (s)', 15;

RECONFIGURE;

Immediate DBCC CHECKDB execution

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

Switch non-critical FULL recovery databases to SIMPLE if point-in-time recovery is not required

-- Use only where business requirements allow:
ALTER DATABASE [DigitsSolver] SET RECOVERY SIMPLE;
ALTER DATABASE [tpcc] SET RECOVERY SIMPLE;
ALTER DATABASE [tpch] SET RECOVERY SIMPLE;
ALTER DATABASE [CRM] SET RECOVERY SIMPLE;

Or, if FULL recovery is required, start proper backup chains

-- Example pattern only; adjust paths and schedule appropriately
BACKUP DATABASE [DigitsSolver]
TO DISK = N'C:\SQLBackups\DigitsSolver_FULL.bak'
WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP LOG [DigitsSolver]
TO DISK = N'C:\SQLBackups\DigitsSolver_LOG.trn'
WITH CHECKSUM, COMPRESSION, STATS = 10;

Standardize Query Store for WideWorldImporters

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

Raise WideWorldImporters compatibility level after validation

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;

Example encrypted client connection guidance

Server=RockyPC;Database=YourDatabase;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;

Confidence Summary