AX 2009, AX 2012, Development, Dynamics AX, Uncategorized

AX Database Maintenance

AX Databases perform better if regular Index Maintenance is done on SQL server. It is only suggested to run this during non-peak hours. A recurring job to be created to run index maintenance periodically.

I am herewith sharing statistics update strategies I read from a blog.

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

Fragmentation

If you would like to know the fragmentation in indexes, run following query on the dynamics perf (if installed) database to find fragmentation details on the indexes.

 

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

 

Alternatively,  you can run following query on the database to find the fragmentation on the indexes.

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 

I came across a very useful blog and a tool for Database maintenance. Please go through this blog for detailed helpful tool and maintenance. Thanks! to Ola Hallengren

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Download the SQL file by clicking the link below on Index Optimize, added here  as well for quick reference. Latest copy should be taken from the link above. Below link file extension is Key change that to SQL and use it. Please change the database name to your AX database name before run. And I would suggest to run this when you have proper downtime .

IndexOptimize

Examples

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ‘Y’,
@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PartitionLevel = ‘Y’

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 3600

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘AdventureWorks’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘AdventureWorks.Production.Product’

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘ALL_INDEXES, -AdventureWorks.Production.Product’

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = ‘Y’

Advertisements
Standard

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