AI SQL Tuner Recommendations

Executive summary (top priorities)

1. Critical Start/restore DBCC CHECKDB coverage immediately for all user databases (CRM, DigitsSolver, SQLStorm, tpcc, tpch, WideWorldImporters) where last run shows Jan 1 1900 (~never). This is the highest operational risk (silent corruption can become unrecoverable).
2. Critical Fix tpch datafile read latency (~97 ms) (tpch.mdf). This is far above typical targets (often <10–20 ms for OLTP-ish reads) and will drive query runtimes and timeouts for tpch workload.
3. High Investigate Query Store / direct log consumer waits (QDS_ASYNC_QUEUE, PWAIT_DIRECTLOGCONSUMER_GETNEXT, QDS_PERSIST_TASK_MAIN_LOOP_SLEEP). These look like background-worker dominated waits; validate whether they’re mostly benign “sleep/idle” vs. true persistence pressure, and adjust Query Store settings if needed.
4. High Address high-impact missing indexes in CRM, SQLStorm, WideWorldImporters (multiple recommendations with extremely high impact scores). Run comprehensive index analysis before blindly creating them.
5. High Deadlock detected (past 7 days). Run a focused deadlock analysis using the recommended goal to identify the exact objects and statements involved and implement targeted fixes.
6. Good Sysadmin membership is minimal (1) and user connections are encrypted except telemetry. The only unencrypted session is SQL telemetry over shared memory (minimal risk; ignore).

Detailed prioritized recommendations

1. Implement CHECKDB schedule and execute initial checks (operational integrity risk).
2. Remediate tpch.mdf I/O latency (dominant concrete performance bottleneck).
3. Index tuning for CRM, SQLStorm, WideWorldImporters (high ROI, reduce reads/CPU).
4. Deadlock remediation workflow (correctness + throughput).
5. Query Store configuration optimization (reduce overhead, improve troubleshooting value).
6. Instance-level sp_configure adjustments (plan cache efficiency, operational access).
7. Encryption posture + least privilege hardening (already good; lock in).
8. Operational best practices (backups, consistency, storage layout).
9. Proactive maintenance (statistics, index maintenance, monitoring).

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

I/O bottleneck: tpch.mdf (~97 ms average read latency)

• Primary risk: storage path C:\Data\tpch.mdf is substantially slower than other datafiles, or the workload is forcing inefficient reads (large scans with low cache re-use) amplified by slow storage.
• Recommendation (priority): move tpch.mdf to faster storage and/or isolate it from competing I/O. If this is a lab VM, ensure the virtual disk is not on a constrained host volume and disable aggressive host-side power saving.
• Recommendation: validate that tpch queries aren’t doing avoidable scans due to missing/inefficient indexes and outdated stats (see Index section).

Memory & grants: large memory grants observed + spills

• Recommendation: prioritize tuning the spilling query (the one with spills 26,578) by adding supporting indexes, reducing row width, and avoiding non-SARGable predicates like LIKE '%' + TagName + '%' on large tables.
• Recommendation: ensure stats are current on large tables in the involved databases; bad cardinality drives over/under grants.
• Recommendation: leverage SQL 2022 features (PSP) by ensuring compatibility level 160 where possible for databases with parameter sensitivity issues (WideWorldImporters is at 130).

CPU patterns: heavy logical reads

• Finding: several top queries show multi-million logical reads (4–6 million), indicating scan-heavy patterns.
• Recommendation: reduce logical reads with targeted indexes and query rewrites (especially correlated subqueries counting comments/votes per post; consider pre-aggregation or indexed views only if justified).

2) Configuration optimizations

Enable optimize for ad hoc workloads (plan cache efficiency)

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

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

• Benefit: reduces plan cache bloat from single-use ad hoc plans; improves overall memory availability for useful cache.

Parallelism settings: generally reasonable; keep under review

• Current: MAXDOP = 8; Cost Threshold for Parallelism = 30.
• Recommendation: keep as-is for now (no evidence of CX* wait pressure provided). Revisit only after resolving I/O and indexing; those changes can materially alter parallelism behavior.

