Index Performance
Learn how indexes improve query performance and how to maintain them effectively in SQL Server.
What is Index Performance?
Index performance refers to how effectively indexes help SQL Server find and retrieve data from a table.
Properly designed indexes can significantly improve SELECT query performance.
However, unnecessary or poorly designed indexes can increase storage usage and slow down INSERT, UPDATE and DELETE operations.
Why Do Indexes Improve Performance?
Without a suitable index, SQL Server may need to examine many or all rows in a table to find the required data.
This operation is commonly known as a Table Scan.
With a suitable index, SQL Server may be able to locate the required rows more efficiently.
Table Scan
A table scan occurs when SQL Server reads rows throughout a table to find matching records.
SELECT *
FROM Students
WHERE City = 'Pune';
If there is no useful index on the City column, SQL Server may need to scan a large portion of the table.
Index Seek
An Index Seek allows SQL Server to navigate directly to the relevant portion of an index instead of examining every row.
Index seeks are generally efficient when a suitable index exists and the query can use it effectively.
Example of Index Performance
Suppose we have the following table:
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Course VARCHAR(100),
City VARCHAR(50),
Age INT
);
Create an index on City:
CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City);
Now execute:
SELECT *
FROM Students
WHERE City = 'Pune';
SQL Server can consider the City index when creating the execution plan for this query.
Execution Plan
SQL Server uses an Execution Plan to determine how a query should be executed.
The execution plan can show whether SQL Server is using operations such as:
- Index Seek
- Index Scan
- Table Scan
- Key Lookup
Viewing Execution Plan in SSMS
In SQL Server Management Studio (SSMS), you can enable the Actual Execution Plan before running a query.
Keyboard shortcut:
Ctrl + M
After enabling it, execute the query and open the Execution Plan tab to analyze how SQL Server processed the query.
Index Seek vs Index Scan
| Index Seek | Index Scan |
|---|---|
| Searches specific portions of an index. | Reads a larger portion or the entire index. |
| Usually efficient for selective queries. | Can be useful when many rows are required. |
| Typically accesses fewer rows. | May process many rows. |
Index Selectivity
Selectivity describes how effectively an index can distinguish between rows.
Columns with many distinct values often have higher selectivity than columns with only a few distinct values.
For example, a StudentId column usually has better selectivity than a Gender column.
Choosing Columns for Indexes
Good candidates for indexes are columns frequently used in:
- WHERE conditions
- JOIN conditions
- ORDER BY
- GROUP BY
However, indexes should be created based on actual query and workload requirements.
Covering Index
A covering index contains all the columns required by a query, allowing SQL Server to retrieve the required information directly from the index.
CREATE NONCLUSTERED INDEX IX_Students_City
ON Students(City)
INCLUDE(StudentName, Course);
In this example, City is the index key while StudentName and Course are included columns.
Problem with Too Many Indexes
Creating an index for every column is not recommended.
Each additional index requires storage and may need maintenance whenever data changes.
- More storage usage
- Slower INSERT operations
- Additional UPDATE maintenance
- Additional DELETE maintenance
Index Fragmentation
Index fragmentation can occur as rows are inserted, updated and deleted over time.
Fragmentation can affect the efficiency of index access, especially in heavily modified tables.
Checking Index Fragmentation
SQL Server provides sys.dm_db_index_physical_stats to inspect index physical statistics.
SELECT
OBJECT_NAME(object_id) AS TableName,
index_id,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
NULL,
NULL,
NULL,
'LIMITED'
);
Rebuilding an Index
An index can be rebuilt to reorganize its structure.
ALTER INDEX IX_Students_City
ON Students
REBUILD;
Rebuilding an index can be useful when significant fragmentation needs to be addressed.
Reorganizing an Index
SQL Server also supports index reorganization.
ALTER INDEX IX_Students_City
ON Students
REORGANIZE;
Reorganize is an online operation that incrementally defragments an index.
REBUILD vs REORGANIZE
| REBUILD | REORGANIZE |
|---|---|
| Recreates the index structure. | Defragments the existing index incrementally. |
| Can be more resource intensive. | Generally less resource intensive. |
| Useful when substantial maintenance is required. | Useful for lighter fragmentation. |
Index Performance Best Practices
- Create indexes based on actual query requirements.
- Avoid creating unnecessary indexes.
- Monitor query performance regularly.
- Review execution plans for important queries.
- Monitor index fragmentation where appropriate.
- Maintain indexes according to workload requirements.
- Consider covering indexes for frequently executed queries.
Important Points
- Indexes can improve SELECT query performance.
- Index Seek is generally efficient for selective searches.
- Table Scan may occur when a suitable index is unavailable.
- Too many indexes can negatively affect write operations.
- Execution Plans help analyze query performance.
- Index fragmentation should be monitored when appropriate.
- REBUILD and REORGANIZE are available for index maintenance.
Summary
Indexes are an important part of SQL Server performance optimization. A properly designed index can reduce the amount of data SQL Server needs to examine when executing queries.
Execution plans can be used to understand how SQL Server accesses data and whether operations such as Index Seek, Index Scan or Table Scan are being used.
Indexes should be created and maintained carefully because they require storage and can increase the cost of data modification operations.