Home > Handy Scripts, Tuning and Optimization > Index Reorganize (Defragmentation)

Index Reorganize (Defragmentation)

Here are two explicit articles, nothing really to add to it.

  1. Rebuilding Clustered Indexes Efficiently with the DROP_EXISTING command in SQL Server, By: Armando Prato
  2. TechNet article

This is sophisticated Index Defrag and Rebuild SP, by Michelle Ufford, which does the job automatically; you just need to plug the values to the variables when calling it.

This is short “copy-paste-and-execute-in-SSMS” script on mine:

--===================================================--
DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
SET @v_fbm_low = 5
SET @v_fbm_high = 30
SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) A
INNER JOIN SYS.INDEXES I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID = I.OBJECT_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
ORDER BY [INDEX_NAME] --[OBJECT_NAME]

 –===================================================–

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
SET @v_fbm_low = 5
SET @v_fbm_high = 30
SELECT 'ALTER INDEX '+[INDEX_NAME]+ ' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME]+ ' REORGANIZE;'
FROM
(SELECT
SCHEMA_NAME(o.schema_id) AS [SCHEMA_NAME],
OBJECT_NAME(a.OBJECT_ID) AS [OBJECT_NAME],
i.name AS [INDEX_NAME],
i.type_desc AS [INDEX_TYPE],
a.index_type_desc AS [INDEX_TYPE_DESC],
a.alloc_unit_type_desc AS [ALLOC_UNIT_TYPE_DESC],
a.index_depth AS [INDEX_DEPTH],
a.avg_fragmentation_in_percent AS [AVG_FRAGMENTATION_IN_PERCENT],
a.fragment_count AS [FRAGMENT_COUNT],
a.avg_fragment_size_in_pages AS [AVG_FRAGMENT_SIZE_IN_PAGES],
a.page_count AS [PAGE_COUNT]
FROM sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL, NULL) a
INNER JOIN sys.indexes i ON i.[OBJECT_ID] = a.[OBJECT_ID] AND i.INDEX_ID = a.INDEX_ID
INNER JOIN sys.objects o ON o.OBJECT_ID = i.OBJECT_ID
WHERE  OBJECTPROPERTY(a.[OBJECT_ID],'IsUserTable') = 1
AND a.avg_fragmentation_in_percent BETWEEN @v_fbm_low AND @v_fbm_high
AND page_count > 8 --ignore tables with less then one extent
) a

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: