Monday, April 11, 2011

How to View Index Fragmentation

The scripts below (provided by Cory Brown of Microsoft) were used to determine how badly the indexes were fragmented:

-- ==========================================================================
-- Description: The purpose of this script is to look at the fragmentation of

--              existing indexes in the database
--              and determine if the index should be REBUILT, REORGANIZED, or

--              statistics updated.
-- Date:        September 22, 2010
-- Author:      Cory Brown
-- ===========================================================================

--Rebuild Index recommended if fragmentation is above 30%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent > 30
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC

--Reorganize Index recommended if fragmentation is between 15% and 30%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent < 30
AND       avg_fragmentation_in_percent > 15
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC

--UpdateStats recommended if fragmentation is below 15%
SELECT    DB_NAME(database_id),*
FROM      sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE     avg_fragmentation_in_percent < 15
AND       page_count > 1000
ORDER BY  avg_fragmentation_in_percent DESC
 

No comments:

Post a Comment