AI SQL Tuner Recommendations

Executive summary (top priorities)

Detailed prioritized recommendations

1. Implement DBCC CHECKDB for all databases and operationalize an integrity schedule CRITICAL
   • Affected: SQLStorm, tpcc, tpch, WideWorldImporters, DigitsSolver (Last CHECKDB appears missing/never recorded).
   • Recommendation: run a full CHECKDB ASAP (during low usage), then schedule:
     • Weekly: DBCC CHECKDB (full logical + physical)
     • Daily/Most days (optional, larger DBs): DBCC CHECKDB ... WITH PHYSICAL_ONLY

   /* Run ASAP (one database at a time during a maintenance window) */
   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;
   DBCC CHECKDB (N'DigitsSolver') WITH NO_INFOMSGS, ALL_ERRORMSGS;
   
   /* Example: nightly PHYSICAL_ONLY for faster detection (optional) */
   DBCC CHECKDB (N'SQLStorm') WITH PHYSICAL_ONLY, NO_INFOMSGS;

   • Operational note: for larger databases, consider offloading to a restored copy on another server (where licensing/ops allow) to reduce production impact.
   Confidence: High (timestamps show 1900 baseline across all DBs, strongly indicating missing/never captured CHECKDB history).
2. Fix the extreme I/O latency for WideWorldImporters primary data file CRITICAL
   • Evidence: WWI_Primary avg read 235 ms, avg write 587 ms (unacceptable for most workloads).
   • Risk: severe query slowdowns, timeouts, and misleading wait patterns (PAGEIOLATCH_SH can rise when reads stall).
   • Actions (prioritized):
     • Move WideWorldImporters.mdf to faster storage / separate volume from competing workloads.
     • Exclude MDF/NDF/LDF directories from real-time antivirus scanning (or use SQL Server AV best practices).
     • Validate Windows storage stack/virtual disk health (hypervisor, host disk, throttling, write-cache policy).
     • Check for background jobs causing stalls (backups, snapshots, file-level scans).
   Confidence: High (latency is orders of magnitude worse than other files on the same instance, indicating a real storage-path issue for that file).
3. Address high-value missing indexes in SQLStorm via structured index tuning HIGH
   • Affected database: SQLStorm
   • High-impact missing index candidates:
     • dbo.PostHistory: key on (PostId), INCLUDE (PostHistoryTypeId) (very high estimated impact).
     • dbo.Votes: key on (UserId), INCLUDE (VoteTypeId, BountyAmount).
   • Recommendation: run comprehensive analysis (dedupe/overlap checks, write overhead, existing index review).

   aisqltuner -S rockypc -d SQLStorm -goal IndexTuning -E

   Confidence: Medium-High (missing index DMVs are directionally useful but can over-recommend; needs validation against existing indexes and workload).
4. Harden security: reduce sysadmin membership and secure/disable sa where possible MED
   • Current sysadmin members (6): NT Service\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT SERVICE\SQLWriter, NT SERVICE\Winmgmt, RockyPC\k_a_f, sa.
   • Recommendations (prioritized):
     • Ensure sa has a strong password; disable if not required; consider renaming as defense-in-depth.
     • Grant human logins (e.g., RockyPC\k_a_f) least privilege: use a custom server role and granular permissions instead of sysadmin for routine administration.
     • Review whether NT SERVICE\Winmgmt requires sysadmin; remove if not needed by your tooling/monitoring stack.

   /* Disable 'sa' if not required (validate app dependencies first) */
   ALTER LOGIN [sa] DISABLE;
   
   /* Example: create a limited admin role (tailor permissions to your ops model) */
   CREATE SERVER ROLE [srvops];
   GRANT VIEW SERVER STATE TO [srvops];
   GRANT ALTER ANY CONNECTION TO [srvops];  -- only if needed
   -- Add more minimal grants as required, avoid CONTROL SERVER unless necessary
   
   -- Example: move a user out of sysadmin into limited role
   -- ALTER SERVER ROLE [sysadmin] DROP MEMBER [RockyPC\k_a_f];
   -- ALTER SERVER ROLE [srvops] ADD MEMBER [RockyPC\k_a_f];

   Confidence: Medium (sysadmin minimization is universally beneficial; exact removals depend on environment/tooling requirements).
5. Enable/standardize connection encryption (note: unencrypted telemetry is minimal risk) MED
   • Observed: 1 unencrypted session for NT SERVICE\SQLTELEMETRY (SQLServerCEIP) over shared memory; user sessions are encrypted.
   • Recommendation:
     • For client connections: use Encrypt=True and validate cert trust (TrustServerCertificate=False where possible).
     • On server: configure TLS certificate and enable Force Encryption if required by policy.
     • Telemetry unencrypted is minimal risk because it does not expose user data, but consistent encryption reduces audit findings.
   Confidence: Medium (shared-memory local connections have different threat models; recommendation targets policy compliance and consistency).
