A Practical Guide to Proving Index Needs in SQL Server

When tuning queries in SQL Server, one of the most common tasks is identifying where indexes are missing (or poorly designed) and proving that new indexes will actually improve performance. Tools like SolarWinds DPA or other monitoring solutions can highlight issues, but sometimes you need to go deeper and use the built-in DMVs and query plan analysis yourself.

Here’s a step-by-step process you can follow from start to finish.

---

1. Establish a Baseline with SET STATISTICS

Start by capturing I/O and time statistics before making any changes. This helps you measure whether a new index actually improves query performance.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Run your query
SELECT ...
FROM TableA a
JOIN TableB b ON a.Key = b.Key
WHERE a.FilterColumn = 'SomeValue';
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

2. Capture Query Plans from the Cache

Next, look at the top cached execution plans to see which queries are consuming resources.

SELECT TOP 50
    qs.last_execution_time,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_worker_time,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
              ((CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC;

SELECT TOP 50
    qs.last_execution_time,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_worker_time,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
              ((CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC;

This identifies high-cost queries and gives you the cached execution plan to review.

---

3. Analyze Predicates and Columns

To see what columns are actually being filtered and joined on, use the execution plan operators or the dm_exec_query_plan XML. Another option is to query DMVs that expose predicate information.

Example to see predicate usage:

SELECT
DB_NAME(st.dbid) AS database_name,
OBJECT_NAME(si.objectid, st.dbid) AS table_name,
sc.name AS column_name,
sp.index_id,
sp.equality_columns,
sp.inequality_columns,
sp.included_columns
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_db_missing_index_details sp
JOIN sys.columns sc ON sp.objectid = sc.object_id
WHERE cp.cacheobjtype = ‘Compiled Plan’;

This helps you identify which columns are repeatedly scanned or filtered.

---

4. Look at Missing Index DMVs

SQL Server itself tracks missing index suggestions. Be careful: these are recommendations, not gospel.

SELECT
DB_NAME(mid.database_id) AS database_name,
OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.user_seeks DESC;

Here you’ll see what SQL Server thinks could help: equality columns, inequality columns, and included columns.

---

5. Build a Test Index

Once you’ve identified a candidate index, create it in dev/test.

CREATE NONCLUSTERED INDEX IX_TableA_FilterColumn
ON TableA (FilterColumn)
INCLUDE (SomeOtherColumn);

• Key columns = columns used in joins and WHERE predicates.
• Include columns = columns selected but not filtered, to avoid lookups.

---

6. Rerun with SET STATISTICS

Run the same query again with STATISTICS IO and TIME enabled. Compare the new numbers to your baseline.

• Did logical reads drop significantly?
• Did CPU and elapsed time improve?
• Is the query plan now using your new index instead of scanning?

If yes, you have proof.

---

7. Remove or Adjust Test Indexes

If the test index isn’t helpful, drop it. Don’t leave redundant or unused indexes in production.

DROP INDEX IX_TableA_FilterColumn ON TableA;

8. Document and Justify

Always record:

• The before stats (reads/time).
• The after stats with new index.
• A screenshot/snippet of the query plan change (scan → seek).
• Justification of why the index matters (e.g., “reduced reads by 95%”).

This way, you’re not just guessing—you’re proving the index’s value.

---

Key Notes

• Missing Index DMVs reset after server restart.
• DMV suggestions don’t consider overlapping or covering indexes.
• Test changes in a lower environment before production.
• Focus on high-impact queries first (top consumers from dm_exec_query_stats).

---

Conclusion

Proving index changes in SQL Server isn’t just about trusting automated tools. By combining baseline statistics, query plan inspection, missing index DMVs, and controlled testing, you can demonstrate exactly why an index is needed (or not).

This process gives you concrete evidence—before and after numbers—that you can share with your team, your DBA lead, or even in audits.

Optional queries to round it out

Index usage after a day or two (prove they’re actually used):

SELECT
  OBJECT_SCHEMA_NAME(i.object_id)+'.'+OBJECT_NAME(i.object_id) AS table_name,
  i.name,
  u.user_seeks, u.user_scans, u.user_lookups,
  u.last_user_seek, u.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats u
  ON u.object_id = i.object_id
 AND u.index_id  = i.index_id
 AND u.database_id = DB_ID()
WHERE i.name IN (
  'IX_ds_active_calc_dates',
  'IX_supplier_supplier_id_cover',
  'IX_setup_state_dsid',
  'IX_setup_coveragetype_dsid',
  'IX_setup_lpreo_dsid',
  'IX_setup_product_category_type_dsid',
  'IX_distribution_setup_details_dsid'
)
ORDER BY table_name, i.name;

Quick size/fragmentation check (sanity):

SELECT
  OBJECT_SCHEMA_NAME(p.object_id)+'.'+OBJECT_NAME(p.object_id) AS table_name,
  i.name,
  ps.avg_fragmentation_in_percent,
  ps.page_count
FROM sys.indexes i
JOIN sys.partitions p ON p.object_id=i.object_id AND p.index_id=i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') ps
WHERE i.name LIKE 'IX_%'
ORDER BY ps.page_count DESC;

Next steps

• Re-run the same test during a “normal load” window to confirm it holds up.
• Keep the “before/after” screenshots together with a small table of logical reads deltas. (If you paste the IO lines here, I’ll write the summary table for you.)
• Watch write-heavy workloads for any noticeable overhead; if a table is very hot on writes, consider consolidating INCLUDEs to just what the query selects.