DBA Scripts: Get Index Design Issues

---

Most index problems aren’t about a single bad index, they’re about a table’s index design as a whole drifting out of shape over time. A table that accumulates 25 indexes because nobody ever removed the old ones. A composite key that grew column by column until it’s wider than SQL Server is comfortable with. A table where the Missing Index DMV has given up and is suggesting a dozen different indexes because none of the existing ones come close to covering the workload.

This script checks every table in every user database against three specific design problems at once: too many indexes, key columns that are too wide, and tables where the optimizer is flooding you with missing-index suggestions because existing coverage is that far off.

---

Why Index Design Issues Matters

Individually, any one of these problems is manageable. Together, or left unaddressed for a long time, they compound:

• Too many indexes — every one of them is maintained on every write. A table with 30 indexes pays 30x the index-maintenance cost per row change.
• Wide key columns — SQL Server’s row-store index key limit is 1,700 bytes (900 in older compatibility levels). Getting close to that limit means larger index pages, fewer rows per page, and more I/O per seek. Exceeding it means the index can’t be created at all.
• Missing-index flooding — when the DMV is suggesting five or more indexes for the same table, it’s not asking for five new indexes, it’s telling you the existing index design doesn’t match how the table is actually queried.

---

When to Run This Script

• Routine SQL Server health checks
• Reviewing a table before a major schema or query change
• Investigating a table with high write latency or a large index footprint
• Auditing a server or database you’ve just inherited

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-IndexDesignIssues
Category    : performance
Purpose     : Tables with index design problems: excessive index count (write amplification),
              wide key columns (>900 bytes — approaching the 1700-byte row-store limit),
              and tables where Missing Index DMV has > 3 recommendations (optimizer giving up
              on existing index coverage). Complements Get-DuplicateIndexes.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-index-design-issues/)
Requires    : VIEW ANY DATABASE, VIEW DATABASE STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-index-design-issues/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

CREATE TABLE #issues (
    database_name SYSNAME,
    schema_name SYSNAME,
    table_name SYSNAME,
    issue_type NVARCHAR(60),
    detail NVARCHAR(500),
    metric_value INT,
    status NVARCHAR(400)
);

DECLARE @db SYSNAME;
DECLARE @sql NVARCHAR(MAX);

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'
    -- Issue 1: Too many indexes per table (write amplification)
    INSERT INTO #issues
    SELECT
        N' + QUOTENAME(@db, N'''') + N',
        s.name,
        t.name,
        ''TOO_MANY_INDEXES'',
        CAST(COUNT(i.index_id) AS VARCHAR) + '' indexes on this table (> 20 harms INSERT/UPDATE/DELETE throughput)'',
        COUNT(i.index_id),
        CASE WHEN COUNT(i.index_id) > 30 THEN ''CRITICAL''
             WHEN COUNT(i.index_id) > 20 THEN ''WARN''
             ELSE ''INFO'' END
    FROM ' + QUOTENAME(@db) + N'.sys.indexes i
    JOIN ' + QUOTENAME(@db) + N'.sys.tables t ON t.object_id = i.object_id
    JOIN ' + QUOTENAME(@db) + N'.sys.schemas s ON s.schema_id = t.schema_id
    WHERE i.type IN (1, 2) AND t.is_ms_shipped = 0 AND i.is_disabled = 0
    GROUP BY s.name, t.name
    HAVING COUNT(i.index_id) > 10;

    -- Issue 2: Wide key columns (row-store limit is 1700 bytes in SQL 2016+, 900 in older)
    INSERT INTO #issues
    SELECT
        N' + QUOTENAME(@db, N'''') + N',
        s.name,
        t.name,
        ''WIDE_KEY_COLUMNS'',
        i.name + '' — key width ~'' +
            CAST(SUM(CASE WHEN c.max_length = -1 THEN 900 ELSE c.max_length END) AS VARCHAR) +
            '' bytes (avoid keys > 900 bytes; > 1700 bytes will fail)'',
        SUM(CASE WHEN c.max_length = -1 THEN 900 ELSE c.max_length END),
        CASE
            WHEN SUM(CASE WHEN c.max_length = -1 THEN 900 ELSE c.max_length END) > 1700
            THEN ''CRITICAL''
            WHEN SUM(CASE WHEN c.max_length = -1 THEN 900 ELSE c.max_length END) > 900
            THEN ''WARN''
            ELSE ''INFO''
        END
    FROM ' + QUOTENAME(@db) + N'.sys.indexes i
    JOIN ' + QUOTENAME(@db) + N'.sys.tables t ON t.object_id = i.object_id
    JOIN ' + QUOTENAME(@db) + N'.sys.schemas s ON s.schema_id = t.schema_id
    JOIN ' + QUOTENAME(@db) + N'.sys.index_columns ic ON ic.object_id = i.object_id
                                AND ic.index_id = i.index_id
                                AND ic.is_included_column = 0
    JOIN ' + QUOTENAME(@db) + N'.sys.columns c ON c.object_id = ic.object_id
                                AND c.column_id = ic.column_id
    WHERE i.type IN (1, 2) AND t.is_ms_shipped = 0
    GROUP BY s.name, t.name, i.name
    HAVING SUM(CASE WHEN c.max_length = -1 THEN 900 ELSE c.max_length END) > 450;

    -- Issue 3: Tables with many Missing Index recommendations (index coverage severely lacking)
    INSERT INTO #issues
    SELECT
        N' + QUOTENAME(@db, N'''') + N',
        mi_details.schema_name,
        mi_details.table_name,
        ''MISSING_INDEX_FLOOD'',
        CAST(mi_details.missing_count AS VARCHAR) + '' missing index recommendations — index coverage likely poor; review before applying all'',
        mi_details.missing_count,
        CASE WHEN mi_details.missing_count >= 10 THEN ''WARN''
             ELSE ''INFO'' END
    FROM (
        SELECT
            OBJECT_SCHEMA_NAME(mid.object_id, DB_ID(N' + QUOTENAME(@db, N'''') + N')) AS schema_name,
            OBJECT_NAME(mid.object_id, DB_ID(N' + QUOTENAME(@db, N'''') + N')) AS table_name,
            COUNT(*) AS missing_count
        FROM sys.dm_db_missing_index_details mid
        WHERE mid.database_id = DB_ID(N' + QUOTENAME(@db, N'''') + N')
        GROUP BY mid.object_id
        HAVING COUNT(*) >= 5
    ) mi_details;';

    BEGIN TRY EXEC sp_executesql @sql; END TRY
    BEGIN CATCH END CATCH;

    FETCH NEXT FROM db_cursor INTO @db;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT
    database_name,
    schema_name,
    table_name,
    issue_type,
    detail,
    metric_value,
    status
