Index fragmentation

Index fragmentation

This post describes HOW part of WHAT to do for database performance improvement and index fragmentation is to identified as one of the cause of database performance hampering factor. There are other factors which might have negative effect on database performance; we shall deal with those one by one in subsequent posts. For now, let’s consider Index fragmentation

Cause of fragmentation can be attributed to heavy inserts, updates to the clustering key as well as updates resulting in page splits occurring to the data pages or the index pages.

Identifying Index fragmentation

You can determine the amount of fragmentation on the database by wxwcuting the following DBCC commands or the DMVs

use adventureworks2008r2; go DBCC ShowContig; SELECT * FROM sys.dm_db_index_physical_stats (     DB_ID ('adventureworks2008r2'), OBJECT_ID ('Address'), NULL, NULL, 'DETAILED'); -- mode 'LIMITED', 'DETAILED', 'SAMPLED' GO DBCC CHECKTABLE('Person.Address',1); GO

The DBCC ShowContig; statement returns the fragmentation data for all the tables in the database, let’s select the address table and execute the next statement on Address table

Result of DBCC ShowContig;

/*------------------------ DBCC ShowContig; ------------------------*/ DBCC SHOWCONTIG scanning 'ProductInventory' table... Table: 'ProductInventory' (2099048); index ID: 1, database ID: 16 TABLE level scan performed. - Pages Scanned................................: 7 - Extents Scanned..............................: 2 - Extent Switches..............................: 1 - Avg. Pages per Extent........................: 3.5 - Scan Density [Best Count:Actual Count].......: 50.00% [1:2] - Logical Scan Fragmentation ..................: 28.57% - Extent Scan Fragmentation ...................: 50.00% - Avg. Bytes Free per Page.....................: 600.0 - Avg. Page Density (full).....................: 92.59% DBCC SHOWCONTIG scanning 'SpecialOffer' table... Table: 'SpecialOffer' (14623095); index ID: 1, database ID: 16 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Logical Scan Fragmentation ..................: 0.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 5432.0 - Avg. Page Density (full).....................: 32.89% DBCC SHOWCONTIG scanning 'Address' table... Table: 'Address' (85575343); index ID: 1, database ID: 16 TABLE level scan performed. - Pages Scanned................................: 340 - Extents Scanned..............................: 44 - Extent Switches..............................: 43 - Avg. Pages per Extent........................: 7.7 - Scan Density [Best Count:Actual Count].......: 97.73% [43:44] - Logical Scan Fragmentation ..................: 0.29% - Extent Scan Fragmentation ...................: 2.27% - Avg. Bytes Free per Page.....................: 98.8 - Avg. Page Density (full).....................: 98.78% DBCC SHOWCONTIG scanning 'ProductListPriceHistory' table... . . Rest of the message is omitted.

Result of executing dm_db_index_physical_stats DMV will return one row per heap page having “IN_ROW” allocation, a row each per index per B-Tree level per partition. For detailed documentation of dm_db_index_physical_stats DMV, please visit msdn: http://msdn.microsoft.com/en-us/library/ms188917.aspx

Result of executing DBCC CHECKTABLE on Person.Address table

/*------------------------ DBCC CHECKTABLE('Person.Address',1); -- Clustered ------------------------*/ DBCC results for 'Person.Address'. There are 19614 rows in 340 pages for object "Person.Address". DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To view all the pages across the indexes and heap, allocated for the Address table, we can use DBCC IND command as follows

DBCC IND ('adventureworks2008r2','Person.Address',-1);

Defragmentation

There are two ways for defragmenting indexes, first one Executing DBCC INDEXDEFRAG, DBCC DBREINDEX on the specific table and clustered index. The second one is to execute Alter index statement with rebuild or reorder TSQL commands.   

Rebuild corresponds to DBCC REINDEX and Reorganize corresponds to DBCC IndexDefrag. For details please visit technet: http://technet.microsoft.com/en-us/library/ms189858.aspx

USE AdventureWorks2008R2; GO DBCC INDEXDEFRAG (AdventureWorks2008R2, [HumanResources.EmployeeDepartmentHistory],PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID) GO USE AdventureWorks2008R2; GO DBCC DBREINDEX ("HumanResources.EmployeeDepartmentHistory",PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ,80); GO USE AdventureWorks2008R2; GO ALTER INDEX                   PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID             ON                   [HumanResources.EmployeeDepartmentHistory]  REBUILD; go USE AdventureWorks2008R2; GO ALTER INDEX                   PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID             ON                   [HumanResources.EmployeeDepartmentHistory]  REORGANIZE; go

Rule of thumb for deciding between Alter Index Rebuild and alter Index Reorganize is if fragmentation (described by “avg_fragmentation_in_percent” of sys.dm_db_index_physical_stats DMV) is between 5% to 30%, then go for Reorganize if fragmentation is more than 30% then go for Rebuild.

In certain cases, in spite of executing these commands, you may still find the pages with free spaces, which might be because of the index having very few pages and data is not enough to fill the page.

To view this, use the DBCC Ind command to view the allocated pages for the particular table and to view individual page, used the DBCC page command as shown below.

DBCC IND ('adventureworks2008r2','Person.Address',-1); DBCC Traceon (3604); DBCC PAGE( 'adventureworks2008r2',1,20320,3);

There are certain overheads like resource locking, transaction logging, IO operations performed during index reorganization or rebuild, which might impact the online operation during this operation.

Recommendation is to perform these kinds of database maintenance activities during the low usage period of the application or down time window of the application, so that the online operations are not impacted.

Enjoy!