Index fragmentation is a common issue that can affect the performance of your SQL Server database. When a page of data fills to 100 percent and more data needs to be added, a page split occurs. This means that SQL Server must move half of the data from the full page to a new page, resulting in index fragmentation.

The severity of index fragmentation can be determined by querying the sys.DM_DB_INDEX_PHYSICAL_STATS view. In SQL Server 2000, the DBCC SHOWCONTIG command was used to find index fragmentation, but in SQL Server 2005, it was deprecated and replaced by querying sys.DM_DB_INDEX_PHYSICAL_STATS.

To determine index fragmentation in SQL Server 2005, you can use the following example queries:

-- To return the index information for the SalesOrderDetail table
USE AdventureWorks;
SELECT INDEX_ID, AVG_FRAGMENTATION_IN_PERCENT
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Sales.SalesOrderDetail'), NULL, NULL, 'DETAILED');

-- To return all the information for all the indexes in the database
SELECT *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL);

Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. To address index fragmentation, you can perform index defragmentation or index rebuilding.

In SQL Server 2000, the DBCC INDEXDEFRAG and DBCC DBREINDEX statements were used to reduce table fragmentation. However, in SQL Server 2005, the preferred method is to use the ALTER INDEX statement with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement of SQL Server 2000.

Here are some examples of how to rebuild indexes in SQL Server 2005:

-- To rebuild only one index
USE AdventureWorks;
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE;

-- To rebuild all the indexes on a table with specifying options
USE AdventureWorks;
ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

It’s important to note that the FILLFACTOR option determines the percentage of space on each leaf-level page to be filled with data. A FILLFACTOR of 0 means the page is 100% full, while a FILLFACTOR of 100 means the page is completely empty. In most cases, a FILLFACTOR of 90 is recommended for optimal performance.

By regularly monitoring and addressing index fragmentation in your SQL Server database, you can ensure that your queries perform efficiently and your application responds quickly.

Thank you for reading!