Remote Dedicated Admin Connection (DAC)

• Current: remote admin connections = 0.
• Recommendation: enable in environments where remote troubleshooting is required (especially if you ever manage it without console access). Safe operational improvement.

EXEC sys.sp_configure N'remote admin connections', 1;
RECONFIGURE;

Backup defaults: enable checksum and compression (Developer/Enterprise capable)

EXEC sys.sp_configure N'backup checksum default', 1;
RECONFIGURE;

EXEC sys.sp_configure N'backup compression default', 1;
RECONFIGURE;

3) Index optimization opportunities

High-value missing indexes detected (run comprehensive Index Tuning)

• Action: run a comprehensive analysis using the Index Tuning goal on each affected database (recommended by the tool output): CRM, SQLStorm, WideWorldImporters.

aisqltuner -S RockyPC -d CRM -goal IndexTuning -E
aisqltuner -S RockyPC -d SQLStorm -goal IndexTuning -E
aisqltuner -S RockyPC -d WideWorldImporters -goal IndexTuning -E

Targeted (candidate) index patterns based on the missing index list

Do not apply blindly; validate existing indexes, key order, and write overhead first.

Query pattern red flags (read amplification)

• Non-SARGable tag search: Posts.Tags LIKE '%' + tg.TagName + '%' will scan; normalize tags into a junction table (PostTags) and index it, or use full-text search if appropriate.
• Correlated scalar subqueries per row: counting comments/votes per post in SELECT can multiply work; prefer joins to pre-aggregated sets (GROUP BY) with supporting indexes.
• Old-style joins: FROM customer, orders, lineitem increases risk of accidental cross joins; rewrite using explicit JOINs for clarity and optimizer hints avoidance.

4) Query Store optimization

Waits dominated by Query Store background tasks (interpretation + tuning)

• Recommendation: treat these as potentially benign “sleep/idle” background waits unless correlated with Query Store read/write stalls, high CPU, or I/O pressure. Given server uptime is ~1 day, long max waits can be misleading.
• Recommendation: still optimize Query Store for value-to-overhead ratio by tightening capture where appropriate and standardizing intervals.

Standardize Query Store settings and reduce unnecessary plan churn

• WideWorldImporters anomalies: Flush interval 50 min (others 15), stats interval 15 (others 60), max plans per query 1000 (others 200), compatibility level 130.
• Recommendation: for most workloads, set:
  • Capture mode = AUTO or CUSTOM (prefer CUSTOM on very busy OLTP to reduce noise)
  • Max plans per query = 200 (or lower if many ad hoc variants)
  • Flush interval ~ 15 minutes (reasonable default)
  • Stats collection interval 30–60 minutes (depending on troubleshooting needs)
• Recommendation: consider raising WideWorldImporters compatibility level to 160 (after validating app compatibility) to take advantage of SQL 2022 optimizer features.

-- Example: bring WideWorldImporters closer to a consistent, lower-churn profile
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;

ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,            -- 15 minutes
    INTERVAL_LENGTH_MINUTES = 60,                 -- stats aggregation interval
    MAX_PLANS_PER_QUERY = 200,
    QUERY_CAPTURE_MODE = AUTO
);

Note: if a database is extremely ad hoc or high-throughput, use QUERY_CAPTURE_MODE = CUSTOM with appropriate filters (not provided in the dataset) to avoid excess overhead.

5) Database integrity maintenance (CHECKDB schedules)

All listed user databases show “never run” CHECKDB

Immediate actions (do now)

1. Run CHECKDB for each user database during the next available maintenance window (expect I/O impact).
2. If time constrained: run DBCC CHECKDB ... WITH PHYSICAL_ONLY as a fast first pass, then follow with a full CHECKDB on a schedule.

