SQL Server Index Tuning & Optimization: Fix Missing, Unused, and Bloated Indexes

When a SQL Server database slows down, most teams face the same two options: spend money on additional hardware or invest time in tuning. Hardware is fast to procure and easy to justify, but it rarely solves the underlying problem and delivers diminishing returns. SQL Server index tuning, by contrast, addresses the root cause directly. A single missing index can turn a millisecond query into a multi-second table scan. A single redundant index imposes write overhead on every INSERT, UPDATE, and DELETE against that table, indefinitely.

This guide explains how SQL Server indexes work, how to identify the specific index problems degrading your workload, and how AI SQL Tuner Studio automates the entire analysis — delivering prioritized, ready-to-implement recommendations in minutes rather than hours.

Why SQL Server Index Optimization Matters

An index in SQL Server is a separate on-disk structure that allows the query engine to locate rows without scanning an entire table. Without an appropriate index, SQL Server performs a full table scan — reading every row regardless of how many rows match the query. On a table with millions of rows, a scan that should return one row can consume seconds of CPU and thousands of I/O operations. With the right index, the same query executes as a seek: a lookup that finds the target rows in milliseconds.

Index problems fall into three categories, each with a different impact profile:

• Missing indexes cause table scans instead of seeks, driving up CPU and I/O under query load.
• Unused, redundant or bloated indexes add overhead to every write operation without providing any read benefit.
• Fragmented indexes reduce page density, forcing SQL Server to read more pages to return the same data.

All three problems compound over time as data volumes grow and query patterns evolve. Index optimization is not a one-time task; it is an ongoing operational requirement for any production SQL Server workload.

Types of SQL Server Indexes

Understanding which index type applies to a given problem is essential before making changes. Microsoft’s SQL Server Index Design Guide covers the full specification; the four types most relevant to optimization work are:

Clustered Index

A clustered index defines the physical storage order of rows in a table. SQL Server stores the actual data rows as the leaf level of the clustered index B-tree. Each table can have only one clustered index. Most tables should have a clustered index on their primary key; heap tables (no clustered index) are generally a performance liability because SQL Server cannot efficiently navigate to specific rows without one.

Nonclustered Index

A nonclustered index is a separate structure containing the indexed column values and a pointer (the row locator) back to the data row in the clustered index or heap. SQL Server supports up to 999 nonclustered indexes per table, though practical limits are much lower. Each nonclustered index adds storage and write overhead, so every index must justify its cost in read performance gained.

Columnstore Index

Columnstore indexes store data organized by column rather than by row, enabling batch-mode execution and high compression ratios. They deliver dramatic performance gains for analytics and reporting workloads — scan-heavy queries that aggregate large amounts of data across many rows. SQL Server supports both clustered and nonclustered columnstore indexes. For OLTP workloads with frequent single-row lookups, rowstore nonclustered indexes remain the better choice.

Covering Index

A covering index is a nonclustered index that includes all columns a specific query needs: key columns in the WHERE clause plus any additional columns in the SELECT list as INCLUDE columns. When a covering index exists, SQL Server satisfies the entire query from the index without a key lookup into the clustered index, eliminating an additional I/O operation per row.

How to Identify Index Problems in SQL Server

SQL Server exposes index telemetry through a set of dynamic management views (DMVs) and SSMS reports. The data is available at no additional cost and requires only read access to system tables. The challenge is knowing which signals to look at, how to interpret them together, and how to prioritize what to fix first.

Finding Missing Indexes

SQL Server’s query optimizer records index recommendations in memory each time it compiles a query plan and determines that a useful index is absent. These recommendations accumulate in the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats DMVs. You can also view them in SSMS via Reports > Standard Reports > Performance Dashboard, then click the Missing Indexes link.

Two caveats apply. First, the recommendations are not deduplicated; SQL Server may log multiple overlapping suggestions for the same table. Before creating an index, check whether an existing index could be extended with a new INCLUDE column to satisfy the same need. Second, the optimizer’s recommendations are generated per-query and do not account for write overhead, so a naively implemented missing index can harm overall workload performance even while improving one specific query.

You can use the SQL statement below to find the 20 missing indexes for the current database with the highest overall impact:

SELECT TOP 20
    s.name + '.' + o.name AS TableName,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    stats.avg_total_user_cost,
    stats.avg_user_impact,
    stats.user_seeks + stats.user_scans AS TotalSeeksScans
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats stats ON g.index_group_handle = stats.group_handle
INNER JOIN sys.objects o ON d.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE d.database_id = DB_ID()
ORDER BY (stats.avg_total_user_cost * stats.avg_user_impact * (stats.user_seeks + stats.user_scans)) DESC;

