THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Lara Rubbelke

Interesting Things in the World of SQL Server

Smart Index Defragmentation for an ONLINE World

One of the many reasons I why I love consulting is the constant evolution and challenge.  You may have something that works “just fine” until a new customer simple states “This is great.  Can you make it do XYZ too?”  I love those “could you just” statements, because they challenge the status quo and help you create some really meaningful solutions.

Recently I have been engaged with a customer who was migrating from SQL Server 7.0 to SQL Server 2005 Enterprise Edition for a high profile web application requiring high availability.  They have large tables, and will be embracing partitioning in the near future.  At this time, they cannot sustain the downtime necessary to address index fragmentation, and wanted to defragment their indexes ONLINE (Enterprise Edition feature which significantly reduces downtime when rebuilding indexes).  In the past, I had developed a centralized “smart reindex” procedure but this did not account for ONLINE index processing.  Available for download on this blog is an update to this procedure which will accommodate addressing index fragmentation online and/or offline.  This is a single procedure which should be stored centrally.  It can be executed against any user database in an instance.  I will also include the SQL Server 2005 Standard Edition procedure which will not offer an option to execute using ONLINE.

I will not go into a full discussion of how SQL Server rebuilds an index ONLINE, as this is discussed in Books Online.  I will discuss some of the nuances and caveats of building indexes ONLINE that directly relate to the attached procedure.

First, an index cannot be built ONLINE if it contains LOB data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml).  For a clustered index, you cannot rebuild the clustered index ONLINE if any columns exist in the table with these data types.  For a non-clustered index, you cannot rebuild the index ONLINE if the index contains any columns with these data types.

Second, a single partition of an index with multiple partitions cannot be rebuilt ONLINE.  You can REORGANIZE a single index partition (which is by nature ONLINE), but you cannot REBUILD a single index partition ONLINE.  You can rebuild an entire index (all partitions) ONLINE.

These important rules are handled in the attached procedure with the following logic:

The user specifies @online= ‘ON’ and @fullprocess = ‘ON’

An index partition from an index with multiple partitions will be processed with REORGANIZE

An index partition from an index with a single partition will be processed with REBUILD WITH(ONLINE=ON) if the index does not contain columns which of a data type not supported by ONLINE index processing.

An index partition from an index with a single partition which contains a column with a data type not supported by ONLINE index processing will be processed with REORGANIZE or REBUILD (OFFLINE) depending on the level of fragmentation.

The user specifies @online= ‘ON’ and @fullprocess = ‘OFF’

An index partition from an index with multiple partitions will be processed with REORGANIZE.

An index partition from an index with a single partition will be processed with REBUILD WITH(ONLINE=ON) if the index does not contain columns which of a data type not supported by ONLINE index processing.

An index partition from an index which contains a column with a data type not supported by ONLINE index processing will not be processed.

The user specifies @online= ‘OFF’ and @fullprocess = ‘ON’

All indexes are processed with REORGANIZE or REBUILD (OFFLINE) depending on the level of fragmentation.

The user specifies @online= ‘OFF’ and @fullprocess = ‘OFF’

No indexes are processed.

This procedure will not support the capability to process an index with multiple partitions ONLINE.  Remember, if an index has multiple partitions you can REORGANIZE each partition or you can REBUILD ONLINE the entire index (all partitions).  This process assumes that REORGANIZE is acceptable for index partitions meeting these criteria.  Since this is inherently ONLINE, it may be acceptable.  The logic can be altered to ignore these indexes, if this is not acceptable.  Since REORGANIZE may not be as complete as REBUILD, or may require more time to eliminate fragmentation, the user should know that they can specify @online= ‘OFF’ and @fullprocess = ‘ON’ during an acceptable maintenance window to address the fragmentation.

Published Monday, July 30, 2007 10:26 AM by Lara Rubbelke

Attachment(s): RebuildIndex_SmartOnline.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jerry Jung said:

Thank you, sounds like a very useful procedure. Index Index Index....

We schedule our re-index (only on certain tables) monthly, not "smartly" though

July 31, 2007 2:08 PM
 

Brian C. said:

When I try and run the script this is the error I keep getting.

Msg 208, Level 16, State 6, Procedure ap_RebuildIndexes, Line 222

Invalid object name 'ap_RebuildIndexes'.

August 13, 2007 3:20 PM
 

Cory Nguyen said:

I wrote my own proc to do the same and wanted to let you know that by using user_type_id instead of system_type_id the proc will not be able to properly handle the logic when user defined data types are found within the database.  My suggestion is to change the proc to use system_type_id because this will alwasy reflect the true nature of the data type that you are trying to verify.

August 27, 2007 5:31 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement