Read this article in your language IT | EN | DE | ES

Well, after reviewing my earlier posts on building a reindex script, I think I’ll break it a part a bit. On one hand getting the best disk performance from SQL requires it’s files to be defragmented, on the other the placement of the pages inside the database file need to be “defragmented”.

The challenge with the first requires running a defrag utility and I’ve got a project open on that. I’ll be throwing that out in an article designed to sell the utility I’m building, which will include the SQL reindexing too.

In the mean time, if you came here just for a reindex script that beats the pants of anything else you’ve Googled for then here it is:

DECLARE 
    @databaseName varchar(255), @databaseID smallint, 
    @recoveryModel varchar(50), @sqlString varchar(max), 
    @spName varchar(255)
    
SET @databaseName = 'MyDatabase'
SET @databaseID = (SELECT db_id(@databaseName))
SET @recoveryModel = CAST(DATABASEPROPERTYEX(@databaseName, 'Recovery') AS varchar(50))

SET @sqlString = 'ALTER DATABASE ' + @databaseName + ' SET RECOVERY SIMPLE'
EXEC (@sqlString)

DECLARE 
    @tableName varchar(100),
    @indexName varchar(100), 
    @objectID int, 
    @fragPercent int, 
    @isBlob varchar(5)

DECLARE TableCursor CURSOR FOR

    SELECT 
        object_Name(ps.object_id) AS 'TableName',
        ix.Name AS 'IndexName', 
        ps.object_id AS 'ObjectID', 
        ps.avg_fragmentation_in_percent, 
        (SELECT CASE WHEN ps.object_id IN 
            (SELECT c.object_id FROM sys.all_columns c 
                INNER JOIN sys.all_objects o ON o.object_id = c.object_id
            WHERE c.system_type_id IN (35, 165, 99, 34, 173) AND 
                o.[name] NOT LIKE 'sys%' AND 
                o.[name] <> 'dtproperties' AND 
                o.[type] = 'U') 
            THEN 'True' ELSE 'False' END) AS 'IsBlob' 
    FROM sys.dm_db_index_physical_stats (@databaseID, null, null, null, 'Limited') ps 
        JOIN sys.indexes ix ON ps.object_id = ix.object_id AND ps.index_id = ix.index_id
        --JOIN sysobjects so ON ps.object_id = so.id
        JOIN sys.dm_db_partition_stats st ON ps.object_id = st.object_id 
    WHERE 
        ps.avg_fragmentation_in_percent > 10 AND 
        ps.index_type_desc <> 'HEAP' AND 
        st.in_row_reserved_page_count > 10 
    ORDER BY st.in_row_reserved_page_count ASC 

OPEN TableCursor 

    FETCH NEXT FROM TableCursor INTO 
    @tableName, 
    @indexName, 
    @objectID, 
    @fragPercent, 
    @isBlob
    
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    
    IF @fragPercent >= 30
    BEGIN
        IF @isBlob = 'True'
        BEGIN
            SET @sqlString = 'ALTER INDEX ' + @indexName + ' ON dbo.' + @tableName 
                + ' REBUILD PARTITION = ALL WITH (FILLFACTOR = 90, ' 
                + 'STATISTICS_NORECOMPUTE = ON, ONLINE = OFF)'
        END
        
        IF @isBlob = 'False'
        BEGIN
            SET @sqlString = 'ALTER INDEX ' + @indexName + ' ON dbo.' + @tableName 
                + ' REBUILD PARTITION = ALL WITH (FILLFACTOR = 90, ' 
                + 'STATISTICS_NORECOMPUTE = ON, ONLINE = ON)'
        END
        
        EXEC (@sqlString)
    END
    
    IF @fragPercent < 30
    BEGIN
        SET @sqlString = 'ALTER INDEX ' + @indexName + ' ON dbo.' 
        + @tableName + ' REORGANIZE WITH (LOB_COMPACTION = ON)'
        EXEC (@sqlString)
        
        SET @sqlString = 'UPDATE STATISTICS ' + @tableName + ' WITH FULLSCAN, ALL'
        EXEC (@sqlString)
    END
    
    FETCH NEXT FROM TableCursor INTO 
    @tableName, 
    @indexName, 
    @objectID, 
    @fragPercent, 
    @isBlob
    
    END 

CLOSE TableCursor 
DEALLOCATE TableCursor

SET @sqlString = 'ALTER DATABASE ' + @databaseName + ' SET RECOVERY ' + @recoveryModel
EXEC (@sqlString)

You’ll need to set the “MyDatabase” name to the name of the database you want to reindex. Everything else is taken care of for you. Here is an itemized list of the logic:

1. Don’t reindex if the fragmentation size is less than 10%.

2. Don’t reindex if the table is classified as a HEAP. They can’t be reidexed anyway…well, not easily or cleanly. :)

3. Reindex if the row_reserved_page_count is greater than 10. If it is smaller then it is too small to care about.

4. Based on books online and a SQL 2000 white paper on redexing, if the index is greater than 30% then reindex it.

5. If it is a BLOB then the index needs done while the table is offline, otherwise leave the table online for availability.

6. If the fragmentation percentage is less than 30% (but greater than 10%…from the WHERE clause in the SELECT) then re-organize it.

7. If re-organizing update the statistics with a FULLSCAN so they are the most accurate. Re-organizing only gets a 25% scan so they stats can be off but the index not too fragmented.

8. Set the recovery model of the database to SIMPLE to mitigate log file growth while the reindexing is done, after it is done set it back to what it was before.

…

Well folks, that’s it. The smartest reindexing script I’ve found on the internet today. I might not be able to say that a year from now.

:)

--Robert