Index Management
Overview
Indexes improve the speed of data retrieval operations on a SQL Server table at the cost of additional storage and maintenance overhead. Proper index management is essential for optimal query performance and efficient use of resources.
Creating Indexes
Indexes can be created using CREATE INDEX or CREATE UNIQUE INDEX. Choose appropriate columns based on query predicates and sort requirements.
CREATE NONCLUSTERED INDEX IX_Employee_LastName
ON dbo.Employee (LastName);
Rebuilding and Reorganizing Indexes
Fragmentation degrades performance. Use ALTER INDEX … REBUILD for heavy fragmentation and ALTER INDEX … REORGANIZE for light fragmentation.
-- Rebuild all indexes on a table
ALTER INDEX ALL ON dbo.Order
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
Fragmentation Monitoring
| Fragmentation Level | Action |
|---|---|
| 0‑10 % | Do nothing |
| 10‑30 % | Reorganize |
| >30 % | Rebuild |
Dropping Indexes
Remove unused indexes to reduce write overhead. Verify usage with sys.dm_db_index_usage_stats before dropping.
DROP INDEX IX_Employee_LastName ON dbo.Employee;
Best Practices
- Index columns used in
WHERE,JOIN, andORDER BYclauses. - Avoid over-indexing; limit to essential indexes.
- Prefer covering indexes for frequently queried columns.
- Monitor index usage and fragmentation regularly.
- Align index fill factor with data modification patterns.