Database Maintenance Strategies for Dynamics AX

AX utilizes SQL Indexes in order to speed up queries to the database. It is important that any index created is analyzed for its usage pattern; for example, will the query only be made once per day, or thousands of times in a loop? It might not be worth it to create an index for a query which is only executed once per day. The reason for this is that anytime an INSERT, UPDATE or DELETE statement is executed, any affected indexes must also be updated.

A side effect from a lot of INSERT, UDPATE or DELETE statements is that over time that index can become fragmented. Fragmentation occurs when SQL can no longer fit data into the free space allocated for an index, or space is removed within the middle of a page of data. It handles this by creating another page for the index to store its data in. Excessive fragmentation can lead to poor performance if a lot of records are being selected as the data is not kept contiguous. This can lead to increased IO and CPU. One workload that is particularly prone to cause heavy fragmentation is the upgrade process. After upgrade, a one-time full reindex should be considered.

Note: Dynamics AX is an OLTP workload which typically is not affected by heavy fragmentation. Queries which return only a single row show little to no performance penalty on fragmented indexes. See the end of this article for a list of tables within AX which are known to be impacted by heavy fragmentation. You can identify tables which will not be affected by fragmentation utilizing the methods in the blog post by Peter Scharlock and Mark Prazak.

You can check to see how heavily fragmented indexes are by utilizing one of the 2 methods:

SELECT s.DATABASE_ID, 
       s.OBJECT_ID, 
       s.INDEX_ID, 
       b.NAME, 
       s.AVG_FRAGMENTATION_IN_PERCENT 
FROM   sys.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS s 
       INNER JOIN sys.INDEXES AS b 
               ON s.OBJECT_ID = b.OBJECT_ID 
                  AND s.INDEX_ID = b.INDEX_ID 
WHERE  s.DATABASE_ID = DB_ID() 
ORDER  BY s.AVG_FRAGMENTATION_IN_PERCENT DESC 

Fragmentation is also covered in the Performance Analyzer for Microsoft Dynamics AX (also known as DynamicsPerf). When running an sp_capturestats, pass the parameter @INDEX_PHYSICAL_STATS =’Y’. Note that this places a heavier load on SQL when capturing, so you should consider executing this during non-peak hours. Once the index fragmentation statistics have been captured you can view them with this script:

SELECT DATABASE_NAME, 
       TABLE_NAME, 
       INDEX_NAME, 
       AVG_FRAGMENTATION_IN_PERCENT, 
       FRAGMENT_COUNT 
FROM   INDEX_STATS_CURR_VW 
ORDER  BY AVG_FRAGMENTATION_IN_PERCENT DESC  

REINDEX 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.

Enterprise Edition Options

If you have Enterprise Edition of SQL, then you can do a REBUILD online by specifying the option WITH(ONLINE = ON). This will allow SQL to create the new index while keeping the old one available without taking a table lock (except for a very brief period when the indexes are changed). All changes to the old index are tracked and copied to the new index, which can cause transaction logfile growth. If this is a concern, then transaction growth should be monitored.

You cannot do an ONLINE REBUILD of clustered indexes which contain LOB (TEXT, NTEXT, IMAGE, VARBINARY, VARCHAR(MAX), NVARCHAR(MAX)) columns in the table. If you attempt to execute an ONLINE REBUILD of one of these indexes, you will receive SQL Error 2725.

In Dynamics AX databases, it is recommended to run with a max_degree_of_parallelism (MAXDOP) option of 1. However, a REBUILD benefits highly from parallelism. Developer and Enterprise Editions support temporarily altering the MAXDOP setting as part of the ALTER command. You can change this by specifying the WITH(MAXDOP = n) option where n is the number of cores in your server up to a maximum of 8.

Maintenance Strategies

REORGANIZE and REBUILD based on fragmentation threshold

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 fromhttp://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 WITH(ONLINE = ON, MAXDOP = 8)'; 

      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 

Note: sys.dm_db_index_physical_stats although a system table, can be resource intensive to query. Consider testing the implications of this before moving into production. There are alternative methods for querying the fragmentation percentages online.

