Defragmenting/Rebuilding Indexes in SQL Server 2005

Tue Jun 23, 2009 by Tara Kizer

UPDATE: A new version of this stored procedure is available. Please check it out here.

I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005. The changes include two bug fixes and one feature request.

The first bug fix was reported by Fedor Baydarov. He found that @lobData was not being re-initialized to zero after a LOB data type was encountered. This meant that the rest of the indexes to be processed were being done offline even if the online option was available. D’oh!

The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index. The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered. This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it. I came across the bug recently on a system that had such an index.

The feature request was to add the option to do the sort operation in the tempdb database. This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs. See ALTER INDEX topic in SQL Server Books Online for more details.

You can download the new version of the stored procedure here.

Let me know if you run into any issues with it. I’d also be interested to hear if it works on SQL Server 2008.

Legacy Comments


Peso 2009-06-23	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Maybe an improvement? 1. Add NULL as default value for @dbName 2. If @dbname is null, set to current database Works great when used in jobsteps, since database is set with dropdown list. CREATE PROC [dbo].[isp_ALTER_INDEX] ( @dbName sysname = null, @statsMode varchar(8) = 'SAMPLED', @defragType varchar(10) = 'REORGANIZE', @minFragPercent int = 25, @maxFragPercent int = 100, @minRowCount int = 0, @logHistory bit = 0, @sortInTempdb bit = 0 ) AS SET NOCOUNT ON IF ... IF ... IF @dbname IS NULL SET @dbName = select db_name() The script works well in 2008.

Tara 2009-06-24	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Hi Peter, I actually disagree with adding this to the code as this type of stored procedure should be stored in a generic database that is not associated with applications, named such as Admin or DBA. And in that type of database you don't care about defragmenting or rebuilding indexes. So anyway, you should build your job step code to handle this situation rather than modifying the stored procedure code. Thanks for the info on 2008. ~Tara

Brian 2009-06-25	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 I am a bit new to SQL, but our vendor told us we might need to re-index our DB. I found your highly recommended script and I thought I would try it, since I am inexperienced in SQL. Executed the script and got the following error: Msg 102, Level 15, State 1, Procedure isp_ALTER_INDEX, Line 89 Incorrect syntax near '('. Does this mean something or am I running things incorrectly?

GeorgeP 2009-06-26	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Hi Tara, You can use sp_ms_marksystemobject procedure to mark it as a system object, to be available in all user databases. More info here: http://www.mssqltips.com/tip.asp?tip=1612 George

Tara 2009-06-29	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Brian, make sure your database compatibility level is at least 90 for the database where you are trying to place this object. George, there is no need to mark it as a system object as it does not need to be made available to more than one database. Just place is in a DBA-type database, we name ours Admin, and then pass the various database names to the object as an input parameter.

Ehussain 2009-07-08	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Can I run this against the specific database or this is for all the databases on the server?? let me know. Thanks,

aasim abdullah 2009-07-13	re: Defragmenting/Rebuilding Indexes in SQL Server 2005 Tara, i am using ur script for all of my production databases. i use it with REBUILD option. I am facing ldf file size problem. When ever i execute this script to rebuild all my indexes my ldf grows to 1 GB every time. What i am missing ? How to control this log file size while rebuilding my indexes on daily basis.?