Triage the Plan Cache with AI-Annotated Advice

Tier: Core · Workflow area: SQL Authoring & Query Tuning · ~35 minutes

Single-query tuning (pb-03) assumes you know which query matters. This playbook finds it. The pattern comes from sp_BlitzCache's @AI-style annotation: machine-rank the workload, machine-annotate each offender, human-prune the annotations. The prune is not optional — it is the step that makes the sheet trustworthy.

When to use

• "The database is slow" with no named queryworkload triage converts a vague complaint into a ranked, evidenced shortlist.
• Capacity reviews and quarterly tuning passesthe annotated sheet is a repeatable artifact you can diff quarter over quarter.
• After a deploy shifts the workloadnew top offenders in the plan cache are the earliest sign of an ORM regression or a missing index on a new feature.

When NOT to use

• During an active incidenttriage-by-annotation is a 30-minute analytical loop. Mid-incident you want waits and blocking now (pb-07, pb-06).
• On a cold cacheright after a restart or stats reset, the top-N is noise. Let the workload run long enough to mean something.
• If nobody will act on the outputan annotated sheet that never becomes a tuning ticket is process theater. Commit to implementing at least the top accepted item.

The recipe

1. 1.

   Pull the top offenders with real numbers. Rank by total time (what the server feels) and by mean time (what a user feels) — they surface different problems:

   Copy

   -- PostgreSQL: pg_stat_statements, top 15 by total time
   SELECT queryid,
          left(query, 80)                    AS query_start,
          calls,
          round(total_exec_time::numeric/1000, 1) AS total_s,
          round(mean_exec_time::numeric, 2)  AS mean_ms,
          rows,
          round(100.0 * shared_blks_hit /
            nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 15;
   

   On SQL Server the equivalent single move is sp_BlitzCache @SortOrder = 'cpu' (then 'reads'), or Query Store's top-resource-consumers view.

2. 2.

   Annotate the batch with one structured prompt. Paste the top-N export and ask for a per-row annotation, not an essay:

   Copy

   You are triaging a PostgreSQL 16 workload. Below: the top 15 queries
   from pg_stat_statements with calls, total time, mean time, rows and
   cache hit ratio, plus the normalized query text.
   
   For EACH query return one row:
   - likely_issue: one line (e.g. "N+1 pattern: 2.1M calls, 0.4ms mean",
     "low hit ratio: reads working set from disk", "sort spill suspect")
   - evidence: which numbers from MY data support that reading
   - next_check: the ONE diagnostic to run before any fix
   - candidate_fix: one line, marked [rewrite] [index] [app] or [config]
   - confidence: high / medium / low
   Do not propose fixes for queries whose numbers look healthy; write
   "leave alone" instead.
   

3. 3.

   Prune every row — this is the human step. Go down the sheet and mark each annotation accept / reject / needs-testing, with a one-line reason. A pruned sheet looks like:

   Copy

   queryid    | AI annotation                  | verdict       | reason
   8841002... | N+1: 2.1M calls at 0.4ms      | accept        | matches ORM lazy-load in app repo
   1290583... | missing index on status col    | reject        | partial idx exists; AI can't see
              |                                |               | planner chose seq scan on purpose
   5520091... | sort spill, raise work_mem     | needs-testing | plausible; check log for temp files
   7018334... | "leave alone"                  | accept        | agreed, healthy numbers
   

   Expect to reject 20–40% of annotations. That is the system working: the model reads numbers well but cannot see your partial indexes, your intentional design choices, or last week's incident.

4. 4.

   Run the next_check for everything marked needs-testing. Example — confirming the sort-spill theory before touching work_mem:

   Copy

   SELECT queryid, temp_blks_written
   FROM pg_stat_statements
   WHERE temp_blks_written > 0
   ORDER BY temp_blks_written DESC LIMIT 5;
   

   Evidence found: promote to accept. Not found: reject, note why. Never promote on plausibility alone.

5. 5.

   Implement exactly one fix, measured. Take the highest-value accepted row into the pb-03 loop: snapshot the query's pg_stat_statements numbers, apply the fix on a copy, verify, ship, then compare a same-length window:

   Copy

   SELECT pg_stat_statements_reset();  -- on the copy, for a clean window
   -- ... replay or wait one business cycle, then re-pull the top-15
   

   One measured improvement per pass builds more credibility than five unmeasured ones.

6. 6.

   Keep the sheet as a living artifact. Date it, file it with the tuning tickets, and re-run the pass monthly. The diff between passes — offenders that left the list, new arrivals — is your workload's changelog.

Make it yours

• SQL Server shopsp_BlitzCache @SortOrder='cpu' then @SortOrder='reads', export to the sheet, same annotation prompt with the columns renamed; Query Store gives you the regression view (sys.query_store_runtime_stats) for the before/after, and plan forcing is your emergency brake, not your fix.
• MySQL shopthe workload source is performance_schema.events_statements_summary_by_digest (SUM_TIMER_WAIT, COUNT_STAR, SUM_ROWS_EXAMINED); the rows-examined-to-rows-sent ratio is your strongest annotation input, and pt-query-digest over the slow log is the fallback where performance_schema is trimmed.
• Oracle shopfeed AWR top-SQL sections (elapsed, executions, gets/exec) to the same prompt; validate candidates against V$SQLSTATS and use SQL Monitor reports for the deep dive on any accepted row.

Quality check

• Reset counters knowingly, never casuallypg_stat_statements_reset() destroys your baseline. Snapshot the current numbers first; on shared instances, coordinate before resetting.
• Both rankings were consideredtotal-time offenders and mean-time offenders are different populations; triaging only one hides either death-by-a-thousand-cuts or the single horrible report query.
• Every rejected annotation has a written reasonthe reject log is what tells you, three months in, where this model systematically misreads your workload.
• Fixes flowed through pb-03, not straight to prodannotation confidence is not verification. Plans and before/after numbers remain the only currency.