SQL Server Index Tuning: The 8 Changes That Deliver Immediate Results

Index problems are responsible for the majority of SQL Server performance issues we encounter. Not the most complex or exotic issues — index problems. Missing indexes, unused indexes, fragmented indexes, and duplicated indexes account for more performance headaches than anything else on the list.

The good news: they are also among the most actionable. Here are the eight index changes that consistently deliver the fastest, most measurable improvements — with the queries to find them.

---

1. Add Missing Indexes from DMV Recommendations

SQL Server tracks queries that would have benefited from an index that did not exist. This data lives in sys.dm_db_missing_index_details and is one of the most direct performance signals available.

SELECT TOP 20
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS improvement_score,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_score DESC;

Important caveats: DMV recommendations are suggestions, not instructions. Review each one before applying — SQL Server does not account for index maintenance overhead, storage cost, or write amplification. High improvement_score with high user_seeks is a strong signal. High score with low seeks is worth investigating further before committing.

---

2. Remove Unused Indexes

Every index you maintain has a cost: INSERT, UPDATE, and DELETE operations must update all indexes on the table. Indexes that are never (or rarely) used for reads are pure overhead.

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id
    AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE i.type_desc <> 'HEAP'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
    AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
    AND (ius.user_scans IS NULL OR ius.user_scans < 10)
ORDER BY ius.user_updates DESC NULLS LAST;

Before dropping any index: check that the server has not been restarted recently (DMV data resets on restart), and verify the index is not referenced in any stored procedures or application code. Script the drop — do not apply it directly in production without a rollback plan.

---

3. Rebuild High-Fragmentation Indexes

Fragmented indexes cause SQL Server to read more pages than necessary to satisfy a query. For clustered indexes on busy transactional tables, fragmentation can accumulate rapidly.

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ROUND(ips.avg_fragmentation_in_percent, 1) AS fragmentation_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Guidance:

• Fragmentation < 10%: leave it
• 10–30%: REORGANIZE (online, low overhead)

• 30%: REBUILD (more thorough, can be done online with Enterprise Edition)

Rebuild generates more log activity than reorganize — factor this into your maintenance window planning.

---

4. Update Stale Statistics

The query optimiser uses statistics to estimate how many rows a query will process. When statistics are stale, estimates are wrong — and wrong estimates produce wrong execution plans, which produce slow queries.

SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) NOT LIKE 'sys%'
    AND sp.modification_counter > 1000
ORDER BY sp.modification_counter DESC;

For high-frequency tables with large modification counts, consider running UPDATE STATISTICS manually with a higher sample rate than the default:

UPDATE STATISTICS dbo.YourTable WITH FULLSCAN;

For most databases, ensuring your maintenance plan runs UPDATE STATISTICS regularly is sufficient. The problem arises when maintenance jobs are skipped, disabled, or never set up in the first place.

---

5. Eliminate Duplicate Indexes

Duplicate or near-duplicate indexes are surprisingly common — especially on tables that have grown organically across multiple development cycles. They consume storage, increase write overhead, and provide no incremental read benefit.

SELECT
    t.name AS table_name,
    i1.name AS index1,
    i2.name AS index2,
    i1.type_desc
FROM sys.indexes i1
INNER JOIN sys.indexes i2
    ON i1.object_id = i2.object_id
    AND i1.index_id < i2.index_id
INNER JOIN sys.tables t
    ON i1.object_id = t.object_id
WHERE EXISTS (
    SELECT ic1.column_id, ic1.key_ordinal
    FROM sys.index_columns ic1
    WHERE ic1.object_id = i1.object_id AND ic1.index_id = i1.index_id
    INTERSECT
    SELECT ic2.column_id, ic2.key_ordinal
    FROM sys.index_columns ic2
    WHERE ic2.object_id = i2.object_id AND ic2.index_id = i2.index_id
)
ORDER BY t.name;

Identify which of the duplicates is actually used (via sys.dm_db_index_usage_stats) and consolidate. Prefer the index with the better column coverage — wider included columns over narrower — then drop the redundant one.

---

6. Address Key Lookups on High-Frequency Queries

A key lookup occurs when a non-clustered index satisfies the WHERE clause but the query needs additional columns not in the index. SQL Server fetches those columns by looking up the clustered index row — one per row returned. On high-cardinality result sets, this can destroy performance.

-- Find queries with significant key lookup operations
SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_logical_reads / qs.execution_count > 10000
ORDER BY avg_logical_reads DESC;

The fix: add the frequently-needed columns as INCLUDE columns on the non-clustered index. This converts the key lookup to a covering index scan — dramatically reducing logical reads.

-- Before: index covers WHERE clause only
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID);

-- After: index covers query output columns too
CREATE INDEX IX_Orders_CustomerID ON Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

---

7. Fix Fill Factor on Hot Tables

Fill factor controls how full SQL Server packs data into index pages when building or rebuilding an index. At 100% (the default), pages are packed completely — great for read-heavy workloads, but on insert/update-heavy tables it causes page splits, which cause fragmentation, which causes slow reads.

For tables with high insert/update rates, a lower fill factor leaves space on each page for row growth, reducing splits:

-- Check current fill factor for your indexes
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.fill_factor
FROM sys.indexes i
WHERE i.fill_factor > 0
    AND OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
ORDER BY i.fill_factor;

A fill factor of 80–90 is a reasonable starting point for transactional tables. Set it at rebuild time:

ALTER INDEX IX_YourIndex ON dbo.YourTable
REBUILD WITH (FILLFACTOR = 85);

Monitor fragmentation rates after the change — if fragmentation is still accumulating rapidly, reduce fill factor further. If fragmentation was never an issue, leave it at 100.

---

8. Add Filtered Indexes for Sparse Data Patterns

Standard indexes include every row in the table. When queries consistently filter on a small subset of rows — for example, unprocessed records, active users, or open orders — a filtered index covers only that subset. Smaller index, faster reads, lower maintenance overhead.

-- Example: index covering only unprocessed queue items
CREATE INDEX IX_Queue_Unprocessed
ON dbo.ProcessingQueue (CreatedDate, Priority)
INCLUDE (Payload, RetryCount)
WHERE Processed = 0;

This is particularly effective for:

• Status-based filtering (active/inactive, open/closed)
• Soft deletes (IsDeleted = 0)
• Nullable columns with sparse non-NULL distributions

The query must include the filter predicate in its WHERE clause for the filtered index to be used. Verify with execution plan that the index is being selected.

---

Putting It Together

These eight changes are not theory. They are the moves that consistently appear in the first pass of every SQL Server performance engagement we run. In most cases, addressing the top three to five findings from this list produces a measurable improvement within days — before touching application code, hardware, or architecture.

The order matters. Start with missing indexes and unused indexes — those have the clearest cost/benefit. Then move to fragmentation and statistics. Key lookups, fill factor, duplicates, and filtered indexes are refinements that compound the gains.

If you want to know which of these apply to your environment and in what priority order, an SQLOPTIMISE health check will tell you — with a structured plan, not just a list of findings.

Request a health check →