Indexes – to Rebuild or Reorganize?

Index maintenance is one of the best “bang for the buck” performance tuning activities a DBA can perform. Due to factors such as page splits and updates to index and branch leaf pages, index become fragmented, resulting the in index data no longer being physically contiguous. Looking at our available options as far as index maintenance goes, there may be some confusion as whether we should be rebuilding our indexes, or performing an index reorganization. It really depends on their degree of fragmentation.

Microsoft recommends rebuilding if fragmentation is over 30%, reorganizing if it’s between 5% and 30% and leaving it alone if fragmentation is below 5%. In SQL Server 2005, you can use the sys.dm_db_index_physical_stats function to view the fragmentation of an index.

This will get detail for all tables in your DB, returning the three key fields first and ordering by most fragmented:

—————————————————————————————-

SELECT OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
dm.avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
AND i.index_id = dm.index_id
order by avg_fragmentation_in_percent desc

—————————————————————————————-

To reorganize: ALTER INDEX REORGANIZE –reorgs and repacks the leaf pages of the index; this makes DBCC INDEX DEFRAG obsolete.

To rebuild: ALTER INDEX REBUILD WITH (ONLINE = ON) –drops the index and recreates it; this makes
DBCC DBREINDEX and DBCC INDEX REBUILD obsolete.

Rebuild syntax: ALTER INDEX PKtTable1
ON dbo.tTable1
REBUILD
GO

The (ONLINE = ON) operator allows the index to be rebuilt while the underlying data is accessed by other users (new to SQL 2005).

The following script will rebuild indexes for all tables in your database:

—————————————————————————————-

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name = ‘YourDatabaseName’
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ‘DECLARE TableCursor CURSOR FOR SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + @Database + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’

– create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

– SQL 2000 command
–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)

– SQL 2005 command
SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

—————————————————————————————-

Now, you may notice that even after rebuilding your indexes, many still show a large percentage of fragmentation. Hint: check out the page_count column of sys.dm_db_index_physical_stats. If it is a small number, SQL Server may have just skipped over this index. This is fine, as fragmentation in small tables/indexes has a minimal hindrance on performance.

About these ads
Tagged , , , , , , , , , , , , , , ,

2 thoughts on “Indexes – to Rebuild or Reorganize?

  1. Thanks, Mxhxrdba. I would like to share a solution that I’ve made that works a little differently. It does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level and lob existence.

    http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
    http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
    http://blog.ola.hallengren.com/_attachments/3440068/IndexOptimize.sql

    Ola Hallengren
    http://ola.hallengren.com

  2. Dugi says:

    In the script where we can retrieve the info about fragmentation is missing the table alias, so the script below is correct, if anyone have any problem during the run:

    SELECT OBJECT_NAME(i.object_id) AS [Table Name],
    i.name AS [Index Name],
    dm.avg_fragmentation_in_percent, *
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) dm
    INNER JOIN sys.indexes i ON i.object_id = dm.object_id
    AND i.index_id = dm.index_id
    order by dm.avg_fragmentation_in_percent desc

    Anyway nice explanation and thnx for the infos!

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: