Ramblings of a DBA

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

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