Every DBA learns the same lesson eventually: do not blindly create the indexes recommended by sys.dm_db_missing_index_details. The DMV will recommend an index every time the optimizer cannot find a perfect seek for a query. It does not consider the cost of maintaining that index across writes. It does not deduplicate against existing indexes that would partially serve the same query. It does not know that the query in question runs once a quarter and the table is written to a million times a day.

The LLM alone has the opposite failure mode. It reasons about workload patterns and trade-offs articulately, but it cannot see the actual missing-index telemetry, and it will confidently recommend indexes that the optimizer already considered and rejected for good reason. Its hallucinations are also softer than the DMV's: a wrong DMV index is concretely wrong; a wrong AI index recommendation can sound plausible long enough to make it into a migration script.

The combination works. Each fixes the other's failure mode.

What the DMV gives you

The missing-index DMVs (sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats) report indexes the optimizer wishes it had. The classic query joins the three and ranks candidates by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans).

What you get back is a list of (table, equality columns, inequality columns, included columns, expected impact). It is mechanically correct. It is operationally dangerous, because:

  1. It treats every miss equally, regardless of how often the underlying query runs.
  2. It does not deduplicate. You will get five recommendations on the same table with overlapping columns.
  3. It does not consider write cost. The DMV is read-only telemetry; it has no idea the table is the target of a high-frequency insert.
  4. It does not consider whether the existing indexes already cover the query if you add one included column. The DMV will recommend a new index when modifying an existing one would be cheaper.

DBAs who survive the first year of operating a database learn to read DMV recommendations as hypotheses, not instructions.

What the AI adds

Hand the LLM the missing-index recommendation along with: the existing indexes on the table, the top queries hitting that table from sys.dm_exec_query_stats, and the write pattern from sys.dm_db_index_operational_stats. Ask it to evaluate the recommendation across read and write costs.

A reasonable model returns something like:

"The DMV recommends an index on (CustomerId ASC, OrderDate DESC) INCLUDE (Status, TotalAmount). The existing IX_Orders_CustomerId already covers CustomerId. Adding OrderDate as the second key column would help the date-range query in sp_GetCustomerOrders, which runs about 4,200 times per hour. However, the table sees about 12,000 inserts per hour and the existing IX_Orders_CustomerId is already wide. Recommendation: extend the existing index to add OrderDate as a second key, rather than create a new index. Also consider whether the INCLUDE columns are necessary — if sp_GetCustomerOrders only reads Status and TotalAmount in 8% of calls, drop them to reduce write amplification."

That paragraph is more useful than the DMV's recommendation alone, because it is reasoning about the trade-off, not just stating the recommendation. It is also reasoning over information the DMV does not have — the fact that the existing index can be extended.

The risk is the AI confidently inventing facts. If the model says "your existing IX_Orders_CustomerId is wide and includes Status," verify that's actually true. The model has a tendency to hallucinate index definitions. Always check against sys.indexes and sys.index_columns.

The combined workflow

The pattern that holds up:

  1. Pull DMV candidates. The standard ranked-recommendations query, filtered to the database and tables you care about.
  2. Pull existing indexes. sys.indexes joined with sys.index_columns and sys.columns for each table in the candidate list.
  3. Pull workload weights. Top 50 queries against the candidate tables from sys.dm_exec_query_stats, plus operational stats from sys.dm_db_index_operational_stats.
  4. Hand all three to the model. Prompt: "Given these missing-index candidates, the existing indexes on each table, and the top queries hitting each table, recommend the smallest set of index changes that meaningfully improves the workload. Prefer modifying existing indexes over creating new ones. Account for write amplification."
  5. Verify the model's claims. Spot-check the existing-index claims and the workload claims against the underlying queries. Treat the output as a draft.
  6. Run in staging first. Even after AI review and human verification, no index hits production without a staging run that confirms the workload still completes within its SLA.

What this saves

A senior DBA does this analysis manually for the top 5-10 indexes per database in their care. With the combined workflow, the analysis covers the top 50 in the same time. The marginal recommendations are the ones that historically went unmade because there were not enough analyst-hours to chase them.

The result is not more indexes. The result is a smaller set of better-targeted index changes — typically extensions of existing indexes rather than new ones, typically with INCLUDE lists pruned by reading the actual query patterns rather than the DMV's suggestion, typically deduplicated against indexes the previous DBA created in 2018 and never revisited.

Where this still breaks

Workload heterogeneity. If the table has multiple distinct workloads — read-heavy reporting and write-heavy OLTP, say — the AI's recommendation collapses them into one optimization target. You have to prompt explicitly about which workload to optimize for, or split the table.

Filtered indexes. The DMV does not recommend filtered indexes. The model can reason about them, but only if you prompt it to consider them. The default workflow misses cases where a 20%-selectivity filter would make an otherwise-bloated index efficient.

For both, the workaround is prompting. Once you have a couple of cycles under your belt, your prompt accumulates the failure modes you have seen and the model picks them up.

The longer-form treatment is in The Birth of Bob, particularly the chapter on what the autonomous remediation layer is and is not allowed to apply (ALTER INDEX ... REBUILD and UPDATE STATISTICS only — index creation and modification stays on the human side of the gate).