Executive summary (top priorities)

  <h2>Detailed prioritized recommendations</h2>

  <ol>
    <li>
      <b>Implement DBCC CHECKDB for all databases and operationalize an integrity schedule</b>
      <span class="badge crit">CRITICAL</span>
      <ul>
        <li>Affected: <b>SQLStorm, tpcc, tpch, WideWorldImporters, DigitsSolver</b> (Last CHECKDB appears missing/never recorded).</li>
        <li>Recommendation: run a full CHECKDB ASAP (during low usage), then schedule:
          <ul>
            <li><b>Weekly</b>: <code>DBCC CHECKDB</code> (full logical + physical)</li>
            <li><b>Daily/Most days</b> (optional, larger DBs): <code>DBCC CHECKDB ... WITH PHYSICAL_ONLY</code></li>
          </ul>
        </li>
      </ul>
      <pre><code>/* 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.

    <li>
      <b>Fix the extreme I/O latency for WideWorldImporters primary data file</b>
      <span class="badge crit">CRITICAL</span>
      <ul>
        <li>Evidence: <b>WWI_Primary</b> avg read <b>235 ms</b>, avg write <b>587 ms</b> (unacceptable for most workloads).</li>
        <li>Risk: severe query slowdowns, timeouts, and misleading wait patterns (PAGEIOLATCH_SH can rise when reads stall).</li>
        <li>Actions (prioritized):
          <ul>
            <li>Move <b>WideWorldImporters.mdf</b> to faster storage / separate volume from competing workloads.</li>
            <li>Exclude MDF/NDF/LDF directories from real-time antivirus scanning (or use SQL Server AV best practices).</li>
            <li>Validate Windows storage stack/virtual disk health (hypervisor, host disk, throttling, write-cache policy).</li>
            <li>Check for background jobs causing stalls (backups, snapshots, file-level scans).</li>
          </ul>
        </li>
      </ul>
      <div class="callout">
        <b>Confidence:</b> High (latency is orders of magnitude worse than other files on the same instance, indicating a real storage-path issue for that file).
      </div>
    </li>

    <li>
      <b>Address high-value missing indexes in SQLStorm via structured index tuning</b>
      <span class="badge warn">HIGH</span>
      <ul>
        <li>Affected database: <b>SQLStorm</b></li>
        <li>High-impact missing index candidates:
          <ul>
            <li><b>dbo.PostHistory</b>: key on <code>(PostId)</code>, INCLUDE <code>(PostHistoryTypeId)</code> (very high estimated impact).</li>
            <li><b>dbo.Votes</b>: key on <code>(UserId)</code>, INCLUDE <code>(VoteTypeId, BountyAmount)</code>.</li>
          </ul>
        </li>
        <li>Recommendation: run comprehensive analysis (dedupe/overlap checks, write overhead, existing index review).</li>
      </ul>
      <pre><code>aisqltuner -S rockypc -d SQLStorm -goal IndexTuning -E</code></pre>
      <div class="callout">
        <b>Confidence:</b> Medium-High (missing index DMVs are directionally useful but can over-recommend; needs validation against existing indexes and workload).
      </div>
    </li>

    <li>
      <b>Harden security: reduce sysadmin membership and secure/disable sa where possible</b>
      <span class="badge warn">MED</span>
      <ul>
        <li>Current sysadmin members (6): NT Service\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, NT SERVICE\SQLWriter, NT SERVICE\Winmgmt, RockyPC\k_a_f, sa.</li>
        <li>Recommendations (prioritized):
          <ul>
            <li>Ensure <b>sa</b> has a strong password; disable if not required; consider renaming as defense-in-depth.</li>
            <li>Grant human logins (e.g., <b>RockyPC\k_a_f</b>) least privilege: use a custom server role and granular permissions instead of sysadmin for routine administration.</li>
            <li>Review whether <b>NT SERVICE\Winmgmt</b> requires sysadmin; remove if not needed by your tooling/monitoring stack.</li>
          </ul>
        </li>
      </ul>
      <pre><code>/* 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];

    <li>
      <b>Enable/standardize connection encryption (note: unencrypted telemetry is minimal risk)</b>
      <span class="badge warn">MED</span>
      <ul>
        <li>Observed: 1 unencrypted session for <b>NT SERVICE\SQLTELEMETRY</b> (SQLServerCEIP) over shared memory; user sessions are encrypted.</li>
        <li>Recommendation:
          <ul>
            <li>For client connections: use <code>Encrypt=True</code> and validate cert trust (<code>TrustServerCertificate=False</code> where possible).</li>
            <li>On server: configure TLS certificate and enable Force Encryption if required by policy.</li>
            <li>Telemetry unencrypted is <b>minimal risk</b> because it does not expose user data, but consistent encryption reduces audit findings.</li>
          </ul>
        </li>
      </ul>
      <div class="callout">
        <b>Confidence:</b> Medium (shared-memory local connections have different threat models; recommendation targets policy compliance and consistency).
      </div>
    </li>

    <li>
      <b>Reduce plan cache waste for ad hoc workloads (if applicable): enable optimize for ad hoc workloads</b>
      <span class="badge warn">MED</span>
      <ul>
        <li>Current: <code>optimize for ad hoc workloads = 0</code>.</li>
        <li>Recommendation: enable when you have many single-use ad hoc queries to reduce memory pressure and cache bloat.</li>
      </ul>
      <pre><code>EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;
EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;

    <li>
      <b>Query Store: keep enabled; tune for stability and overhead consistency</b>
      <span class="badge ok">INFO</span>
      <ul>
        <li>Status: Query Store is <b>READ_WRITE</b> for all listed databases (good).</li>
        <li>Recommendations:
          <ul>
            <li>SQLStorm/tpch/tpcc/DigitsSolver: settings are reasonable; continue monitoring size (currently very low usage).</li>
            <li>WideWorldImporters: compatibility level 130 and different intervals; consider aligning to modern defaults if you rely on consistent cross-DB analytics.</li>
            <li>If you see excessive plan variants: consider lowering <b>Max Plans Per Query</b> (currently 200 for most DBs; WWI is 1000) to reduce noise.</li>
          </ul>
        </li>
      </ul>
      <pre><code>/* 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
);

    <li>
      <b>Wait stats: deprioritize SOS_WORK_DISPATCHER; validate PAGEIOLATCH_SH and memory allocation waits with context</b>
      <span class="badge ok">INFO</span>
      <ul>
        <li><b>SOS_WORK_DISPATCHER</b> dominating (92.74%) is often background/dispatcher behavior and not an immediate performance target.</li>
        <li><b>PAGEIOLATCH_SH</b> exists but is a small percentage overall; it becomes actionable when correlated with high read latency (notably WWI_Primary).</li>
        <li><b>MEMORY_ALLOCATION_EXT</b> is present at low percent; monitor if it rises alongside query memory grants/spills (currently no spills in shown top queries).</li>
      </ul>
      <div class="callout">
        <b>Confidence:</b> Medium (interpretation is based on common SQL Server wait semantics; full validation requires runtime workload counters).
      </div>
    </li>

    <li>
      <b>Concurrency: no deadlocks detected; keep guardrails for blocking detection</b>
      <span class="badge ok">INFO</span>
      <ul>
        <li>Deadlocks in past 7 days: <b>none</b> (good).</li>
        <li>Recommendation: enable blocked process reporting to catch severe blocking early (use Extended Events to capture reports).</li>
      </ul>
      <pre><code>/* 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;

    <li>
      <b>Operational best practices: backups, checksums, and maintenance automation</b>
      <span class="badge warn">MED</span>
      <ul>
        <li>Backup hardening:
          <ul>
            <li>Enable backup checksums by default.</li>
            <li>Enable backup compression by default (Developer/Enterprise supports it; saves I/O and storage).</li>
          </ul>
        </li>
      </ul>
      <pre><code>/* 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.

    <li>
      <b>SQL Server configuration sanity (current settings largely reasonable for this host)</b>
      <span class="badge ok">INFO</span>
      <ul>
        <li>CPU: 16 logical schedulers, MAXDOP = 8 and cost threshold for parallelism = 30 are generally reasonable starting points.</li>
        <li>Memory: max server memory = 23168 MB on a 32 GB host leaves ~9 GB to OS/other processes (reasonable).</li>
        <li>tempdb: multiple data files present with good latency (overall ~15–16 ms); no urgent changes indicated from provided data.</li>
      </ul>
      <div class="callout">
        <b>Confidence:</b> Medium (baseline looks fine; workload-specific tuning may still be beneficial).
      </div>
    </li>

    <li>
      <b>Proactive maintenance suggestions (ongoing)</b>
      <span class="badge ok">INFO</span>
      <ul>
        <li>Establish a standard maintenance toolkit (agent jobs) for: CHECKDB, backups, index/statistics maintenance, and purge/retention tasks.</li>
        <li>Implement monitoring for: file latency per DB/file, Query Store size/readonly transitions, failed jobs, and security drift (sysadmin membership).</li>
        <li>Keep SQL Server 2022 patched (you are on RTM-GDR; maintain latest applicable CU/GDR cadence per your policy).</li>
      </ul>
      <div class="callout">
        <b>Confidence:</b> High (general best practices with strong risk reduction).
      </div>
    </li>
  </ol>

  <h2>Targeted “AI SQL Tuner” actions</h2>
  <div class="grid">
    <div class="card">
      <h3>Index optimization (SQLStorm)</h3>
      <ul>
        <li>High-value missing indexes detected in <b>SQLStorm</b>.</li>
        <li>Run comprehensive index analysis:</li>
      </ul>
      <pre><code>aisqltuner -S rockypc -d SQLStorm -goal IndexTuning -E</code></pre>
    </div>
    <div class="card">
      <h3>Deadlocks</h3>
      <ul>
        <li>No deadlocks detected in the past 7 days.</li>
        <li>No FixDeadlocks run recommended at this time.</li>
      </ul>
    </div>
  </div>

  <h2>Key findings snapshot</h2>
  <div class="grid">
    <div class="card">
      <h3>Performance</h3>
      <ul class="kv">
        <li><b>Primary bottleneck:</b> Storage latency outlier (WideWorldImporters.mdf)</li>
        <li><b>Waits:</b> SOS_WORK_DISPATCHER dominates (likely benign); PAGEIOLATCH_SH present</li>
        <li><b>tempdb:</b> Latency ~15–16 ms overall (acceptable)</li>
      </ul>
    </div>
    <div class="card">
      <h3>Security &amp; operations</h3>
      <ul class="kv">
        <li><b>Encryption:</b> 1 unencrypted local telemetry session (minimal risk), recommend standardizing encryption</li>
        <li><b>Privileged access:</b> 6 sysadmin members; apply least privilege; secure/disable <b>sa</b></li>
        <li><b>Integrity:</b> CHECKDB appears missing for all DBs listed (critical)</li>
      </ul>
    </div>
  </div>
</div>