FROM #issues
ORDER BY
    CASE status WHEN 'CRITICAL' THEN 1 WHEN 'WARN' THEN 2 ELSE 3 END,
    database_name,
    schema_name,
    table_name,
    issue_type;

DROP TABLE #issues;

The script checks every table in every online user database for three separate issues (too many indexes, wide key columns, missing-index flooding) and returns one row per issue found, ranked by severity.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check every table for index design problems:
.\run.ps1 Get-IndexDesignIssues

# To run against a remote sql server:
.\run.ps1 Get-IndexDesignIssues -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/performance/indexes/Get-IndexDesignIssues.sql
• powershell/wrappers/performance/indexes/Get-IndexDesignIssues.ps1

---

Example Output

database_name	schema_name	table_name	issue_type	detail	metric_value	status
WatchtowerMetrics	dbo	DatabaseGrowthEvents	WIDE_KEY_COLUMNS	UX_DatabaseGrowthEvents_GrowthEvent — key wid…	1047	WARN

This run only surfaced one issue locally: a wide composite key on a growth-tracking table, still under the hard 1,700-byte limit but over the 900-byte caution threshold. In a busier environment, expect to see all three issue types.

---

Understanding the Results

issue_type = TOO_MANY_INDEXES

Flagged once a table passes 10 indexes, status moves to WARN at 20 and CRITICAL at 30. metric_value is the index count. Review whether every index is still earning its write cost. Cross-check against Duplicate Indexes and Unused Indexes.

issue_type = WIDE_KEY_COLUMNS

Flagged once a key’s combined column width passes 450 bytes, WARN at 900, CRITICAL at 1,700 (the hard limit; SQL Server will refuse to create the index past this). metric_value is the estimated key width in bytes. NVARCHAR(MAX) and other unbounded types are estimated conservatively at 900 bytes each.

issue_type = MISSING_INDEX_FLOOD

Flagged once a table has 5 or more distinct missing-index recommendations from sys.dm_db_missing_index_details, WARN at 10+. metric_value is the recommendation count. Don’t apply all of them mechanically. Review for overlap first (an index covering the union of several suggestions is usually better than several narrow ones).

---

How to Fix Index Design Issues

The fix depends on which issue fired:

• TOO_MANY_INDEXES — run Duplicate Indexes and Unused Indexes against the same table first; the fix is usually consolidation, not a wholesale redesign.
• WIDE_KEY_COLUMNS — check whether every key column is actually needed for seeks, or whether some belong in the INCLUDE list instead (included columns don’t count toward the key-width limit).
• MISSING_INDEX_FLOOD — group the suggested indexes by overlapping columns and design one or two wider indexes that cover most of them, rather than creating each suggestion verbatim.

-- Move a column from the key to INCLUDE to reduce key width
-- without losing the ability to cover the query
DROP INDEX [IX_Example] ON [dbo].[YourTable];
CREATE NONCLUSTERED INDEX [IX_Example]
    ON [dbo].[YourTable] (KeyColumn1, KeyColumn2)
    INCLUDE (WideColumn);

---

Best Practices

• Review a table’s full index list before adding another one, not just the query that prompted the request.
• Prefer INCLUDE columns over key columns for anything not used in a WHERE, JOIN, or ORDER BY clause.
• Treat a growing missing-index recommendation count as a design signal, not a to-do list to apply verbatim.

---

Related Scripts

You may also find these scripts useful:

• Index Maintenance (hub)

• Duplicate Indexes

• Heaps
• Index Fragmentation
• Index Fragmentation Across Databases
• Index Usage Stats
• Missing Indexes
• Unused Indexes
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Index design problems rarely show up as a single dramatic incident. They accumulate quietly across months of ad-hoc changes until a table is carrying more indexes, wider keys, or worse coverage than anyone intended.

Run this script as part of your regular health checks, especially on tables that have been through several rounds of tuning by different people, and treat each finding as a design conversation rather than a one-line fix.