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