Indexes are the most powerful performance optimization tool in databases.
But incorrect indexing can make performance worse instead of better.
Understanding how to design the right indexing strategy in Microsoft SQL Server can drastically improve query performance.
1️⃣ What is an Index?
An index is a data structure that improves the speed of data retrieval.
Without an index, SQL Server must scan the entire table.
Example
SELECT *
FROM Orders
WHERE OrderId = 1001;
Without index → Table Scan
With index → Index Seek
📌 Huge performance difference.
2️⃣ Types of Indexes
🔹 Clustered Index
Defines the physical order of data in a table.
Each table can have only one clustered index.
Example:
CREATE CLUSTERED INDEX IX_Orders_OrderId
ON Orders(OrderId);
Best used on:
- Primary keys
- Frequently sorted columns
🔹 Non-Clustered Index
Separate structure pointing to table rows.
CREATE NONCLUSTERED INDEX IX_Orders_UserId
ON Orders(UserId);
Best used for:
- Search columns
- Filter columns
3️⃣ Covering Index
A covering index contains all columns required by a query.
Example query:
SELECT OrderId, Amount
FROM Orders
WHERE UserId = 10;
Create covering index:
CREATE INDEX IX_Orders_UserId
ON Orders(UserId)
INCLUDE (OrderId, Amount);
📌 SQL Server doesn’t need to access the main table.
Huge speed improvement.
4️⃣ Composite Index
Index built on multiple columns.
CREATE INDEX IX_Orders_User_Date
ON Orders(UserId, OrderDate);
Important rule:
📌 Index order matters.
This index supports:
✔ UserId
✔ UserId + OrderDate
But NOT:
❌ OrderDate alone
5️⃣ Detecting Missing Indexes
SQL Server suggests missing indexes.
SELECT *
FROM sys.dm_db_missing_index_details;
Or check execution plan warnings.
⚠ But never blindly create all suggested indexes.
6️⃣ Detecting Unused Indexes
Too many indexes slow down:
- INSERT
- UPDATE
- DELETE
Check unused indexes:
SELECT *
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0;
Unused indexes waste storage and CPU.
7️⃣ Index Fragmentation
Over time, indexes become fragmented.
Check fragmentation:
SELECT *
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
NULL,
NULL,
NULL,
'LIMITED'
);
8️⃣ Fixing Fragmentation
Reorganize Index (light fragmentation)
ALTER INDEX IX_Orders_UserId
ON Orders
REORGANIZE;
Rebuild Index (heavy fragmentation)
ALTER INDEX IX_Orders_UserId
ON Orders
REBUILD;
📌 Schedule index maintenance regularly.
9️⃣ Common Index Mistakes
❌ Too Many Indexes
Every insert/update must update all indexes.
❌ Index on Low Selectivity Columns
Example:
Gender
Status
IsActive
Bad index candidates.
❌ Duplicate Indexes
Example:
(UserId)
(UserId, OrderDate)
First index may be redundant.
🔟 Real Production Example
❌ Problem
Dashboard query taking 12 seconds.
🔍 Root Cause
Table scan on large table.
Query:
SELECT *
FROM Orders
WHERE UserId = 5000;
✅ Fix
CREATE INDEX IX_Orders_UserId
ON Orders(UserId);
Query time reduced:
12 seconds → 20 milliseconds
1️⃣1️⃣ Best Index Design Strategy
✔ Index frequently filtered columns
✔ Create covering indexes for heavy queries
✔ Monitor index usage
✔ Remove unused indexes
✔ Maintain indexes regularly
1️⃣2️⃣ Indexing Checklist for Developers
Before creating index ask:
✔ Is this column used in WHERE clause?
✔ Is table large?
✔ Is query slow?
✔ Will this index affect write performance?
If yes → create index.
✍️ Conclusion
Indexes are the single biggest factor in SQL performance.
A good indexing strategy leads to:
✔ Faster queries
✔ Lower CPU usage
✔ Reduced IO
✔ Better scalability
Mastering indexing moves you from SQL developer → Performance Engineer.