Uncategorized X++

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;

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

5 comments 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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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.

%d bloggers like this: