Maintaining and Managing Indexes

Introduction

In this tutorial, we'll cover the maintenance and management of SQL indexes, which are vital for improving database performance. We will learn how to rebuild and reorganize indexes.

By the end of this tutorial, you'll understand what SQL indexes are, why and when they need maintenance, and how to maintain them efficiently.

Prerequisites
- Basic knowledge of SQL
- Access to a SQL database for practice

Step-by-Step Guide

Indexes in a database are similar to indexes in a book. They greatly speed up data searching but, over time, can become fragmented due to data modifications. That's why regular index maintenance, including rebuilding and reorganizing, is crucial.

Rebuilding an Index entirely recreates the index. This operation is more resource-intensive and should be performed during periods of low database activity.

Reorganizing an Index is a lighter operation that helps to reduce fragmentation and uses fewer system resources.

Best practices and tips

• Regularly monitor index fragmentation and take action when necessary.
• Schedule index maintenance during off-peak hours to minimize impact on system performance.
• Use the ONLINE option when rebuilding indexes to allow users to query the data during the operation.

Code Examples

Example 1: Checking Index Fragmentation

SELECT 
  dbschemas.[name] as 'Schema',
  dbtables.[name] as 'Table',
  dbindexes.[name] as 'Index',
  indexstats.avg_fragmentation_in_percent,
  indexstats.page_count
FROM 
  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS 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 AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
WHERE 
  indexstats.database_id = DB_ID()
ORDER BY 
  indexstats.avg_fragmentation_in_percent desc

This query retrieves the fragmentation percentage for each index in the current database. You might consider reorganizing indexes with fragmentation between 10% and 30% and rebuilding indexes with fragmentation above 30%.

Example 2: Reorganizing an Index

ALTER INDEX ALL ON Production.Product
REORGANIZE;

This code reorganizes all indexes on the Production.Product table to reduce fragmentation.

Example 3: Rebuilding an Index

ALTER INDEX PK_Product_ProductID ON Production.Product
REBUILD;

This code rebuilds the PK_Product_ProductID index on the Production.Product table.

Summary

In this tutorial, we've learned about the importance of maintaining SQL indexes, how to check index fragmentation, and how to reorganize and rebuild indexes.

For further learning, explore the use of FILLFACTOR in index creation and the sys.dm_db_index_usage_stats dynamic management view.

Practice Exercises

1. Check the fragmentation of all indexes in your database. Which index has the highest fragmentation?
2. Reorganize an index with fragmentation between 10% and 30%.
3. Rebuild an index with fragmentation over 30%.

Tips for further practice: Try to automate the process of checking fragmentation and maintaining indexes using SQL Server Agent jobs or similar scheduling tools.