SQL Performance Tuning: Indexing

Effective indexing is crucial for optimizing the performance of your SQL Server database. Indexes allow SQL Server to find rows quickly without scanning the entire table, significantly reducing query execution time.

Understanding Indexes

An index is a data structure that improves the speed of data retrieval operations on a database table. It works much like the index in a book, allowing the database engine to locate specific records without needing to examine every row in the table.

Types of Indexes

Clustered Indexes: Determines the physical order of data in the table. A table can have only one clustered index. It typically resides on the primary key.
Nonclustered Indexes: A separate structure that contains key values and pointers to the actual data rows. A table can have multiple nonclustered indexes.
Unique Indexes: Ensures that the index key contains no duplicate values.
Filtered Indexes: A nonclustered index optimized for query performance by including only a subset of rows in the table, defined by a WHERE clause.
Columnstore Indexes: Designed for data warehousing and analytical workloads, these indexes store data column by column rather than row by row, leading to high compression and query performance for analytical queries.
Full-Text Indexes: Used for performing full-text searches on character-based data.

Creating and Managing Indexes

You can create indexes using the CREATE INDEX statement. Consider the following when designing your indexes:

Key Considerations for Index Design:

Selectivity: Indexes are most effective on columns with high selectivity (many distinct values).
Query Patterns: Analyze your most frequent and performance-critical queries to determine which columns are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
Column Order: For composite indexes (indexes on multiple columns), the order of columns is critical. Place columns used in equality predicates first.
Over-Indexing: Too many indexes can negatively impact performance, especially during data modifications (INSERT, UPDATE, DELETE), as each index needs to be maintained.
Index Maintenance: Regularly rebuild or reorganize indexes to combat fragmentation and maintain optimal performance.

Example: Creating a Nonclustered Index

To create a nonclustered index on the LastName column of a Customers table:

CREATE NONCLUSTERED INDEX IX_Customers_LastName
ON dbo.Customers (LastName);

Example: Creating a Composite Index

For queries filtering on both OrderDate and CustomerID:

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID
ON dbo.Orders (OrderDate, CustomerID);

Index Maintenance

Indexes can become fragmented over time due to data modifications, which can degrade query performance. SQL Server provides commands to manage this:

REORGANIZE: Defragments the leaf level of the index and optionally reorders pages. This is an online operation.
REBUILD: Rebuilds the entire index, creating a new, defragmented structure. This can be an online operation depending on the edition.

Example: Reorganizing an Index

ALTER INDEX IX_Customers_LastName ON dbo.Customers REORGANIZE;

Tip:

Use SQL Server Management Studio (SSMS) or Dynamic Management Views (DMVs) like sys.dm_db_index_physical_stats to identify fragmented indexes and determine the appropriate action (reorganize or rebuild).

Covering Indexes

A covering index is a nonclustered index that includes all the columns required to satisfy a query from the index itself, without having to access the base table. This is achieved using the INCLUDE clause.

Example: Creating a Covering Index

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_CustomerID_Covering
ON dbo.Orders (OrderDate, CustomerID)
INCLUDE (TotalAmount);

This index can satisfy queries that select OrderDate, CustomerID, and TotalAmount when filtering by OrderDate and CustomerID.

MSDN Documentation