RE-INDEX versus REORGANIZE

In order to reduce fragmentation on indexes, you have two options. You can REORGANIZE an index, or you can REBUILD the index.

When you REORGANIZE an index, the leaf level will be reorganized, and the index is kept online during this operation. The advantage to this is that table locks are not held, and queries can continue as normal.

When you REBUILD an index the default is to take the index offline. What this means is that a table lock is held, and all reads and writes that utilize the index are blocked until the REBUILD is complete. This can cause blocking and poor performance of queries.

Dynamics AX Database Maintenance Strategies

The recommended strategy is to REORGANIZE indexes below a certain fragmentation threshold, and REBUILD those above that same threshold on a daily basis. See the script at the end for how to accomplish this. The fragmentation threshold that you choose can vary the amount of time that your maintenance job takes to complete, and should be monitored to make sure that it is not running into business hours. 

Microsoft recommends not performing any operation on indexes that are fragmented less than 10%. For those indexes which are between 10 and 30 percent fragmentation, a REORGANIZE should be used. Above 30% fragmentation, the cost of an ONLINE REBUILD is actually less than that of a REORGANIZE.

Index Maintenance Script

Using the option presented above, the following script which is adapted from http://msdn.microsoft.com/en-us/library/ms188917.aspx in Examples Section D will REORGANIZE lowly fragmented indexes and REBUILD heavily fragmented ones. It is only suggested to run this during non-peak hours. This script assumes that you are running Developer or Enterprise Edition of SQL.

-- Ensure a USE  statement has been executed first.  
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
  
-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
  
-- Open the cursor.  
OPEN partitions;  
  
-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  
  
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  
  
-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  
  
-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO