Prompt Engineering

SQL Server Query Tuning — Plans, Sniffing, Clustering Keys

Fast for most customers, 30x slower for the largest two — the parameter sniffing signature. SQL Server tuning with actual plans, clustering-key economics, and CONVERT_IMPLICIT hunting.

Open in SQL Optimization Prompt

Overview

SQL Server tuning has engine-specific realities that generic SQL advice misses entirely. This prompt carries them: demand actual execution plans with SET STATISTICS IO, TIME ON (estimated plans hide spills and estimate-vs-actual gaps); reason about the clustered index — every nonclustered index carries the clustering key, so a wide key taxes every index; check parameter sniffing when performance varies by parameter — the loaded scenario is exactly that signature, fast for most customers and 30x slower for the largest; and watch for CONVERT_IMPLICIT in the plan, where a VARCHAR/NVARCHAR mismatch turns seeks into scans. A high-execution Key Lookup gets flagged as the covering-index candidate it is.

How to use this resource

Demand the actual plan

SET STATISTICS IO, TIME ON plus the actual plan — estimates hide exactly what matters.
Check the sniffing signature

Performance varying by parameter value points at a plan compiled for unrepresentative values — evidence before OPTIMIZE FOR.
Cost against the clustering key

Every index recommendation accounts for what the clustered index makes it carry.

Why This Works

Engine-specific guidance replaces the advice that ignores how SQL Server actually works
The sniffing check turns a classic mystery into a testable hypothesis
Clustering-key economics keep index advice from being free-lunch thinking

Best for

.NET and SQL Server shops tuning production queries
Parameter-dependent performance mysteries
Plans that look fine estimated and terrible actual

Not for

PostgreSQL or MySQL tuning — platform modes exist because the realities differ
Investigating whether the database is the problem at all — that's the Debugging Prompt Generator

Use cases

Diagnosing queries fast for some parameters and slow for others
Reading actual plans for spills and key lookups
Costing nonclustered indexes against a wide clustering key

FAQ

Why does this prompt insist on the actual execution plan instead of the estimated one?

Estimated plans hide the two things this tuning depends on. The DATABASE CONTEXT requires the Actual Execution Plan with SET STATISTICS IO, TIME ON, since estimates conceal spills and estimate-vs-actual gaps. Where no plan is pasted, the prompt refuses to invent one and instead outputs the exact capture command, marking every plan-dependent conclusion as pending that evidence.

Is this SQL prompt safe to use on PostgreSQL or MySQL queries?

It's SQL Server-specific and will misfire elsewhere. The engine-specific rules — CONVERT_IMPLICIT hunting for VARCHAR/NVARCHAR mismatches, clustering-key economics where every nonclustered index carries the clustering key, and OPTIMIZE FOR/RECOMPILE for parameter sniffing — only hold on SQL Server. notFor points Postgres and MySQL to their own platform modes because the realities differ.

My query is fast for most customers but 30x slower for the biggest two — what does this prompt point at?

That signature points straight at parameter sniffing. The prompt flags performance that varies by parameter as a plan compiled for unrepresentative values, and the loaded scenario — same query, same plan cache, 30x slower for the two largest customers — is exactly that pattern. It still gates OPTIMIZE FOR or RECOMPILE behind evidence from the actual plan rather than applying them blind.

Customize This Resource

Opens this setup in SQL Optimization Prompt. Generate to get the full optimization contract — then adjust the goal, platform, and evidence mode.

Open in SQL Optimization Prompt

Prompt Template

Copy it as-is, or use Open in SQL Optimization Prompt to load it pre-filled and customize it with your own context.

OPTIMIZATION OBJECTIVE
Tune the invoice listing query on SQL Server — it is slow only for some customers.
Optimization goal: query speed — find where the time goes and remove the expensive work.
Establish why this query is expensive before changing anything. Every recommendation must trace to evidence from this query, this plan, or this schema — no generic database advice.

DATABASE CONTEXT
Platform: SQL Server.
- Use actual execution plans with SET STATISTICS IO, TIME ON — estimated plans hide spills and estimate-vs-actual gaps.
- Reason about the clustered index: every nonclustered index carries the clustering key, so a wide clustering key taxes every index.
- Check for parameter sniffing when performance varies by parameter: a plan compiled for unrepresentative values; consider OPTIMIZE FOR or RECOMPILE only with evidence.
- Watch for implicit conversions (VARCHAR vs NVARCHAR) — they appear as CONVERT_IMPLICIT in the plan and turn seeks into scans.
- A Key Lookup executing many times is a covering-index candidate — check its actual executions in the plan.

QUERY CONTEXT
Query:
```sql
[Paste the SQL query here]
```
Execution plan: not provided. Do not invent one. State the exact command to capture it — the Actual Execution Plan plus SET STATISTICS IO, TIME ON — and mark every plan-dependent conclusion as pending that evidence.
Tables and row counts: not provided — do not assume row counts. State the cardinality questions that block firm conclusions.
Existing indexes: not provided — do not assume any index exists. Before recommending new indexes, list what must be checked about the current ones.

PERFORMANCE SYMPTOMS
Fast for most customers; 30x slower for the two largest ones; same query, same plan cache.

OPTIMIZATION GOAL
Primary goal: Query Speed.
Analysis priorities for this goal:
1. Identify the expensive operations first: full scans, large sorts, spools, repeated subquery evaluation.
2. Check whether every filter is sargable — functions, casts, and leading wildcards on filtered columns defeat index use.
3. Question every sort: what ordered it (ORDER BY, DISTINCT, GROUP BY, a merge join) and whether an index could provide the order for free.
4. Reduce rows as early as possible: the cheapest row is the one never read.

EVIDENCE REVIEW
Evidence mode: Standard Analysis.
- Prefer evidence from the provided query, plan, and schema; where evidence is missing, label the assumption explicitly.
- Carry at least two candidate bottlenecks until evidence separates them.

BOTTLENECK ANALYSIS
- Identify the bottlenecks and, for each: what it costs, why it matters for this workload, and the estimated share of the total cost.
- Expected analysis areas for this goal: Scan vs seek on every table access; Sort and memory pressure; Sargability of every predicate; Rows flowing between operators.
- Distinguish the bottleneck from its symptom — a slow sort caused by a missing filter is a filter problem, not a sort problem.

OPTIMIZATION OPPORTUNITIES
- Order opportunities by expected impact, highest first; state the basis for each estimate.
- For each opportunity: the change, the expected effect, and the evidence that predicts it.
- Any query rewrite must return identical results — call out every difference in NULL handling, duplicates, and ordering, or state explicitly that there is none.
- Do not tune speculatively: a change without an evidenced problem is risk without benefit.

INDEX RECOMMENDATIONS
- Every index recommendation must name the exact predicates, joins, or sorts it serves — no index without a clause.
- Justify column order for composite indexes, and say whether the index should cover (and at what storage and write cost).
- State each index's write tax: which inserts and updates it slows, and whether the workload can afford that.
- Check existing indexes first: a near-miss index that could be extended beats a new overlapping one.

TRADEOFF ANALYSIS
- Every recommendation carries its costs: write amplification, storage, maintenance burden, plan-stability risk, staleness (for pre-aggregation).
- State when NOT to apply each recommendation — the workload shape under which it backfires.

ASSUMPTIONS
- List every assumption made about data volumes, value distributions, index state, or workload patterns.
- Mark each assumption VERIFIED (with its evidence) or UNVERIFIED (with the query or command that would resolve it).
- Any recommendation that depends on an UNVERIFIED assumption must be flagged as conditional on it.

NON-GOALS
- Do not invent execution plans.
- Do not assume indexes exist.
- Do not assume row counts.
- Do not recommend changes without justification.
- Separate facts from assumptions throughout.
- Explain the tradeoffs of every change that has them.
- Schema redesign and application-level changes are out of scope — unless the evidence shows no query-level fix exists, in which case say so and stop.

OUTPUT REQUIREMENTS
- Present recommendations as an ordered list, highest expected impact first, each with its evidence and its tradeoffs.
- For each recommendation, include the verification step: how to measure the improvement — the Actual Execution Plan plus SET STATISTICS IO, TIME ON before and after, on production-shaped data.
- Where essential evidence is missing, the first recommendations are the commands to gather it — not guesses in its place.
- End with the open questions: what could not be determined from the provided context, and what would settle each.

More resources from SQL Optimization Prompt

Resource

Optimize SQL Query — Joins, Cardinality, and Wasted Work

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.

Prompt Engineering

Resource

SQL Optimization Prompt — the Evidence-Based Contract

"Optimize this query" gets generic indexing advice. The optimization contract demands evidence: real bottlenecks, justified indexes with their write tax, and no invented plans.

Prompt Engineering

Resource

Execution Plan Analysis — Read What the Engine Actually Chose

Optimize what the engine does, not what the SQL looks like: cost concentration, estimate-vs-actual gaps, and plan warnings — with forensic evidence rules.

Prompt Engineering

Resources that pair well

Resource

Debugging Prompt — the Investigation Contract

"Fix this error" gets guesses. The investigation contract gets a ten-stage diagnosis: facts separated from assumptions, alternatives weighed, fixes justified.

Prompt Engineering

Resource

Code Review Prompt — the Review Contract

"Review this code" gets shallow comments. The review contract gets findings with severities, a checklist, and a verdict.

Prompt Engineering

Resource

Refactor Prompt — the Behavior Preservation Contract

"Refactor this code" invites silent behavior changes. The refactoring contract preserves business rules, outputs, and side effects — and flags uncertainty instead of deciding it.

Prompt Engineering

Related tools

Tool

SQL Optimization Prompt

Build evidence-based SQL optimization prompts — goal, platform, and the evidence you have turn into a query tuning contract.

Coding Workflows

Tip: Save time by exploring related resources and tools that integrate with this resource.