If you are looking for a full solution which automatically handles all of the conditions listed above, I am a fan of Michelle Ufford’s script from sqlfool.com, Index Defrag Script, v4.1. It is very customizable via parameters to the stored procedure and will timeout by default after 12 hours so that you can ensure it does not run into production hours.

Statistics Update Strategies

SQL will keep statistics on indexes on how data is structured within each of those indexes in order to determine the best execution plan. These statistics look at the composition of data to determine such things as whether to seek (utilize the B-tree in the index) or scan (walk through the whole table) among many other execution options.

Within Dynamics AX, these statistics are kept up to date by the 2 options on the database

             auto_create_statistics – When column is specified in a query, statistics on that column are kept.

             auto_update_statistics – When enough changes have been made to the column or index, SQL will update its statistics.

Best practice for the Dynamics AX database is to leave these on which is the default when installing Dynamics AX. However, you may have taken note of the text for auto_update_statistics which states “when enough changes have been made to the column”. What this means is that when a certain percentage of records have been changed, SQL will perform an update on the information that it has about the data. If a table contains a high number of rows, it can take a long time however for these statistics to be updated, which will then cause SQL to pick an execution plan that may not be the best option for the current data composition.

If you have large tables, it is recommended to run a nightly update only for those tables. We have found that transactional tables such as LEDGERTRANS and INVENTTRANS are prone to needing to be updated on a nightly basis. You have the option of turning auto_update_statistics off for those tables on a per-table basis. When doing an update statistics, it is recommended that you test how your data will be sampled. The more data that is sampled, the more accurate the statistics will be. For example, the following command will scan every record for the most accuracy

      UPDATE STATISTICS <table_name> WITH FULLSCAN

Depending on the size of the table, this can take multiple hours to complete, so you may choose to sample a certain percentage of records, at the expense of accuracy.

      UPDATE STATISTICS <table_name> WITH SAMPLE 50 PERCENT 

We can make no recommendations on what is the better option, each one should be tested for the time it takes, and the overall impact on execution plan performance.

SQL ships with a stored procedure called sp_updatestats. You can take advantage of this stored procedure which will automatically update statistics for those indexes and columns which have changed data.

If you are interested in determining fragmentation levels and the last time statistics update was performed for a specific database, you can use the following query on SQL to determine this

SELECT OBJECT_NAME(ps.OBJECT_ID)             AS TableName, 
       si.NAME, 
       ps.AVG_FRAGMENTATION_IN_PERCENT, 
       STATS_DATE(ss.OBJECT_ID, ss.STATS_ID) AS LastUpdatedStatistics 
FROM   sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) 
       ps 
       JOIN SYSINDEXES si 
         ON ps.OBJECT_ID = si.ID 
            AND ps.INDEX_ID = si.INDID 
       LEFT OUTER JOIN sys.STATS ss 
                    ON ss.OBJECT_ID = ps.OBJECT_ID 
                       AND ss.NAME = si.NAME 
WHERE  ps.AVG_FRAGMENTATION_IN_PERCENT > 10 
ORDER  BY ps.AVG_FRAGMENTATION_IN_PERCENT DESC 

New Statistics Calculation

In Service Pack 1 of SQL 2008 R2, a new feature got introduced which changes the algorithm used to determine when to update statistics. The new algorithm uses a sliding scale as the table grows to reduce the percentage of records that must change before an update occurs. The graph which shows the scale looks similar to this:

As you can see, as the table grows, the percentage of records drops and keeps fairly consistent to what you might expect.

If you are running SQL 2008 R2 SP1 or greater, you can enable this feature through Trace Flag 2371. The linked article has more explanation on this change.

Archiving and Purging

Along with fragmentation and statistics, keeping the amount of data in your database to a minimum can help with performance. To assist with this, AX has many cleanup jobs accessible by an Admin from the Periodic->Cleanup section of each module.

If this is not enough, you should consider a long term strategy of archiving the data (moving it to a separate database for retention purposes), or purging unnecessary data. The Intelligent Data Management Framework tool is designed to manage the process of building templates to archive and purge data. A future blog post will discuss the options for setting this tool up.