Too Much Database Logging in Dynamics AX

One of the best features in Dynamics AX is database logging.  While this gives you a great audit trail for tracking changes in your system, this can actually lead to a performance problem in your Dynamics AX system. 

There are essentially two major performance issues that come up with database logging.  The first is that it can make the AX kernel ignore all set based operations on that table. All X++ code such as UPDATE_RECORDSET will become a row based operation causing multiple round trips to the database.

Code such as the following that might update 1000 employee records giving everyone a 10% raise in 1 statement would actually cause 1000 trips to the database to update each record if UPDATE logging was enabled on this table

MyTable myTableBuffer;

;

update_recordset myTableBuffer

setting field1 = field1 * 1.10;

The second major issue is the amount of writes that potentially are caused in the database.  The hard part about this is determining when we have too much.  Performance Analyzer for Microsoft Dynamics makes this task fairly easy to identify.  One of the DMVs that we collect data from is sys.dm_db_index_usage_stats.  With this DMV we can determine the amount of writes that occur on a table in the Dynamics AX database.   The following query shows this activity:

SELECT TABLE_NAME,

       CASE

         WHEN ( SUM(USER_UPDATES + USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0 ) THEN NULL

         ELSE ( CAST(SUM(USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) / CAST(SUM(USER_UPDATES + USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) )

       END                                                        AS RatioOfReads,

       CASE

         WHEN ( SUM(USER_UPDATES + USER_SEEKS + USER_SCANS + USER_LOOKUPS) = 0 ) THEN NULL

         ELSE ( CAST(SUM(USER_UPDATES) AS DECIMAL) / CAST(SUM(USER_UPDATES + USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS DECIMAL) )

       END                                                        AS RatioOfWrites,

       SUM(USER_SEEKS + USER_SCANS + USER_LOOKUPS)                AS TotalReadOperations,

       SUM(USER_UPDATES)                                          AS TotalWriteOperations,

       SUM(USER_UPDATES + USER_SEEKS + USER_SCANS + USER_LOOKUPS) AS TotalOperations

FROM   INDEX_STATS_CURR_VW /*sys.dm_db_index_usage_stats*/

GROUP  BY TABLE_NAME

--order by TotalOperations desc

--order by TotalReadOperations desc

ORDER  BY TotalWriteOperations DESC

The results will look as follows:

In this example, you can see that the second most written tool table in the database is SYSDATABASELOG.  If this table is in the top 10 written tool tables in your system, then logging should be reviewed. 

Some good practices are:

• Don’t log transaction tables such as INVENTTRANS
• Log updates to specific columns vs. the entire table
• Log insert/deletes but not updates, tables get updated more often

One table that can cause a lot of logging is the INVENTTABLE if you run BOMCALC many times.  Many customers log all changes to the INVENTTABLE if they enable logging on this table.  What most don’t realize is that there is a column called BOMLEVEL that is updated every time BOMCALC is run.  So, if you have 100, 000 INVENTTABLE records you would get a 100,000 new records in the SYSDATABASELOG table with each run of BOMCALC.

Be cautious when setting up logging in Dynamics AX.  Ensure there is a real business need for the logging that is configured and that the data will actually be reviewed or you may cause a performance issue.

 http://dynamicsperf.codeplex.com.  Downloadable Article