Common Indexing Mistakes and How to Avoid Them: A SQL Performance Guide

Introduction

Indexes are the silent workhorses of database performance when done right. But poor indexing strategies can cripple your database just as effectively as having no indexes at all. In this post, I’ll expose the most common indexing pitfalls I’ve encountered in 15 years of database tuning and show you exactly how to avoid them.

The 7 Deadly Indexing Sins

1. The "Index Everything" Approach

Mistake: Creating indexes on every column, hoping something helps.

Symptoms:

• Slow writes (INSERT/UPDATE/DELETE)

• Bloated database size

• Query optimizer choosing bad execution plans

Solution:

-- Find unused indexes (SQL Server)
SELECT 
    o.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0
ORDER BY o.name, i.name;

2. The "Forgotten Index" Trap

Mistake: Never reviewing or maintaining existing indexes.

Symptoms:

• Fragmentation over 30%

• Outdated statistics

• Indexes no longer matching query patterns

Solution:

-- Check index fragmentation (SQL Server)
SELECT 
    OBJECT_NAME(ind.OBJECT_ID) AS table_name,
    ind.name AS index_name,
    indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
    AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 15
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

3. The "Wide Key" Problem

Mistake: Using unnecessarily wide columns in index keys.

Symptoms:

• Large index size

• Poor read performance

• Increased memory pressure

Solution:

• Prefer narrow data types (e.g., INT instead of VARCHAR(255))

• Use filtered indexes for large or sparse data

• Use INCLUDE for columns only needed in output

4. The "Missing Composite Index" Oversight

Mistake: Creating only single-column indexes even when queries filter on multiple columns.

Symptoms:

• Key lookups in execution plans

• Poor performance on multi-column WHERE clauses

Solution:

-- Find "missing" composite indexes (SQL Server)
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
INNER JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

5. The "Over-Indexed Table" Problem

Mistake: Having too many indexes on frequently modified tables.

Symptoms:

• Slower INSERT/UPDATE/DELETE operations

• Transaction log bloat

• Blocking during modifications

Solution:

• Limit indexes on OLTP tables to 5–7 max

• Combine similar indexes when possible

• Use columnstore indexes for analytics on transactional tables

6. The "Ignoring INCLUDEd Columns" Mistake

Mistake: Not using INCLUDE for columns only needed in the SELECT list.

Symptoms:

• Unnecessary key lookups

• Larger than necessary index keys

Solution:

-- Good example of INCLUDE usage
CREATE INDEX IX_Customer_LastName
ON Customers(LastName)
INCLUDE (FirstName, Email);

7. The "One-Size-Fits-All" Approach

Mistake: Applying the same indexing strategy across all tables.

Symptoms:

• Inconsistent query performance

• Some tables fly, others crawl

Solution:

• For OLTP: Use narrow indexes optimized for fast writes and key lookups

• For reporting: Use wider indexes, possibly columnstore

• For archived data: Fewer indexes just the essentials like the primary key

Indexing Best Practices Checklist

1. Measure First – Use execution plans and DMVs before creating indexes

2. Start Small – Add one index at a time and test performance

3. Think Composite – Index on multiple frequently filtered columns

4. Use INCLUDE – For output-only columns to avoid bloated keys

5. Maintain Regularly – Rebuild or reorganize based on fragmentation

6. Review Quarterly – Remove unused or rarely used indexes

7. Test Thoroughly – Simulate production workloads before deploying

Advanced Indexing Techniques

Filtered Indexes

For selective queries on sparse columns:

CREATE INDEX IX_Orders_Active
ON Orders(OrderDate)
WHERE Status = 'Active';

Partitioned Indexes

For massive tables:

CREATE INDEX IX_Sales_Date
ON Sales(OrderDate)
ON PartitionScheme_ByMonth(OrderDate);

Columnstore Indexes

Perfect for analytical queries:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales
ON Sales;

Conclusion

Indexing isn't a "set it and forget it" feature. It's an ongoing strategy that must evolve with your data and workload. By avoiding these common mistakes and applying proven techniques, you’ll unlock serious performance gains in your SQL databases often without touching a single line of application code.

Your Turn

Run the unused index query from above in your dev environment and let me know:
How many zombie indexes did you find?
What’s the most surprising indexing mistake you’ve encountered?

Let’s talk database war stories in the comments.