-- Full CHECKDB (preferred)
DBCC CHECKDB (N'CRM') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'DigitsSolver') 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'WideWorldImporters') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Fast triage option (run first if maintenance window is tight)
DBCC CHECKDB (N'CRM') WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB (N'DigitsSolver') WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB (N'SQLStorm') WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB (N'tpcc') WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB (N'tpch') WITH PHYSICAL_ONLY, NO_INFOMSGS;
DBCC CHECKDB (N'WideWorldImporters') WITH PHYSICAL_ONLY, NO_INFOMSGS;

Ongoing schedule recommendation

• Minimum: Full CHECKDB weekly for all user databases (or at least the most critical ones), PHYSICAL_ONLY daily if you need higher coverage and faster checks.
• Best practice: run CHECKDB against restored backups on a separate server/storage to reduce production impact (especially important if tpch I/O is already slow).

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

Connection encryption

• Observed: 11/12 connections encrypted; the only unencrypted connection is NT SERVICE\SQLTELEMETRY via shared memory (minimal risk; ignore per guidance).
• Recommendation: enforce TLS encryption for all network transports (TCP) via “Force Encryption” and a proper certificate, and ensure clients use Encrypt=True. (Shared memory local sessions can remain as-is.)

-- Client-side guidance (example connection string properties)
-- Encrypt=True; TrustServerCertificate=False; 

Least privilege / sysadmin

• Observed: sysadmin members = 1 (RockyPC\k_a_f) — good.
• Recommendation: keep sysadmin membership minimal; use dedicated admin accounts and grant granular server/database permissions (e.g., CONTROL SERVER only where justified, or scoped roles).
• Recommendation: ensure SQL Agent proxies/credentials are used for jobs requiring OS access, not sysadmin escalation.

7) Concurrency issues (deadlocks, locking patterns)

Deadlock detected in the past 7 days

• Recommendation: run a detailed deadlock analysis using the tool’s Fix Deadlocks goal and apply targeted remediation (indexing to change access paths, consistent object access order, shorter transactions, appropriate isolation, and retry logic where needed).

aisqltuner -S RockyPC -d master -goal FixDeadlocks -E

8) Operational best practices

1. Separate data/log/tempdb where possible: you already have logs on C:\Logs for several DBs; ensure tpch data is on appropriately fast storage and not competing with OS paging/other workloads.
2. Backups: enable default compression + checksum (scripts above) and verify restore regularly.
3. Uptime-aware monitoring: current uptime is ~1 day; waits and “max wait” values can be skewed. Baseline again after 7–14 days of representative workload.

9) Proactive maintenance suggestions

• Statistics maintenance: ensure regular stats updates on large tables (especially in tpch/SQLStorm/CRM) to reduce memory grant errors and poor join choices.
• Index maintenance: after Index Tuning, implement measured rebuild/reorganize strategy based on fragmentation and workload (avoid “rebuild everything”).
• Query Store governance: standardize settings and periodically review top regressed queries, top resource consumers, and forced plans (if used).
• Blocked process reporting: blocked process threshold is 0; consider enabling to capture long blocks (useful alongside deadlock analysis).

-- Example: enable blocked process reports at 15 seconds (adjust to your tolerance)
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'blocked process threshold (s)', 15;
RECONFIGURE;

Confidence levels

• CHECKDB risk assessment: Very high confidence (0.95) — last run indicates “never,” creating clear operational risk.
• tpch.mdf I/O bottleneck: High confidence (0.90) — latency outlier versus all other files strongly indicates a storage/path issue or scan-heavy workload amplified by storage.
• Missing index opportunity: Medium-high confidence (0.80) — DMVs can over-suggest and object names look odd (constraint names), but the magnitude suggests real tuning potential; comprehensive analysis is the correct next step.
• Query Store waits interpretation: Medium confidence (0.65) — these waits are often benign; confirming impact requires runtime symptoms/counters not included.
• Deadlock remediation need: High confidence (0.85) — a deadlock occurred; detailed graph review is required to fix deterministically.
• Security posture (encryption/sysadmin): High confidence (0.85) — encrypted user sessions and minimal sysadmin membership; remaining unencrypted telemetry (shared memory) is minimal risk.