Find SQL Server Hidden Performance Gains with Indexing

You know that feeling when your database is slow and you know indexes would help, but you have no idea where to start? Yeah, I’ve been there too many times.

SQL Server actually keeps track of every time it wishes an index existed. It literally logs “man, I wish there was an index here” every time a query struggles. The problem is, this data is scattered across multiple system views and completely overwhelming.

So we built a query that does the thinking for you.

What This Does

This query pulls from SQL Server’s missing index DMVs (Dynamic Management Views) and does something most scripts don’t: it prioritizes by table size and phases the work.

Here’s why that matters: Creating an index on a 200-million-row table can lock that table for hours. You don’t want to discover that at 2pm on a Tuesday. This query tells you upfront: “Hey, this one’s going to take 12-24 hours, maybe do it on the weekend.”

The Results You Get

When you run this, you get a prioritized list with:

• Priority ranking – Just go down the list
• Phase grouping – Small tables first (Phase 1), monsters last (Phase 4)
• Impact percentage – How much faster queries will be (we’re only showing 50%+ improvements)
• Estimated time – How long the index creation will actually take
• The exact SQL command – Copy, paste, done

How to Use It

• Run the query in SQL Server Management Studio
• Start with Priority 1 (always a small table, quick win)
• Copy the SQL_Command column
• Run it during a maintenance window (or off-hours for the big ones)
• Move to Priority 2

The phases break down like this:

• Phase 1: Under 5M rows – these are your quick wins, 2-30 minutes each
• Phase 2: 5-25M rows – plan for 30 minutes to 3 hours
• Phase 3: 25-100M rows – these need proper maintenance windows, 3-12 hours
• Phase 4: 100M+ rows – weekend work, potentially 24+ hours

What Makes This Different

Most missing index queries just dump everything on you sorted by “impact.” That’s useless when the #1 result is a 48-hour index build on your busiest table.

This query is smarter:

• It factors in table size first, then impact
• It gives you realistic time estimates
• It groups work into phases so you can knock out Phase 1 in an afternoon
• It only shows indexes that matter (50%+ improvement threshold)

Real Talk: The Caveats

SQL Server’s missing index suggestions aren’t perfect. Sometimes it suggests redundant indexes or overly-specific ones. You should still:

• Check if similar indexes already exist
• Verify the index makes sense for your workload
• Test on non-prod first if possible
• Watch for duplicate suggestions on the same table

But honestly? In most cases, if SQL Server is saying “this index would help 75% of queries,” it’s probably right.

The Quick Version

Paste the query, run it, get your prioritized list. Start with Priority 1. Copy the SQL_Command. Run it during maintenance. Watch your slow queries suddenly get fast. Repeat.

That’s it. No analysis paralysis, no reading documentation for hours. Just a clear path from “my database is slow” to “my database is fast.”

I keep this query in a snippet library and run it every quarter or whenever performance starts degrading. It’s found me countless easy wins—indexes that take 5 minutes to create and make queries 10x faster.

Your database is already tracking what it needs. You just need to ask it the right way.

The Full Query

Here’s the complete SQL script. Copy and paste it into SQL Server Management Studio:

-- This will show you exactly which indexes to create in which order
WITH TableStats AS (
    SELECT 
        t.name AS Table_Name,
        SUM(p.rows) AS Row_Count,
        SUM(a.total_pages) * 8 / 1024.0 AS Size_MB
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE i.index_id IN (0,1)
    GROUP BY t.name
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY 
        CASE 
            WHEN ts.Row_Count < 5000000 THEN 1
            WHEN ts.Row_Count < 25000000 THEN 2
            WHEN ts.Row_Count < 100000000 THEN 3
            ELSE 4
        END,
        mi.avg_improvement_percent DESC
    ) AS Priority,
    CASE 
        WHEN ts.Row_Count < 5000000 THEN 'Phase 1'
        WHEN ts.Row_Count < 25000000 THEN 'Phase 2'
        WHEN ts.Row_Count < 100000000 THEN 'Phase 3'
        ELSE 'Phase 4'
    END AS Phase,
    mi.table_name AS Table_Name,
    ts.Row_Count,
    CAST(ts.Row_Count / 1000000.0 AS DECIMAL(10,2)) AS Millions,
    CAST(ts.Size_MB AS DECIMAL(10,2)) AS Size_MB,
    mi.avg_improvement_percent AS Impact_Percent,
    mi.total_queries AS Affected_Queries,
    CASE 
        WHEN ts.Row_Count < 1000000 THEN '2-5 min'
        WHEN ts.Row_Count < 5000000 THEN '10-30 min'
        WHEN ts.Row_Count < 10000000 THEN '30-60 min'
        WHEN ts.Row_Count < 25000000 THEN '1-3 hrs'
        WHEN ts.Row_Count < 50000000 THEN '3-6 hrs'
        WHEN ts.Row_Count < 100000000 THEN '6-12 hrs'
        WHEN ts.Row_Count < 200000000 THEN '12-24 hrs'
        ELSE '24-48+ hrs'
    END AS Est_Time,
    mi.create_index_statement AS SQL_Command
FROM (
    -- Your missing index data
    SELECT 
        OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
        migs.avg_user_impact AS avg_improvement_percent,
        migs.user_seeks + migs.user_scans AS total_queries,
        'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + 
            REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), '[', ''), ']', ''), ', ', '_') +
            CASE WHEN mid.inequality_columns IS NOT NULL 
                THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[', ''), ']', ''), ', ', '_')
                ELSE '' 
            END +
            ' ON ' + OBJECT_NAME(mid.object_id, mid.database_id) + 
            ' (' + ISNULL(mid.equality_columns, '') + 
            CASE WHEN mid.inequality_columns IS NOT NULL 
                THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ', ' ELSE '' END + mid.inequality_columns 
                ELSE '' 
            END + ')' +
            CASE WHEN mid.included_columns IS NOT NULL 
                THEN ' INCLUDE (' + mid.included_columns + ')' 
                ELSE '' 
            END + ';' AS create_index_statement
    FROM sys.dm_db_missing_index_details mid
    JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
    JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
) mi
LEFT JOIN TableStats ts ON mi.table_name = ts.Table_Name
WHERE mi.avg_improvement_percent > 50  -- Only high-impact indexes
ORDER BY Priority