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 `` 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,
    mig.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;

When DMV output is provided, treat each row as a Source C candidate and use weighted_impact as the ranking score rather than the optimizer's static Impact percentage.

How to Run

1. Source A — Operator scan: Walk every `` node and apply the derived rules (D1–D10) below
2. Source B — Explicit extraction: Extract all `` elements
3. Source C — DMV parsing: If DMV output is present, parse each row into a candidate (table, equality cols, inequality cols, include cols, weighted_impact)
4. Unified merge: Combine A, B, and C by table, apply merge rules, deduplicate
5. Rank the merged set by score
6. Generate DDL with width checks applied

---

Source A: Operator-Derived Recommendations

Apply these rules to every operator node. Each fired rule produces a candidate recommendation with an estimated impact derived from the operator's costPercent or actualExecutions.

D1 — Key Lookup / RID Lookup: Extend NC Index

When: physicalOp = Key Lookup or RID Lookup

What to extract from the plan:

• The nonclustered index being seeked (from the parent Nested Loops' seek predicate)
• The seek predicate columns → these become the key columns of the existing NC index
• The output list columns being fetched via the lookup → these become new INCLUDE candidates

Recommendation: Extend the seeked NC index with the lookup's output columns as INCLUDE columns. This eliminates the lookup entirely.

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId]
ON [dbo].[Orders] ([CustomerId])
INCLUDE ([Status], [TotalAmount])   -- add these to kill the lookup
WITH (ONLINE = ON, DROP_EXISTING = ON, SORT_IN_TEMPDB = ON);

Estimated impact: min(90, costPercent) — the lookup's plan share is the impact; no multiplier needed since Key Lookup cost already reflects the round-trip penalty.

Cross-reference: sqlplan-review N5

---

D2 — Expensive Scan: Add Seek Index

When: physicalOp = Index Scan or Table Scan AND costPercent ≥ 25% AND a predicate is present on the operator

What to extract:

• Table and schema name
• Predicate columns: equality columns first, then range/inequality columns
• Output columns that are always fetched (candidates for INCLUDE)

Recommendation: NC index on the predicate columns. Add frequently fetched output columns as INCLUDE only if they meaningfully narrow the query's I/O (avoid including every SELECT column).

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_CreatedDate]
ON [dbo].[Orders] ([CustomerId], [CreatedDate]);

Estimated impact: costPercent directly — the operator's plan share is the impact.

Cross-reference: sqlplan-review N4, N39

---

D3 — Residual Predicate on Seek: Promote Column to Key

