Query to Find the fragementation of the Table

 Script to find the fragementation of a table and choose the Rebuild or Reorganize the indexes.

SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Table Name'), 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

For Ex:

USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     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

The statement might return a result set similar to the following.

 

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Depends on Fragementation we can reorganize or  rebuld the indexes .

avg_fragmentation_in_percent value

Corrective statement


> 5% and < = 30% 

ALTER INDEX REORGANIZE


> 30%

ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Rebuilding an index can be executed online or offline. Reorganizing an 
index is always executed online. To achieve availability similar to the 
reorganize option, you should rebuild indexes online.