Index Rebuild

 (Following Index Reorganize post)

————————————————————

REBUILD CLUSTERED INDEXES

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
DECLARE @v_fillfactor smallint
SET @v_fbm_low = 30
SET @v_fbm_high = 100
SET @v_fillfactor = 80
SELECT 'ALTER INDEX '+[INDEX_NAME]+' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME] +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@v_fillfactor) + ', STATISTICS_NORECOMPUTE = ON, ONLINE = ON);'
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],
i.fill_factor AS [FILL_FACTOR]
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
WHERE [INDEX_TYPE] = 'CLUSTERED'
ORDER BY [SCHEMA_NAME], [OBJECT_NAME]

REBUILD NON-CLUSTERED INDEXES

DECLARE @v_fbm_low smallint --fragmentation benchmark
DECLARE @v_fbm_high smallint
DECLARE @v_fillfactor smallint
SET @v_fbm_low = 30
SET @v_fbm_high = 100
SET @v_fillfactor = 80
SELECT 'ALTER INDEX '+[INDEX_NAME]+' ON ' + [SCHEMA_NAME] + '.' + [OBJECT_NAME]+
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR, @v_fillfactor) + ', STATISTICS_NORECOMPUTE = ON, ONLINE = ON);'
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],
i.fill_factor AS [FILL_FACTOR]
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
WHERE [INDEX_TYPE] = 'NONCLUSTERED';

FILLFACTOR is a tricky part. I think for any Oracle DBA it would be very tempting to think it’s a direct analogy with PCTUSED, but not quite. Long story short, in SQL Server it’s relevant only for creating or rebuilding indexes, not tables. Table pages are splitted in half whenever full, regardless of FILLFACTOR setting. Indexes behave differently. Generally, non-cluster indexes built on highly volatile tables would benefit from saving on leaf-level page splits when created or rebuilt with FILLFACTOR < 100, say b/w 70 and 90.

Clustered indexes built on IDENTITY columns should have FILLFACTOR 100 as the value of identity column doesn’t get updated, but rather is always inserted to the end of last page.

This two articles of Pinal Dave give some detailed insight on FILLFACTOR and its role in index creation/rebuild.

What is Fill factor? – Index, Fill Factor and Performance – Part 1

What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2

Useful TechNet articles:

Reorganizing and Rebuilding Indexes
Fill Factor
Setting Index Options
Configuring Parallel Index Operations
 if you hit any errors while running ONLINE index rebuild, it’s worthwhile to check this article:
Guidelines for Performing Online Index Operations
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: