Make an AI index recommendation prove itself

The problem: one query produces one confident answer

A slow .NET request reaches SQL Server, the plan shows a missing-index suggestion, and an AI assistant returns a polished CREATE INDEX statement. The output looks decisive because it names columns and an estimated improvement. It is still a proposal.

Microsoft documents that missing-index suggestions are estimates produced while optimizing a single query, before execution. They are not tested after the query runs. Suggested key order can be incomplete, large INCLUDE lists receive no size cost-benefit analysis, and similar suggestions can overlap existing indexes. Each added nonclustered index also consumes space and adds work to inserts, updates, and deletes.

Start with the customer operation, not the hint

Name the affected operation and its limit first: for example, the tenant order search must remain below an agreed p95 latency at a stated concurrency and data volume. Capture representative parameter shapes, including ordinary and slow cases. Record application latency, SQL duration, CPU, logical reads, execution count, and the actual plan.

An actual execution plan contains runtime information and warnings. Query Store retains queries, plans, and runtime statistics across time windows, which makes it useful for comparing a controlled before-and-after period. Neither replaces an application-level result: the page must still return the correct tenant's orders, sort them correctly, and preserve pagination.

Ask the agent to design an experiment

Give the assistant the sanitized query, actual plan, relevant schema, existing indexes, representative parameter set, and baseline summary. Ask it to explain why the proposed key order and included columns serve the workload. Require it to identify overlapping indexes and the write paths that touch the table.

The following is an illustrative brief, not a test executed against a customer system.

Investigate the tenant order-search query.
Do not create, drop, or modify an index in production.

Before proposing DDL:
- separate observed facts from optimizer estimates;
- compare the proposal with every existing index on the table;
- explain key order and each included column;
- list affected INSERT, UPDATE, and DELETE paths;
- define the rollback statement and rollout stop conditions.

Return one smallest testable change and the evidence still missing.

Use a falsifiable acceptance check

Build the candidate index in a controlled environment with comparable data. Run the same parameter mix and concurrency before and after. The change passes only if the agreed application latency improves or stays within its target, the selected query's duration and logical reads move in the expected direction, correctness checks remain unchanged, and write throughput, blocking, storage, and log activity remain inside limits chosen before the test.

Then observe the controlled rollout through Query Store long enough to include the relevant business cycle. Stop or roll back if another important query regresses, write latency crosses its limit, the expected plan does not use the index, or the application result changes. A single faster execution is not acceptance evidence.

When the investigation needs specialist judgment

Use the agent work kit to package the evidence and the performance playbook to structure the investigation. Mottobits specialist work starts at $250/hour; the scope, hourly rate, and estimated hours are agreed before starting, and implementation is scoped separately. The purpose is an evidence-backed fix plan, not an index created from an attractive percentage.