To tune one specific slow query end to end use SQL query tuner. It will provide recommendations based on the query’s execution plan, which can include indexes as well as query rewrites. Modifying the query can often provide more improvements than index changes alone.

Finding Bloated, Unused, and Redundant Indexes

The sys.dm_db_index_usage_stats DMV records how many times each index has been accessed for seeks, scans, lookups, and updates since the last SQL Server restart. SSMS surfaces this data in the Index Usage Statistics report (Reports > Standard Reports > Index Usage Statistics).

An index with high user_updates and near-zero user_seeks and user_scans is consuming write overhead without providing any read benefit. These are strong candidates for removal — but verify first that the index is not relied upon for a constraint or a query that runs infrequently (such as a monthly report) and therefore shows low usage stats at the time of review.

Duplicate and redundant indexes — where one index’s key columns are a leading prefix of another — should also be consolidated. Maintaining two indexes that serve overlapping purposes doubles write overhead for no additional query benefit.

Include columns in an index can also help performance by satisfying the query directly from the index. However, unneeded columns add write overhead and use disk space, since you are effectively keeping an extra copy of the table’s data with the subset of columns in the include clause.

The statement below will show the index usage for the current database, along with each index’s size and type of compression:

SELECT 
    sch.name + '.' + obj.name AS TableName,
    i.name AS IndexName,
    i.type_desc,
    STUFF((
        SELECT ',' + c.name
        FROM sys.index_columns ic2
        INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
        WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id AND ic2.is_included_column = 0
        ORDER BY ic2.key_ordinal
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS KeyColumns,
    STUFF((
        SELECT ',' + c.name
        FROM sys.index_columns ic3
        INNER JOIN sys.columns c ON ic3.object_id = c.object_id AND ic3.column_id = c.column_id
        WHERE ic3.object_id = i.object_id AND ic3.index_id = i.index_id AND ic3.is_included_column = 1
        ORDER BY ic3.column_id
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS IncludedColumns,
    ISNULL(s.user_seeks, 0) AS user_seeks,
    ISNULL(s.user_scans, 0) AS user_scans,
    ISNULL(s.user_lookups, 0) AS user_lookups,
    ISNULL(s.user_updates, 0) AS user_updates,
    ROUND((ps.page_count * 8) / 1024.0, 1) AS SizeMB,
    p.data_compression_desc AS Compression
FROM sys.indexes i
INNER JOIN sys.objects obj ON i.object_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID()
LEFT JOIN (
    SELECT object_id, index_id, SUM(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) AS page_count
    FROM sys.dm_db_partition_stats GROUP BY object_id, index_id
) ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
LEFT JOIN (
    SELECT object_id, index_id, MAX(data_compression_desc) AS data_compression_desc
    FROM sys.partitions GROUP BY object_id, index_id
) p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE obj.type = 'U'
  AND EXISTS (SELECT 1 FROM sys.objects o WHERE o.object_id = obj.object_id)
ORDER BY ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0) DESC;

Eliminating redundant, unused, or bloated indexes is critical to maintaining overall performance for any SQL Server database that has significant write activity.

Detecting Index Fragmentation

Index fragmentation occurs when the logical order of pages in an index no longer matches their physical order on disk, or when pages are less than fully populated. Fragmented indexes require SQL Server to read more pages to return the same data, increasing I/O.

Query sys.dm_db_index_physical_stats and check avg_fragmentation_in_percent for each index:

• Below 10%: no action needed.
• 10–30%: use ALTER INDEX ... REORGANIZE — an online, low-impact operation.
• Above 30%: use ALTER INDEX ... REBUILD — more thorough, but acquires a schema lock. Use ONLINE = ON in Enterprise edition to minimize blocking.

You can use the code below to find the average page density and fragmentation for of each index for the 20 largest tables in the database that have more than 10,000 rows:

CREATE TABLE #DensityCandidates (object_id int NOT NULL);
CREATE TABLE #PageDensityResults (
    TableName      nvarchar(512),
    IndexName      sysname,
    IndexType      nvarchar(60),
    AvgPageDensityPct    float,
    AvgFragmentationPct  float,
    PageCount      bigint,
    RecordCount    bigint
);

-- Get object ids for 20 largest user tables 
INSERT INTO #DensityCandidates (object_id)
SELECT TOP 20 ps.object_id
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id
WHERE ps.index_id IN (0, 1)
  AND o.type = 'U'
  AND ps.object_id NOT IN (SELECT object_id FROM #DensityCandidates)
GROUP BY ps.object_id
HAVING SUM(ps.row_count) > 10000
ORDER BY SUM(ps.row_count) DESC;

DECLARE @ObjId int;
DECLARE density_cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT DISTINCT object_id FROM #DensityCandidates;

OPEN density_cur;
FETCH NEXT FROM density_cur INTO @ObjId;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = @ObjId)
    BEGIN
        INSERT INTO #PageDensityResults
        SELECT
            SCHEMA_NAME(o.schema_id) + '.' + o.name,
            i.name,
            i.type_desc,
            ips.avg_page_space_used_in_percent,
            ips.avg_fragmentation_in_percent,
            ips.page_count,
            ips.record_count
        FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjId, NULL, NULL, 'SAMPLED') ips
        INNER JOIN sys.objects o  ON ips.object_id = o.object_id
        INNER JOIN sys.indexes i  ON ips.object_id = i.object_id AND ips.index_id = i.index_id
        WHERE o.type = 'U'
          AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
          AND ips.page_count > 128
          AND ips.avg_page_space_used_in_percent IS NOT NULL
          AND i.name IS NOT NULL;
    END

    FETCH NEXT FROM density_cur INTO @ObjId;
END

CLOSE density_cur;
DEALLOCATE density_cur;

SELECT 
    TableName, IndexName, IndexType,
    AvgPageDensityPct, AvgFragmentationPct, PageCount, RecordCount
FROM #PageDensityResults
ORDER BY AvgPageDensityPct ASC;

DROP TABLE #PageDensityResults;
DROP TABLE #DensityCandidates;

Note that fragmentation thresholds matter more on mechanical disk environments. On NVMe or solid-state storage, the I/O cost of random page access is lower, so fragmentation has less impact than on spinning disks.

Index Compression and Fill Factor

Beyond index structure, two configuration settings have significant impact on storage and query performance: data compression and fill factor.

Row and Page Compression

SQL Server offers two levels of data compression for indexes and tables:

• Row compression stores fixed-length data types in variable-length format, eliminating unnecessary padding. For example, a CHAR(50) column containing a 6-character value stores only 6 bytes rather than 50. Row compression reduces storage with minimal CPU overhead.
• Page compression applies prefix and dictionary compression within each 8 KB page, identifying repeating byte patterns across rows and storing them once. Page compression achieves higher compression ratios than row compression but adds more CPU overhead during reads and writes.

Compression is most beneficial on large, read-heavy tables where I/O is the bottleneck. For write-intensive tables, measure CPU overhead before committing to page compression in production.

Fill Factor

Fill factor controls how much free space SQL Server reserves on each index page when the index is created or rebuilt. A fill factor of 80 leaves 20% of each page empty, providing room for future inserts and reducing page splits. A fill factor of 100 packs pages completely — efficient for read-only or rarely-updated data, but prone to fragmentation in write-heavy workloads.

SQL Server’s default fill factor is 0, which SQL Server treats as 100 for most workloads. For tables with frequent inserts into the middle of the index range (non-sequential keys), a fill factor of 70–85 is a common starting point. The right value depends on write frequency and how often you run index maintenance.

Hardware vs. Index Optimization: The Real Cost

When database performance degrades, hardware upgrades are tempting because they require no code changes and can be provisioned quickly. The cost, however, is substantial and recurring:

• Adding 2 processor cores to SQL Server Standard costs approximately $1,418 per year for the software license alone — before hardware, Windows Server licensing, or cloud infrastructure costs.
• The same 2-core add-on for SQL Server Enterprise runs approximately $5,434 per year.

Index tuning addresses the root cause: queries doing unnecessary work because they cannot find data efficiently. A well-targeted index can eliminate the need for a hardware upgrade entirely, and the fix is permanent rather than recurring. For teams managing cloud-hosted instances where compute costs scale directly with resource consumption, the savings compound with every database you optimize.

Automate Index Tuning with AI SQL Tuner Studio

Working through missing index DMVs, usage statistics, plan cache data and fragmentation reports by hand takes hours, because the signals interact. The Index Tuning goal in AI SQL Tuner Studio reads them together through a least-privilege login, without making changes, and returns a prioritized action plan with ready-to-run T-SQL and the reason for each change, for you to review before you deploy anything. See a sample Index Tuning report.

Index Tuning in 3 Steps

1. Connect your database. Enter the connection details for on-premises SQL Server, Azure SQL Database, Azure SQL Managed Instance, or Microsoft Fabric SQL Database.
2. Run the Index Tuning goal. AI SQL Tuner Studio collects index usage statistics, missing index DMVs, and plan cache data, then sends them to the AI model for analysis. No trace is required.
3. Review your report. You get an HTML report with prioritized recommendations in four categories: indexes to create, overlapping or unused indexes to consolidate, query patterns that prevent index use (with suggested rewrites), and outdated statistics. Each recommendation comes with ready-to-run T-SQL.

Models and platforms. GPT-5.6 Luna on every edition and GPT-6 Sol on Team and Enterprise, hosted on Microsoft Foundry; Pro, Team and Enterprise can also bring their own endpoint. Every edition, including Free, supports SQL Server 2016 and later, Azure SQL Database, Azure SQL Managed Instance and Fabric SQL Database, with Amazon RDS for SQL Server in preview. The Free edition includes 10 analyses a month with no credit card; paid editions start at $19.99/month with a 14-day trial.

Download the free edition, compare editions, or see the Index Tuning goal for what it collects, the permissions it needs and the report format.

How AI SQL Tuner Studio Compares to Manual Tools

The traditional manual toolkit — SSMS reports, sp_BlitzIndex from Brent Ozar Unlimited, and the DMV queries shared earlier — provides the raw data but leaves interpretation entirely to the DBA. AI SQL Tuner Studio automates the interpretation step: it reads the same signals and produces an ordered action plan with implementation scripts. For a detailed comparison, see AI SQL Tuner Studio vs. Database Engine Tuning Advisor.

The open-source aisqltuner-index-tuner stored procedure ports AI SQL Tuner Studio’s Index Tuning goal to T-SQL, so it runs inside the database and can be scheduled from SQL Server Agent. It calls the model through sp_invoke_external_rest_endpoint, so on-premises it requires SQL Server 2025; for SQL Server 2016 through 2022, use AI SQL Tuner Studio. It also supports Azure SQL Database, Azure SQL Managed Instance configured with the SQL Server 2025 or Always-up-to-date update policy, and Microsoft Fabric SQL Database.

Capability	AI SQL Tuner Studio	aisqltuner-index-tuner (open source)	sp_BlitzIndex	Database Engine Tuning Advisor	Manual DMV analysis
Prioritized by workload impact	Yes, AI-ranked	Yes, AI-ranked	No	No	No
Plan cache analysis	Yes, automatic	Yes, top plan cache statements plus Query Store	No	Yes, when the plan cache is used as the workload	Manual, complex queries
Index consolidation	Yes, finds overlapping indexes	Yes, finds duplicate and overlapping indexes	Flags duplicates	Limited	Manual effort
Inefficient query detection	Yes, with rewrite suggestions	Flags non-SARGable predicates for rewrite	No	No	No
Statistics	Outdated statistics flagged	Reviews statistics settings	No	Recommends statistics to create	Separate process
Applies changes	No, you review and run the scripts	Yes, creates verified new indexes; drops only when allowed	No	Yes, can apply its recommendations	You run them
Setup time	Minutes, no trace required	Minutes: one install script plus your AI endpoint	Minutes, runs T-SQL scripts	Minutes with the plan cache; a trace or workload file for broader coverage	Hours
Azure SQL support	Azure SQL Database, Managed Instance, Fabric SQL Database	Azure SQL Database, Managed Instance (SQL Server 2025 or Always-up-to-date policy), Fabric SQL Database	Community-maintained	Not supported (SQL Server only)	Partial (limited DMVs)
AI model	Hosted models included; bring your own endpoint on paid editions	Your own Azure OpenAI or Microsoft Foundry deployment (required)	None	None	None
Price	Free edition; paid from $19.99/month	Free, MIT license (plus your Azure OpenAI usage)	Free (open source)	Included with SQL Server	Your time

For a broader treatment of SQL Server performance tuning beyond indexes, see the SQL Server Performance Tuning Guide.

Frequently Asked Questions

Start Optimizing SQL Server Indexes Today

SQL Server index optimization is the highest-impact performance improvement available to most database teams. The data you need is already in SQL Server’s DMVs; the challenge is gathering it, interpreting the signals together, and prioritizing the changes with the biggest impact. AI SQL Tuner Studio handles that workflow automatically, using GPT-5.6 Luna on every edition, or GPT-6 Sol on Team and Enterprise, to analyze your specific workload and deliver an ordered action plan with deployment scripts included.

Download the free edition: every tuning goal and 10 analyses a month on SQL Server, Azure SQL Database, Azure SQL Managed Instance or Fabric SQL Database, with no credit card. Or explore paid plans from $19.99/month, with a 14-day trial and a 30-day refund guarantee on purchases from aisqltuner.com.

To learn how AI SQL Tuner Studio automates index tuning, see the Index Tuning goal. You can also go to AI SQL Tuner Studio Sample Reports to see complete example reports or a screenshot of one below.