Azure SQL Performance Tuning Prompt
Diagnose a slow Azure SQL Database from query plans, wait stats, and index usage, then propose tuning — indexes, query rewrites, and the right service tier — with safe rollout.
- Target user
- Database and platform engineers running Azure SQL Database
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior database engineer who tunes Azure SQL Database from evidence — execution plans, wait statistics, and the Query Store — not from hunches. You distinguish a query problem from an indexing problem from an undersized-tier problem. I will provide: - The slow query/queries and their execution plan (or plan XML/summary) — [QUERY_AND_PLAN] - Wait stats and resource metrics — DTU/vCore utilization, CPU, IO, log waits, `sys.dm_db_resource_stats` — [RESOURCE_STATS] - Existing indexes on the involved tables and their usage stats — [INDEXES] - Query Store top-consumers / regressed queries if available — [QUERY_STORE] - Service tier and the symptom — what's slow, since when, and under what load — [TIER_AND_SYMPTOM] Your job: 1. **Locate the bottleneck** — read the plan for scans where a seek should be, key lookups, spills, and bad estimates; correlate with wait stats (CPU vs IO vs log vs lock). Don't recommend an index before you know the plan operator that's hurting. 2. **Indexing** — recommend the narrowest index (with the right key order and INCLUDE columns) that turns the costly operator into a seek. Flag duplicate/unused indexes that only slow writes, and resist adding an index when a query rewrite is the real fix. 3. **Query rewrite** — flag SARGability problems (functions on indexed columns, implicit conversions), N+1 patterns, and parameter sniffing; propose the rewrite. 4. **Right-size the tier** — only after query/index tuning, judge whether the workload genuinely needs a higher vCore/DTU tier or whether it was masking an inefficient query. Don't lead with "scale up." 5. **Safe rollout** — recommend testing index changes against a copy/staging, and using Query Store to confirm the plan improved and didn't regress other queries before applying to production. Output as: (a) the diagnosed bottleneck with the plan/wait evidence; (b) the index DDL and/or query rewrite; (c) a tier recommendation only if tuning isn't enough, with rationale; (d) the validation steps (test, measure, watch Query Store) before prod. Reason only from the plan, waits, and index data I gave you. Don't invent table shapes or assume row counts — ask if the plan or stats are missing.
Why this prompt works
The reflex response to a slow Azure SQL Database is to scale up the tier, and it’s almost always the wrong first move — it masks an inefficient query at higher monthly cost and leaves the real problem in place. This prompt refuses to lead with “scale up.” It starts where a real DBA starts: read the execution plan for the operator that’s actually hurting, correlate it with wait statistics to tell a CPU problem from an IO or locking problem, and only then decide whether the fix is an index, a rewrite, or genuinely more capacity. That evidence-first ordering is what makes the tuning stick.
The indexing guidance is deliberately conservative because indexes are not free. Each one speeds reads but taxes every write and consumes storage, so the prompt asks for the narrowest index — correct key order, the right INCLUDE columns — that converts the costly scan into a seek, and it actively looks for cases where a query rewrite (fixing a non-SARGable predicate or an implicit conversion) beats adding an index at all. Grounding this in the real plan and the existing index usage stats means the recommendations target your actual workload instead of bolting on indexes that only slow the write path.
The guardrails keep tuning from becoming its own incident. Adding an index to a write-heavy table without testing the write impact, or flipping the service tier without first ruling out an inefficient query, are the two moves that cost money or cause regressions. By requiring staging tests and a Query Store comparison before production — to confirm the target query improved and others didn’t regress — the prompt lets you tune aggressively while keeping every change measured and reversible.