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

Index tuning is one of the highest-leverage interventions available to a SQL Server DBA. Done well, it delivers dramatic performance improvements — often without touching application code, without adding hardware, and without a lengthy migration project.

The challenge is that indexing advice tends to fall into one of two camps: overly simplistic ("just add the index SQL Server suggests!") or overwhelmingly academic. What follows is neither. These are eight concrete, targeted changes that address the most common and most impactful index problems in production SQL Server environments.

Before You Start: Know Your Workload

Index tuning without workload data is guesswork. Before making any changes, arm yourself with:

1. Wait statistics — what is SQL Server waiting for? High CXPACKET suggests parallelism issues; high PAGEIOLATCH suggests I/O pressure that missing indexes often cause.
2. Top queries by total elapsed time — from sys.dm_exec_query_stats, sorted by total_elapsed_time DESC
3. Missing index DMVs — sys.dm_db_missing_index_details joined with sys.dm_db_missing_index_group_stats

These three sources tell you where to focus. Without them, you risk adding indexes that help no one and slow down writes everywhere.

---

Change 1: Implement the Highest-Impact Missing Indexes

SQL Server tracks queries that could have benefited from an index and does not have one. This data is exposed through the missing index dynamic management views.

SELECT
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    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_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
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Important caveats:

• SQL Server suggests single indexes in isolation. Multiple suggestions for the same table may be consolidatable.
• Do not implement every suggestion blindly — index creation has write overhead.
• Focus on the top 5–10 by improvement_measure. The long tail is often not worth it.
• Test in a non-production environment first where possible.

A well-targeted missing index implementation routinely delivers 30–60% query time reductions on affected queries.

---

Change 2: Drop Indexes Nobody Is Using

Every index on a table must be maintained on every INSERT, UPDATE, and DELETE. Unused indexes consume storage and slow writes for zero read benefit.

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
JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE ius.database_id = DB_ID()
    AND i.type_desc != 'HEAP'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND ius.user_seeks = 0
    AND ius.user_scans = 0
    AND ius.user_lookups = 0
ORDER BY ius.user_updates DESC;

Before dropping:

• Check that SQL Server has been running long enough for the stats to be meaningful (the DMV resets on SQL Server restart — avoid acting on stats from a recently rebooted server)
• Verify the index is not used by a specific job that runs infrequently (month-end, quarter-end)
• Script out the drop so you can recreate quickly if needed
• Disable rather than drop initially if you are cautious

Removing unused indexes directly reduces write latency and often reduces storage requirements significantly on heavily written tables.

---

Change 3: Build Covering Indexes to Eliminate Key Lookups

A key lookup (formerly known as a bookmark lookup) occurs when SQL Server uses a non-clustered index to find the row but then has to go back to the clustered index (or heap) to retrieve columns not in the non-clustered index. On large tables, these are expensive.

Look for key lookups in execution plans — they appear as Key Lookup (Clustered) nodes, often with a Nested Loops join. The cost of the lookup is usually displayed and is frequently the dominant cost in the plan.

The fix is to convert the index to a covering index by including the additional columns:

-- Before: index only covers the seek column
CREATE INDEX IX_Orders_CustomerID
ON Sales.Orders (CustomerID);

-- After: index covers the columns needed by the query
CREATE INDEX IX_Orders_CustomerID
ON Sales.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

The INCLUDE clause adds columns to the leaf level of the index without adding them to the key — so the index stays narrow for seeks, but the query can satisfy all column requirements without a lookup.

Target high: a covering index on a query that runs 10,000 times per day with a 50ms key lookup eliminates 500 seconds of cumulative wait time daily.

---

Change 4: Review and Fix Fragmented Indexes

Index fragmentation degrades read performance by making sequential scans ineffective — pages are out of order on disk (external fragmentation) or sparsely populated (internal fragmentation). For spinning disk this matters enormously; for SSD it matters less, but it still affects the efficiency of range scans.

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

Standard guidance:

• 10–30% fragmentation: ALTER INDEX ... REORGANIZE — online, low-impact
• >30% fragmentation: ALTER INDEX ... REBUILD — offline (or online with Enterprise Edition)

The bigger win here is often implementing automated maintenance if you do not have it. Ola Hallengren's SQL Server Maintenance Solution is the industry standard and is free. If you are running maintenance manually or not at all, implementing automated nightly index maintenance is a one-time effort that pays dividends indefinitely.

---

Change 5: Align Indexes With Actual Query Patterns, Not Assumptions

One of the most common mistakes in index design is building indexes based on what a DBA assumes the queries will be, rather than what the queries actually are.

Pull the top queries by resource consumption and examine their execution plans. Ask for each one:

• What columns are in the WHERE clause?
• What columns appear in JOIN conditions?
• What columns are in ORDER BY?
• What additional columns does the SELECT need?

Then look at what indexes exist on the relevant tables and assess whether they match. Often you will find:

• Indexes on columns nobody is filtering on anymore (application changed, index did not)
• Composite indexes where the column order does not match query predicates
• Indexes designed for a reporting query that no longer runs

Realigning indexes with actual workload — not historical assumptions — is frequently the highest-value indexing work available.

---

Change 6: Add Filtered Indexes for Selective Queries

If a query always filters on a specific value or range — and that value covers a small percentage of the table — a filtered index can be dramatically more efficient than a full index.

-- Standard index: covers all orders
CREATE INDEX IX_Orders_Status
ON Sales.Orders (Status, OrderDate);

-- Filtered index: covers only open orders (5% of the table)
CREATE INDEX IX_Orders_OpenOrders
ON Sales.Orders (OrderDate, CustomerID)
WHERE Status = 'Open';

The filtered index is smaller, cheaper to maintain, and more selective — making seeks against it faster.

Good candidates for filtered indexes:

• "Active" or "Open" records where closed/completed records are rarely queried
• Nullable columns where queries filter on IS NOT NULL
• Status-driven workflows where one status dominates the active workload

Limitations: filtered indexes only help queries whose predicates match the filter. SQL Server will only use the filtered index if the query's WHERE clause is compatible.

---

Change 7: Fix Implicit Conversions That Disable Index Seeks

Implicit conversions are a silent index killer. They occur when SQL Server has to convert a data type during query execution — typically because a parameter or variable does not match the column's data type.

The symptom is an index scan where you expect an index seek. The execution plan will show a CONVERT_IMPLICIT function on the seek column, which prevents the index from being used efficiently.

Common causes:

• VARCHAR column compared to NVARCHAR parameter (or vice versa)
• CHAR(10) column compared to VARCHAR(10) parameter
• INT column compared to a BIGINT or SMALLINT variable
• Date columns compared to string literals

-- This causes an implicit conversion (and a scan) if CustomerCode is VARCHAR
WHERE CustomerCode = N'ACME001'  -- N prefix makes it NVARCHAR

-- This allows an index seek
WHERE CustomerCode = 'ACME001'   -- No N prefix matches VARCHAR

Fixing implicit conversions often requires application-level changes (correcting parameter types in queries or stored procedure signatures), but the performance benefit — converting a table scan to an index seek — is immediate and significant.

Use the execution plan or the sys.dm_exec_query_stats combined with sys.dm_exec_sql_text to find queries with conversion operations on indexed columns.

---

Change 8: Ensure Your Clustered Index Choice Reflects Write and Read Patterns

The clustered index is the table. Its choice determines the physical order of data on disk, which affects insert patterns, range scan efficiency, and page contention.

The most common mistake is using a random or semi-random value as the clustered index key — typically a UNIQUEIDENTIFIER (GUID) with NEWID(), or a non-sequential natural key. This causes page splits: every insert lands on a random page, causing existing pages to split, leading to fragmentation, bloat, and I/O pressure.

The gold standard: an identity integer (INT IDENTITY or BIGINT IDENTITY) as the clustered index key. Sequential by nature — new rows always go at the end, no page splits.

If you are stuck with GUIDs (because they are needed for distributed ID generation), use NEWSEQUENTIALID() instead of NEWID(). It generates GUIDs that are sequential within a server restart, eliminating the random insertion problem.

Changing a clustered index on a large production table is a major operation — it requires rebuilding every non-clustered index and carries significant risk. Plan carefully, test thoroughly, and execute during a maintenance window with a rollback plan. But for tables with chronic fragmentation and high insert rates, it is often the highest-impact index change available.

---

Putting It Together: A Sequenced Approach

These eight changes are not equal in effort or risk. A sensible sequence:

1. Start with data: gather missing index DMVs, index usage stats, and top queries by resource. Understand before you act.
2. Quick wins first: implement top missing indexes (Change 1), drop clearly unused indexes (Change 2). Low risk, high impact.
3. Query-level fixes: address key lookups (Change 3) and implicit conversions (Change 7) for your highest-cost queries.
4. Infrastructure fixes: implement automated maintenance if absent (Change 4), then work through filtered index opportunities (Change 6) and workload alignment (Change 5).
5. Long-term: plan clustered index changes (Change 8) as part of scheduled maintenance, not reactive firefighting.

Index tuning is not a one-time event. As workloads evolve, query patterns change, and data volumes grow, index strategies need to evolve with them. Building a regular review into your maintenance calendar — quarterly for most environments, monthly for high-change workloads — is what separates a proactively managed estate from one that accumulates debt until it becomes a crisis.

---

Need help identifying where your indexing is costing you the most?

A SQLOPTIMISE Health Check surfaces your highest-impact indexing opportunities with a prioritised remediation plan. Let's talk →