SQL script for rebuild/reindex indexes for Dynamics AX 2012

I often use my blog as a personal archive, and this post is for me to quickly find a script I use at customers that have not set up a proper SQL maintenance plan for indexes and statistics. This script will evaluate each index in the database, and determine if it should be reindexed or rebuilt based on how fragmented they are.

I have very good experience in doing this, and it really increases performance the Dynamics AX 2012 databases.

-- Ensure a USE <databasename> statement has been executed first.
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.
    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)
        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;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

5 thoughts on “SQL script for rebuild/reindex indexes for Dynamics AX 2012

  1. Pingback: Running Dynamics AX R3 on Azure | Brian Kinser

  2. Pingback: Running Dynamics AX R3 on Azure – AX Helper

  3. Pingback: Running Dynamics AX R3 on Azure - Warehouse Management and Distribution - Microsoft Dynamics AX - Microsoft Dynamics Community

  4. Big fan of this blog first and foremost, excellent information about AX.

    I agree that this is an absolutely essential activity, but I would also caution that the script above is very cut and dry. It runs on every index in the database and if it’s 10-30% fragmented, it reorganizes, and if it’s over 30% it rebuilds.

    There are going to be a lot of false positives in this script though, for instance, indexes with very few pages will get reorganized/rebuilt even though it will make absolutely no performance difference, and those operations will write to the transaction log, which means a longer backup time and more space used depending on the size and amount of indexes.

    Another consideration, when you rebuild an index, SQL Server goes ahead and rebuilds the statistics for the columns involved in that index, but it doesn’t do that when you reorganize. Statistics can make a huge performance difference, sometimes even more so than indexes. I should also note, that statistics for columns not part of an index are not updated even when you rebuild an index on a table.

    Another consideration, this rebuilds all indexes offline, if one has a large index on an even larger table (say several million or more), the rebuild is going to take awhile. If one were to include the ‘With Online’ option, the information in that index could still be read/updated while the rebuild is occurring, this could be a big deal in global deployments that don’t have large downtime windows.

    I say these things not to criticize the above script, but rather caution that it isn’t a replacement for a thought out maintenance routine for SQL Server, and if someone is thrust into the role of suddenly being a ‘makeshift’ DBA for Dynamics AX (and thus likely to hit this post), they may want to take a look at the scripts by http://ola.hallengren.com/, they’re extremely vetted, well regarded, and tend to ‘do the right thing’.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.