6. Reduce plan cache waste for ad hoc workloads (if applicable): enable optimize for ad hoc workloads MED
   • Current: optimize for ad hoc workloads = 0.
   • Recommendation: enable when you have many single-use ad hoc queries to reduce memory pressure and cache bloat.

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

   Confidence: Medium (benefit depends on workload; safe change and commonly recommended for mixed/ad hoc environments).
7. Query Store: keep enabled; tune for stability and overhead consistency INFO
   • Status: Query Store is READ_WRITE for all listed databases (good).
   • Recommendations:
     • SQLStorm/tpch/tpcc/DigitsSolver: settings are reasonable; continue monitoring size (currently very low usage).
     • WideWorldImporters: compatibility level 130 and different intervals; consider aligning to modern defaults if you rely on consistent cross-DB analytics.
     • If you see excessive plan variants: consider lowering Max Plans Per Query (currently 200 for most DBs; WWI is 1000) to reduce noise.

   /* Example: standardize Query Store settings (adjust per DB workload/SLO) */
   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,
     MAX_PLANS_PER_QUERY = 200,
     QUERY_CAPTURE_MODE = AUTO
   );

   Confidence: Medium (Query Store is already enabled and healthy; recommendations are optimization/standardization rather than fixes).
8. Wait stats: deprioritize SOS_WORK_DISPATCHER; validate PAGEIOLATCH_SH and memory allocation waits with context INFO
   • SOS_WORK_DISPATCHER dominating (92.74%) is often background/dispatcher behavior and not an immediate performance target.
   • PAGEIOLATCH_SH exists but is a small percentage overall; it becomes actionable when correlated with high read latency (notably WWI_Primary).
   • MEMORY_ALLOCATION_EXT is present at low percent; monitor if it rises alongside query memory grants/spills (currently no spills in shown top queries).
   Confidence: Medium (interpretation is based on common SQL Server wait semantics; full validation requires runtime workload counters).
9. Concurrency: no deadlocks detected; keep guardrails for blocking detection INFO
   • Deadlocks in past 7 days: none (good).
   • Recommendation: enable blocked process reporting to catch severe blocking early (use Extended Events to capture reports).

   /* Enable blocked process reports (start with 15s; tune to your tolerance) */
   EXEC sp_configure 'show advanced options', 1;
   RECONFIGURE;
   EXEC sp_configure 'blocked process threshold (s)', 15;
   RECONFIGURE;

   Confidence: Medium (useful operational control; threshold should be tuned to workload).
10. Operational best practices: backups, checksums, and maintenance automation MED
    • Backup hardening:
      • Enable backup checksums by default.
      • Enable backup compression by default (Developer/Enterprise supports it; saves I/O and storage).

    /* Safer defaults for most environments */
    EXEC sp_configure 'backup checksum default', 1;
    RECONFIGURE;
    
    EXEC sp_configure 'backup compression default', 1;
    RECONFIGURE;

    • Schedule: nightly full/diff/log (as appropriate), periodic restore tests, and regular index/statistics maintenance aligned to workload.
    Confidence: High (industry-standard operational controls with low downside).
11. SQL Server configuration sanity (current settings largely reasonable for this host) INFO
    • CPU: 16 logical schedulers, MAXDOP = 8 and cost threshold for parallelism = 30 are generally reasonable starting points.
    • Memory: max server memory = 23168 MB on a 32 GB host leaves ~9 GB to OS/other processes (reasonable).
    • tempdb: multiple data files present with good latency (overall ~15–16 ms); no urgent changes indicated from provided data.
    Confidence: Medium (baseline looks fine; workload-specific tuning may still be beneficial).
12. Proactive maintenance suggestions (ongoing) INFO
    • Establish a standard maintenance toolkit (agent jobs) for: CHECKDB, backups, index/statistics maintenance, and purge/retention tasks.
    • Implement monitoring for: file latency per DB/file, Query Store size/readonly transitions, failed jobs, and security drift (sysadmin membership).
    • Keep SQL Server 2022 patched (you are on RTM-GDR; maintain latest applicable CU/GDR cadence per your policy).
    Confidence: High (general best practices with strong risk reduction).

Targeted “AI SQL Tuner” actions

aisqltuner -S rockypc -d SQLStorm -goal IndexTuning -E

Key findings snapshot