Fragmentation Part II

How to detect fragmentation?

SQL Server 2005 introduces a new DMV to identify the fragmentation levels using sys.dm_db_index_physical_stats and we can use DBCC ShowContig (deprecated) command to view the fragmentation.

The DMV can display information in 3 different modes.

DETAILED -- It displays information by reading all data pages and index pages. We need to be careful while using this option because entire index will read into memory and result I/O issues.

SAMPLED -- Reads 1% of the pages if more than 10,000 pages

LIMITED -- Reads only parent level of b-tree and it doesn’t displays the page density.

Syntax

SELECT *  FROM sys.dm_db_index_physical_stats(DB_ID('KalyanDB'), NULL, NULL, NULL , NULL);

Parameters ( 1st- Database Id, 2nd –ObjectId, 3rd – IndexId, 4th – Partition Number, 5th – mode)

If you specify NULL it displays Limited mode information by default for the given database for all objects and indexes.

We need to pay attention on two columns to identify the fragmentation

Avg_fragmentation_in_percent -- The percentage of logical fragmentation (out of order pages in the index). A value between 5% - 30% indicates moderate fragmentation, while any value >30% indicates high fragmentation

Avg_page_space_used_in_percent -- Average number of pages in one fragment in an index.

If avg_fragmentation_in_percent value is between 5% and 30% then Action = Reorganize Index (SQL Statement – Alter Index Reorganize which replaces DBCC INDEXDEFRAG)

If the value is >30% then Action = Rebuild Index (SQL Statement – Alter Index Rebuild which replaces DBCC REINDEX)

REORGANIZE will always executed online while REBUILD index is executed offline by default and can be executed online by giving ONLINE in Alter Index statement. 

Below are few T-SQL statements from BOL which helps to identify the fragmentation percentage of each index and for Alter statements

Displays fragmentation percentage of indexes for the specified database,
SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'KalyanDB'), OBJECT_ID(N'Students'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

To Reorganize any Index 

ALTER INDEX IndexName ON Students REORGANIZE ;
GO
To Reorganize all Indexes

 ALTER INDEX ALL ON Students REORGANIZE ;
GO

To Rebuild any Indexes

ALTER INDEX IndexName ON Students REBUILD ;
GO

To Rebuild All Indexes

ALTER INDEX ALL ON Students REBUILD ;
GO