Basic Index Maintenance in SQL Server
Indexes are crucial in SQL Server for optimizing query performance. They work like a book’s index, enabling the SQL Server to locate data quickly without scanning the entire table. However, indexes can become fragmented or outdated over time, leading to performance degradation. This article outlines basic index maintenance tasks in SQL Server to ensure your indexes remain efficient and effective.
Understanding Index Fragmentation
Before diving into index maintenance, it’s important to understand fragmentation. Fragmentation occurs when the logical order of pages in an index does not match the physical order. This misalignment can slow down query performance because SQL Server needs to read more pages than necessary.
There are two main types of fragmentation:
- Internal Fragmentation: This happens when pages contain unused space due to page splits, where SQL Server divides a page into two because of data insertion.
- External Fragmentation: Occurs when the logical order of index pages does not match their physical order on disk.
Checking Index Fragmentation
You can check the fragmentation level of your indexes using the sys.dm_db_index_physical_stats dynamic management view (DMV). This DMV provides details about the physical storage of indexes, including the level of fragmentation.
Here’s a query to check the fragmentation:
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
This query returns the average fragmentation percentage of each index in the database. High fragmentation levels (generally above 30%) indicate that the index may need maintenance.
Reorganizing Indexes
Index Reorganization is a process that defragments an index by reordering the leaf-level pages to match the logical order. It’s a light, online operation that can be performed while the database is online and available to users. For more details, you can refer to Microsoft’s documentation on ALTER INDEX…REORGANIZE.
To reorganize an index, use the following command:
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;
Or to reorganize all indexes on a table:
ALTER INDEX ALL ON [SchemaName].[TableName] REORGANIZE;
Reorganizing is recommended when fragmentation is between 5% and 30%.
Rebuilding Indexes
Index Rebuilding is a more intensive operation that drops and recreates the index. It removes fragmentation by rebuilding the entire index structure from scratch. This operation can be performed online or offline, but it requires more resources than reorganization. More information on this can be found in the Microsoft documentation on ALTER INDEX…REBUILD.
To rebuild an index, use the following command:
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;
Or to rebuild all indexes on a table:
ALTER INDEX ALL ON [SchemaName].[TableName] REBUILD;
Rebuilding indexes is typically recommended when fragmentation exceeds 30%.
Automating Index Maintenance
Regular index maintenance is crucial, and automating this process can save time and ensure consistent performance. You can use SQL Server Agent to schedule regular index maintenance tasks.
Here’s a simple SQL script that can be scheduled:
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @SchemaName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE IndexCursor CURSOR FOR
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID();
OPEN IndexCursor;
FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @Fragmentation;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Fragmentation > 30
BEGIN
SET @sql = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD';
END
ELSE IF @Fragmentation BETWEEN 5 AND 30
BEGIN
SET @sql = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE';
END
EXEC sp_executesql @sql;
FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @Fragmentation;
END
CLOSE IndexCursor;
DEALLOCATE IndexCursor;
This script will check the fragmentation level of each index and decide whether to reorganize or rebuild based on the fragmentation percentage.
Conclusion
Proper index maintenance is essential for optimal database performance. Regularly check your indexes for fragmentation and use the appropriate maintenance strategy—reorganize or rebuild—based on the level of fragmentation. Automating these tasks ensures your SQL Server database continues to perform efficiently without manual intervention. For more advanced techniques and options, you can refer to Microsoft’s documentation on index maintenance.
Do you need an external pair of eyes to review your SQL Server architecture?
Maybe your platform development and growth have been organic rather than going through a full enterprise architect design. You have good dev team but NO DBA input. Improvements are built by seeing the real world ‘failure’ as opposed to gaming it out at the DEV/QA stage.
I can offer a full platform evaluation, covering questions such as:
• what is your usage (potential required iops, memory, cpu, space requirements)
• Real world behaviour, usage patterns, etc.
• Routine maintenance review
• Backups / failover review
• UAT environment review
• monitoring / metrics review
Some of the questions I can investigate:
• Are you wrongly sized?
• Are you on the right platform?
• Are you making best use of what you have?
• What is the best model for your business?
• What level of DBA input do you need?
I can provide all of above via my two day “Health Check”. Click here for more info
