How to check Index Fragmentation in SQL Server

Hello friends, In my last article we learnt what is fragmentation and types of fragmentation. In this article we will learn how we can check %age of fragmentation using SQL scripts.

Detecting fragmentation in SQL 2005 and later versions:- In SQL Server 2005 and later versions, Microsoft has introduced concept of DMVs(Dynamic Management Views), to detect fragmentation in SQL Server 2005 and later version we can use DMV. Below is an example of DMV that can be used for detecting fragmentation.

Sample Query-->Not to be run

SELECT OBJECT_NAME(OBJECT_ID) as tableName, index_id,index_type_desc,index_level,

avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count

FROM sys.dm_db_index_physical_stats(DB_ID, Object_Id, Index_Id, Partition ,Mode)

DMV Name: - sys.dm_db_index_physical_stats

Parameters:-

DB_ID-->Need to specify the database id for which we want to get the fragmentation detail (Null, 0, Default).

Object ID-->If we want to get fragmentation detail of a particular table they we specify the object id (Null, 0, Default).

Index_ID-->To find fragmentation detail into a particular index we specify index ID (Null,-1, Default).

Partition-->To find fragmentation detail into a particular partition, we specify Partition (Null, 0, Default).

Mode-->Valid values for this parameter is (Default, Limited, Sampled, Detailed).

SELECT OBJECT_NAME(OBJECT_ID) as tableName, index_id,index_type_desc,index_level,

avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), Object_Id('Emp'), Default, Default ,Default)

The above query will return fragmentation detail of all the indexes and Heap on “Emp” table(As shown in the below screen shot)

avg_fragmentation_in_percent :- This column value represents the %age of logical fragmentation of indexes or extent fragmentation of heap.

avg_page_space_used_in_percent:- This column represents the internal fragmentation within the page.

Corrective actions:-

If avg_fragmentation_in_percent(%age) >5 and avg_fragmentation_in_percent <30 % then we should re-organize the indexes to minimize the fragmentation.

Script for re-organize index :-

alter index xx on emp reorganize

To reorganize particular index(“xx”) on Emp table

alter index all on emp reorganize

To reorganize all the indexes on emp table.

If avg_fragmentation_in_percent(%age) >30 then we should re-build the indexes to minimize the fragmentation.

alter index all on emp rebuild

To rebuild all the indexes on emp table.

alter index xx on emp rebuild

To rebuild particular index(“xx”) on Emp table

Detecting fragmentation in SQL 2000 and earlier versions:- To get the fragmentation detail in SQL Server 2000 and earlier version, we did not have DMVs , we had DBCC command to get the details of fragmentation.

DBCC showcontig

To find fragmentation detail of all tables within a database, we run the above query.

DBCC showcontig('emp')

To find fragmentation detail of “Emp” table, we run the above query.

DBCC indexdefrag(9,'emp')

To reorganize all the indexes on “Emp” table where “9” is the database id

DBCC dbreindex('emp')

To rebuild all the indexes on “Emp” table we can use the above query

I hope this article will help you in detecting fragmentation and to take corrective actions accordingly.