Treat an AI-generated CREATE INDEX statement as a hypothesis until the representative workload shows a useful net improvement.
A missing-index hint is a starting point
SQL Server produces missing-index suggestions from optimizer estimates for one query. They are not measurements of an index that has been built and tested. The suggestions do not specify key-column order, and large INCLUDE lists receive no index-size cost-benefit analysis. Review overlapping indexes before adding another.
An AI assistant can help explain a proposal. Ask it which filter, join, or ordering requirement each key column serves and why each included column is needed. Added indexes consume storage and add maintenance work to affected writes.
Keep three kinds of evidence separate
Define the affected operation, its latency target, data volume, and load. Use representative parameter groups, including small and large tenants where relevant. Record application latency, SQL duration, CPU, logical reads, and execution count.
Use a targeted actual execution plan for runtime row counts and warnings. Capturing it by running a statement executes that statement; use an approved workload and environment. Query Store supplies stored plans and runtime statistics aggregated by time interval, not a per-request trace or an application p95. Confirm that it is capturing the relevant workload before comparing periods.
Choose the smallest testable change
Compare the proposal with existing indexes, including key order, included columns, filters, and uniqueness. Identify the writes that would maintain it. Do not remove an existing index merely because some columns overlap.
For a tenant order list filtered by TenantId and ordered by CreatedUtc then Id, that key sequence is a candidate to test. If another important query also filters by Status, compare its plan rather than assuming one column order serves both. A seek is not an acceptance criterion; measure the work and the result.
This is an illustrative investigation brief. No index or workload was executed for this note.
For the proposed index, return: - the query shape and parameter groups it should help; - the existing index that comes closest; - the expected change in reads, CPU, sorting, or lookups; - affected writes and expected storage growth; - a controlled test and production stop conditions. Do not create or drop production indexes.
Separate a failed experiment from a rollback
Build the candidate in a controlled environment with comparable data. Repeat the same parameter mix and load. Evaluate the application target alongside the mechanism you expected to improve: fewer reads, less CPU, fewer lookups, or removal of a costly sort. Every metric need not decrease. Check write latency, blocking, storage, and log activity against limits agreed before the test.
If the candidate is unused or provides no useful benefit, pause the proposal and investigate. That observation alone is not a production incident: the optimizer may prefer another access path. After an approved rollout, use the agreed rollback procedure when correctness fails or material latency, write, blocking, or resource limits are breached.
Observe the relevant business cycle before deciding whether the index earns its ongoing cost. Retain the before-and-after evidence, including regressions and parameter groups that did not benefit.
Bring an unresolved tradeoff to a performance review
If the read benefit, write cost, or rollout risk remains unclear, bring the query, existing indexes, workload summary, and measurements to Mottobits. Performance consulting has an $8,000 USD minimum engagement, with specialist rates starting at $250/hour. Scope, rate, estimated hours, and total commitment are agreed first; implementation is scoped separately.