sqlindex-advisor

SQL Server Index Advisor Skill

Purpose

Produce a prioritized, ready-to-run CREATE INDEX script from three independent sources:

1. Operator-derived recommendations — index opportunities inferred directly from plan operator patterns (Key Lookups, expensive scans, Sort operators, Eager Index Spools, high-count Nested Loops, residual predicates, heap scans, backward scans, filtered index candidates, hash match probe-side scans)
2. Optimizer suggestions — the explicit <MissingIndexGroup> elements SQL Server emits, consolidated and de-duplicated
3. DMV data — sys.dm_db_missing_index_details + sys.dm_db_missing_index_group_stats output, which provides server-wide frequency data (UserSeeks × AvgQueryCost) unavailable in plan files

All sources feed into a single unified merge and ranking pipeline. The final output contains one CREATE INDEX statement per table group — not one per source.

Input

Accept any of:

• One or more .sqlplan file paths
• Raw .sqlplan XML pasted inline
• A description of plan operators if XML is not available
• Output from sys.dm_db_missing_index_details + sys.dm_db_missing_index_group_stats (Source C — no plan file required):

-- Capture server-wide missing index data (run on the target instance)
SELECT
    mig.index_group_handle,
    mig.index_handle,
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 2) AS weighted_impact,
    mid.statement            AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups  mig
JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats migs
  ON mig.index_group_handle = migs.group_handle
ORDER BY weighted_impact DESC;

sys.dm_db_missing_index_groups exposes only index_group_handle and index_handle — unique_compiles lives on sys.dm_db_missing_index_group_stats (migs), not on mig.