Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008.  The code change has just one bug fix.  The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters. 

In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type.  In SQL Server 2008, the DMF was modified so that you could have either “XML INDEX” or “PRIMARY XML INDEX” for XML indexes in the index_type_desc column.

I don’t have any systems that use the XML data type, so it wasn’t until we had the SQL Server 2008 AdventureWorks database on a development system that we found the bug.

Here’s the code change in case you are interested:

SET @online = 
        CASE
            WHEN @indexType IN ('XML INDEX', 'PRIMARY XML INDEX') THEN 0
            WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
            WHEN @lobData = 1 THEN 0
            WHEN @disabled = 1 THEN 0
            WHEN @partitionCount > 1 THEN 0
            ELSE 1
        END
You can download the new version of the stored procedure here.

Print | posted on Tuesday, November 03, 2009 11:04 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Hi Tara,

It seems the proc only provides rebuilding index ONLINE option. However, some of your apps are not 24/7 and we want to rebuild index OFFLINE instead.

Would you be able to help me on this request?

Thanks much,
Silaphet
11/11/2009 1:26 PM | Silaphet
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

The stored procedure provides the ability to do indexes ONLINE or OFFLINE depending upon the SQL Server edition as well as the index. If you want to override the stored procedure code, simply comment out EXEC(@SQL) and instead use PRINT @SQL. You can make changes from there.

I don't think I'd bother with OFFLINE index rebuilds though if ONLINE is available even if you have a time to do the work. Just do them ONLINE.
11/11/2009 1:30 PM | Tara
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Hi Tara,

This is awesome and definately will help us out. Can you tell me where the log history is then stored?

Thanks,
Jon
11/18/2009 12:47 PM | Jon H
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Jon,

The log history table is stored in the same database as the isp_ALTER_INDEX database, otherwise the stored procedure will fail. The log history table script can be found in the download in the same file where the isp_ALTER_INDEX object is scripted. I put them in the same file since the log history table is required even if you decide not to use that feature.

~Tara
11/18/2009 12:55 PM | Tara
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Awesome thank you for the prompt feedback.
Is it best to create this stored procedure directly in the master database or is it best to create a database called "CenterAdmin" or something to that effect and call it like so:

exec isp_ALTER_INDEX MyDb, etc, etc

?
11/18/2009 2:59 PM | Jon H
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

I don't recommend putting any custom objects in any of the system databases, including master. Instead I recommend that custom DBA-type objects should be put into a database named DBA or perhaps Admin.

The reason for doing this is to separate custom objects from system objects, plus restoring master is a huge pain. If you ever need to grab your custom objects from a backup, it is so much simpler to do a restore on a user database than to restore master.

~Tara
11/18/2009 3:41 PM | Tara
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

One thing I would do is to throw an error if the end user types in the wrong database name.
I was trying to execute this for a database called TimeOff...it kept running forever. I thought well it probably is running because this database is fairly large. Then I found out the db was named TKKTimeOff, changing the name it ran instantly!

Again this sproc saved me :) pat on the back tara.

Jon
11/18/2009 3:47 PM | Jon H
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Jon, thanks for the information. I will look into that.

~Tara
11/20/2009 9:19 PM | Tara
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Tara,

So far, your procedure work great, however, every now and then I get the following error:

Msg 8115, Level 16, State 6, Procedure isp_ALTER_INDEX, Line 255
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated.

It looks like line 255 is where you insert some log information into the DefragmentIndexes table.
Your DefragmentIndexes.PercentFragmented column is datatype decimal (4,2). You are loading this column from the value from sys.dm_db_index_physical_stats.avg_framentation_in_percent column, which is a float datatype.

Is there any reason why you did not make your table column a float datatype?

Thank you and Happy New Year,
Jon
12/31/2009 3:54 PM | Jon Russell
Gravatar

# re: Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Jon, thank you for posting this. I will work to get this resolved soon.
1/11/2010 1:29 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET