Procedures & workloads

Per-unit decomposition

Procedure and script workloads are split into per-statement units. Each safe top-level SELECT / WITH unit is optimized independently. Nested SELECT statements inside IF / ELSE are analyzed for indexes and can show preview rewrites. During a performance run, units without measured improvement and a full result match keep their original SQL, including nested units that only have preview analysis. Results are reassembled so you can review the workload as a whole and drill into each unit’s evidence.

When a procedure run qualifies

A stored procedure run requires measured improvement above the threshold to count as a billed optimization. Changing a rewrite unit alone is insufficient. Candidate selection rejects slower, below-threshold, unmeasured, or insufficiently compared rewrites. Measurements of isolated units do not establish the runtime of the whole procedure. Index-only procedure results are billed the same way as index-only query results. Runs that find indexes or advisories below the threshold are still reported, but they do not consume an optimization from your quota.

Unit evidence

For each optimized unit, the report can show the original and optimized SQL, the rules that fired, why a candidate was selected, and which alternatives were evaluated. Saved procedure runs keep this per-unit detail available when you reopen or export them. The Execution plan tab EXPLAINs each standalone statement after substituting procedure parameters, including nested IF/ELSE SELECTs and DML. Switch statements in that tab to compare their plans; the Overview tab still lists every unit in the routine.

Advisory-only paths

Some constructs deliberately never get automatic rewrites: procedural DML, SELECT … INTO, dynamic SQL, recursive CTEs, and trigger bodies. These surface as advisory findings with review guidance, so “no rewrite” never means “nothing to report.”

Confidence and completeness

Every result carries a strategy, a confidence level with reasons, and an input-completeness readout (for example, missing table definitions). Jobs and history views let you filter by strategy, workload type, and confidence.