One Difference between DBCC DBREINDEX and ALTER INDEX

I was recently discussing with one of my peers the fact that following a periodical reindexing/index rebuild, the overall database performance was better when the old syntax was executed. To put you in the picture, until the SQL Server 2000 version, index fragmentation was handled using the DBCC DBREINDEX and DBCC INDEXDEFRAG commands. Since SQL Server 2005 the new ALTER INDEX syntax was introduced and Microsoft have been notifying all users of the SQL Server Books Online that the old command will be deprecated in a future version; at the same time Microsoft have been recommending that any code containing the old syntax is updated accordingly. Migrating to use the new syntax also involves changes to the code-parts checking which indexes are fragmented. Before SQL Server 2005 this was done by inserting the output of the DBCC SHOWCONTIG command into a temporary table, then executing the DBCC DBREINDEX command for those user object indexes whose fragmentation is more than a specific threshold (e.g. 5% or 10%). A sample of such code is shown below:

-- Do the showcontig of all indexes of all tables
SET @cmd = 'USE [' + @Database + ']; DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS';
INSERT INTO #fragmentationinfo
    EXEC (@cmd);

SET @cmd = 'USE ' + QUOTENAME(@Database, '[') + '; ' +
    'SELECT ''' + @Database + ''', ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
     FROM #fragmentationinfo f JOIN ' + QUOTENAME(@Database, '[') + '..sysobjects so ON f.ObjectId = so.id
     WHERE ScanDensity <= ' + cast(@fillfactor as varchar(3)) + '
     AND ObjectId > 1000 AND LEN(IndexName) > 0
     AND INDEXPROPERTY(ObjectId, IndexName, ''IndexDepth'') > 0';

-- create table cursor
INSERT INTO #fraglist
    EXEC(@cmd);

SET @curIndexes = CURSOR FOR
    SELECT ObjectName, ObjectOwner, ObjectId, QUOTENAME(IndexName, '['), ScanDensity
    FROM #fraglist

OPEN @curIndexes;
FETCH NEXT FROM @curIndexes INTO @ObjectName, @ObjectOwner, @ObjectId, @IndexName, @ScanDensity;
WHILE (@@FETCH_STATUS = 0)  
BEGIN
    SET @Table = QUOTENAME(@Database, '[') + '.' + QUOTENAME(@ObjectOwner, '[') + '.' + QUOTENAME(@ObjectName, '[');
    RAISERROR('Defragmenting index ''%s'' for object ''%s''', -1, -1, @IndexName, @Table);

    --DBCC DBREINDEX(@Table, @IndexName, @fillfactor);
    SET @cmd = N'DBCC DBREINDEX(''' + @Table + ''', ' + @IndexName + ', ' + cast(@fillfactor as nvarchar(3)) + ');';
    EXEC sp_executesql @cmd;

    FETCH NEXT FROM @curIndexes INTO @ObjectName, @ObjectOwner, @ObjectId, @IndexName, @ScanDensity;
END;  
CLOSE @curIndexes;

Code parts similar to the above sample would be executed for each user database. A complete sample script with logic similar to the above can be found with the DBCC INDEXDEFRAG documentation.

In SQL Server 2005 and later versions, identifying which indexes are fragmented is done by querying the sys.dm_db_index_physical_stats DMV.

SELECT * FROM sys.dm_db_index_physical_stats(
    @dbid, @objectid, @indexid, @partitionnumber, 'LIMITED')

In the above example, when the values of the @dbid, @objectid, @indexid and @partitionnumber variables are NULL, index information for all objects in all databases will be returned.

Back to the original question: Why is performance better after running DBCC DBREINDEX contrarily to when executing the new ALTER INDEX syntax?

I found the answer is in the DBCC DBREINDEX documentation, a nine-year old Microsoft Whitepaper and finally after reviewing the code being executed.

• The DBCC REINDEX documentation states that “If index_name is not specified or is specified as ‘ ‘, all indexes for the table are rebuilt”.
• The Microsoft Whitepaper titled “Microsoft SQL Server 2000 Index Defragmentation Best Practices” states that the “DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fillfactor” and “running DBCC DBREINDEX is very similar to using Transact-SQL statements to drop and re-create the indexes manually”.
• The code being executed was running DBCC DBREINDEX without passing the index_name parameter.

That solved the puzzle. Having newly created CLUSTERED and NONCLUSTERED indexes on all tables translates in better performance. The only issue is that features such as the SORT_IN_TEMPDB option were not available pre-2005. This means that if your database is set to the FULL Recovery Model the transaction log for the affected database will grow and the process will either fail or might cause disk space issues, depending if AutoGrow is enabled or not.