When: A Seek operator has both ` AND (residual), AND when runtime data is present actualRows / actualRowsRead )

• Residual predicate column and operator (= / > / LIKE etc.)

Recommendation: Create a new index that includes the residual column as a key column (not INCLUDE — it must narrow the B-tree traversal, not just be available at the leaf).

-- Current index: IX_Orders_CustomerId
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_Status]
ON [dbo].[Orders] ([CustomerId], [Status]);   -- Status as key, not INCLUDE

Note: If the residual column is low-cardinality (e.g., a boolean or 3-value status), consider D9 (Filtered Index) instead — a filtered index avoids including the low-cardinality column on all rows.

Estimated impact: min(80, (1 - actualRows/actualRowsRead) × 100) — scaled by how much the residual discards.

Cross-reference: sqlplan-review N43

---

D4 — Eager Index Spool: Replace with Permanent Index

When: logicalOp = Eager Spool AND operator name contains "Index" (distinguishes from table spools)

What to extract:

• The spool's seek predicate — this is exactly what the temporary index is built on at runtime
• Output columns of the spool — INCLUDE candidates

Recommendation: A permanent NC index matching the spool's seek predicate eliminates the runtime index build entirely.

CREATE NONCLUSTERED INDEX [IX_LineItems_LineItemId]
ON [dbo].[LineItems] ([LineItemId])
INCLUDE ([OrderId], [Quantity], [Price]);

Estimated impact: 85 — Eager Index Spools always indicate a missing index and always carry significant overhead.

Cross-reference: sqlplan-review N2

---

D5 — Costly Sort: Add Pre-Sorted Index

When: physicalOp = Sort AND costPercent ≥ 10%

What to extract:

• Sort column list and direction (ASC/DESC for each)
• The WHERE predicate of the upstream scan or seek (to include as leading key columns)

Recommendation: NC index whose key columns match the upstream filter first (equality columns), then the sort columns in the correct direction. This allows SQL Server to read data pre-ordered and skip the Sort entirely.

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_OrderDate_Desc]
ON [dbo].[Orders] ([CustomerId], [OrderDate] DESC);

Estimated impact: costPercent directly.

Cross-reference: sqlplan-review N22

---

D6 — High-Count Nested Loops: Index the Inner Side

When: physicalOp = Nested Loops AND actualExecutions > 1,000 AND the inner side operator is a Scan (no seek on the join column)

What to extract:

• Inner side table name
• Outer references — the correlated columns passed from the outer loop to the inner side (these are the join columns)

Recommendation: NC index on the inner table's join column(s). This converts the inner-side scan to a seek, reducing each loop iteration from O(n) to O(log n).

CREATE NONCLUSTERED INDEX [IX_LineItems_OrderId]
ON [dbo].[LineItems] ([OrderId]);

Estimated impact: min(85, actualExecutions / 100) capped at 85 — high execution counts amplify the per-iteration seek benefit.

Cross-reference: sqlplan-review N15

---

D7 — Heap Scan: Add Clustered Index

When: physicalOp = Table Scan (indicates a heap — table with no clustered index)

What to extract:

• Table name
• Filter predicate columns if present (suggest as clustered key candidates)

Recommendation: A clustered index converts the heap to a B-tree, enables ordered access, and reduces fragmentation. Suggest the most selective filter column as the clustering key, or the natural identity/PK column.

-- If no natural key exists, use an identity:
ALTER TABLE [dbo].[StagingOrders] ADD [Id] INT IDENTITY(1,1) NOT NULL;
CREATE CLUSTERED INDEX [CIX_StagingOrders_Id] ON [dbo].[StagingOrders] ([Id]);

-- If a natural key exists:
CREATE CLUSTERED INDEX [CIX_StagingOrders_OrderRef] ON [dbo].[StagingOrders] ([OrderRef]);

Estimated impact: 60 (fixed) — heaps have consistently higher scan overhead than clustered tables.

Cross-reference: sqlplan-review N39

---

D8 — Backward Scan: Add DESC Index

When: Any scan or seek operator has ScanDirection = BACKWARD

What to extract:

• The index being scanned and its current key column directions
• The columns and directions needed for a forward scan that produces the same order

Recommendation: A new index with the sort direction reversed on the relevant key column eliminates the backward scan. Backward scans have modestly higher CPU cost than forward scans due to latch ordering differences.

CREATE NONCLUSTERED INDEX [IX_Orders_CreatedDate_Desc]
ON [dbo].[Orders] ([CreatedDate] DESC)
INCLUDE ([CustomerId], [Status]);

Estimated impact: 22 (fixed) — modest CPU saving; backward scans are rarely the primary bottleneck. Validate with sys.dm_os_wait_stats PAGELATCH data before prioritizing this fix over higher-impact recommendations.

Cross-reference: sqlplan-review N12

---

D9 — Filtered Index Opportunity

When: A seek or scan has an equality predicate on a low-cardinality column (≤ 10 distinct values estimated) AND the predicate's selectivity discards > 80% of rows — e.g., IsDeleted = 0 where 98% of rows have IsDeleted = 1, or Status = 'Active' on a table where most rows are 'Completed'

What to extract:

• Table and schema name
• The highly selective equality predicate column and constant value
• The remaining predicate and output columns (→ key and INCLUDE of the filtered index)

Why a filtered index rather than a standard key-column index: Adding a low-cardinality column to the key of a full index writes an index entry for every row, including the 98% your query never touches. A filtered index stores only the matching subset — it is narrower, cheaper to maintain on writes, and faster to scan because the entire index fits in far fewer pages.

CREATE NONCLUSTERED INDEX [IX_Tasks_Status_Pending_AssignedUserId]
ON [dbo].[Tasks] ([AssignedUserId], [CreatedDate])
INCLUDE ([Priority], [DueDate])
WHERE Status = 'Pending'
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

Caveats:

• Filtered indexes only benefit queries that include the filter predicate as a literal constant or a properly typed parameter — the query optimizer will not use IX_..._Pending to satisfy WHERE Status = @status unless the plan is forced or the parameter value is sniffed to 'Pending'.
• Filtered statistics generated by the optimizer (auto-created) may not cover filtered index predicates correctly; manual statistics update may be needed after creation.
• Do not use on columns with high cardinality or evenly distributed values — a standard seek index is better in that case.

Estimated impact: min(85, (1 − selectivity_pct) × 100) — scaled by what fraction of the table is excluded from the filtered index.

Cross-reference: sqlplan-review N4, D3

---

D10 — Hash Match Probe Side: Add Join Index

When: physicalOp = Hash Match (Join, not Aggregate) AND the probe-side input operator is a Scan (no seek) AND costPercent ≥ 20%

Hash Match joins are build-then-probe: the optimizer builds a hash table from the smaller input, then probes it for each row from the larger input. When the probe side is a scan, adding an index on the probe-side join column often allows the optimizer to switch to Merge Join (which requires no hash table build) or a much cheaper nested-loops seek pattern.

What to extract:

• Probe-side table name (typically the larger input — identified by the Probe child of the Hash Match node)
• The join predicate column(s) on the probe side — these become the key columns
• Probe-side output columns referenced further up the plan — INCLUDE candidates

Recommendation:

CREATE NONCLUSTERED INDEX [IX_OrderLines_OrderId]
ON [dbo].[OrderLines] ([OrderId])
INCLUDE ([ProductId], [Quantity], [UnitPrice])
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

Note: The optimizer will not always switch to Merge Join — it depends on whether both sides can now be presented in sorted order. If after adding this index the plan still uses Hash Match, check whether the build side also lacks a sorted seek; if so, both sides may need indexes to unlock Merge Join.

Estimated impact: min(80, costPercent) — the hash match's plan share is the upper bound.

Cross-reference: sqlplan-review N6 (Hash Match spill), N7 (Hash Match memory grant)

---

Source B: Optimizer Explicit Suggestions

Extract all `` elements across all input plans.

Per suggestion, extract: Impact, Database/Schema/Table, EQUALITY columns, INEQUALITY columns, INCLUDE columns.

Key column order rule: EQUALITY columns always precede INEQUALITY columns, regardless of XML order.

Note on Impact scores: The Impact percentage reflects the optimizer's single-query cost estimate. A score of 99.999 (the cap) means the optimizer thinks the query would be effectively free with the index — treat these as high-priority but verify the query actually runs frequently enough to justify the write overhead.

---

Source C: DMV-Based Suggestions

When sys.dm_db_missing_index_group_stats output is pasted, treat each row as a candidate. The weighted_impact column (avg_total_user_cost × avg_user_impact × (user_seeks + user_scans)) is a much better ranking signal than the static optimizer Impact because it accounts for how frequently the query actually runs (both seeks and scans).

Extract per row: table_name, equality_columns, inequality_columns, included_columns, weighted_impact.

Note: Missing index DMV data is cleared on SQL Server restart. If user_seeks is low but the server was recently restarted, the data may be incomplete.

---

Unified Merge

After collecting all candidates from Sources A, B, and C, group by (Schema, Table) and apply:

Merge Rules (within each table group)

1. Identical keys — merge INCLUDE columns, keep higher impact score
2. One is a prefix of the other — wider key subsumes narrower; merge INCLUDEs
3. Overlapping, not prefix — keep as separate indexes; flag the overlap
4. Completely distinct keys — keep as separate indexes

When a Source A (derived) candidate and Source B/C (optimizer/DMV) candidate overlap for the same table, merge them — the optimizer's Impact or the DMV's weighted_impact takes precedence over the derived estimate when both are available.

Filtered index candidates (D9) are never merged with full indexes. A filtered index serves only queries containing its filter predicate; merging it into a full index defeats the purpose.

Label each merged recommendation with its sources: [optimizer], [dmv], [derived: D1, D5], [both], etc.

---

Ranking

After merging, rank by:

Score = Impact × ln(1 + QueryCount)

• Impact — DMV weighted_impact if available (preferred); optimizer Impact if only a plan file; derived estimate otherwise
• QueryCount — how many distinct queries (plan files or DMV entries) were merged here; logarithmic to avoid over-weighting broad but shallow suggestions

Sort descending.

---

Width Check

Before generating DDL, flag:

| Condition |

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.