Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.
Query text is the intention; the execution plan is the truth. This prompt uses the plan-analysis goal — work from the actual plan, find the one or two operators where the cost concentrates, compare estimates against actuals (large gaps mean stale statistics or unmodelable predicates, and invalidate downstream choices), and follow row counts between operators, because where rows explode or collapse is where the story is. The forensic mode makes it rigorous: every conclusion cites its operator, missing evidence becomes the exact command to gather it, and generic advice is forbidden. The loaded scenario — a settlement batch that tripled its runtime — starts where plan work usually starts: the plan not yet captured.
Capture before concluding
No plan, no analysis: the first recommendation is the platform's capture command.
Find the concentration
One or two operators usually own the cost — the rest is noise to ignore.
Trust actuals over estimates
Where they diverge, fix the statistics story first — downstream choices depend on it.
It refuses to invent one. The QUERY CONTEXT section marks 'Execution plan: not provided. Do not invent one.' and instructs the output to state the exact capture command instead — the Actual Execution Plan plus SET STATISTICS IO, TIME ON — with every plan-dependent conclusion flagged pending that evidence. Missing evidence becomes a command to gather, never a guess.
That labeling comes from the Forensic Optimization evidence mode. Every statement is tagged FACT (from the provided query, plan, or schema), ASSUMPTION (believed without evidence), or HYPOTHESIS (a testable claim with its test), each recommendation carrying a confidence level tied to evidence strength. It's the rule that bars generic advice — anything that would read the same without seeing this query.
It treats that gap as the lead signal, not a side note. Analysis priority 3 states a large estimate-vs-actual gap means stale statistics or unmodelable predicates and 'invalidates every downstream choice' — so cardinality gets resolved before any operator-cost reasoning built on top of it is trusted. The divergence sets the investigation order.
Treat it as SQL-plan-only. Everything is scoped to reading a database execution plan operator by operator — cost concentration, plan warnings like CONVERT_IMPLICIT and spills, Key Lookup executions. Its NON-GOALS section puts application-level changes out of scope entirely, and schema redesign too unless the evidence shows no query-level fix exists — so an app endpoint needs a different prompt.
A five-table join that got slow as data grew: establish cardinality first, check each join strategy against the data shape, and hunt the fan-out doing wasted work.
"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.
Map every predicate, join, and sort to the index that serves it — or doesn't. Composite order rules, covering decisions, and the write tax nobody mentions.
"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.
"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.
"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.
Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.
Ask AI to "make this query faster" and it guesses — it never saw your execution plan and doesn't know your indexes or row counts. Optimize a slow SQL query with AI instead: bring the evidence, diagnose before you change, and prove the result before it ships.