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. 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
5 thoughts on “SQL script for rebuild/reindex indexes for Dynamics AX 2012”
Pingback: Running Dynamics AX R3 on Azure | Brian Kinser
Pingback: Running Dynamics AX R3 on Azure – AX Helper
Pingback: Running Dynamics AX R3 on Azure - Warehouse Management and Distribution - Microsoft Dynamics AX - Microsoft Dynamics Community
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’.
Thanks for this valuable input, and I fully agree.