axinthefield: Too Much Database Logging in Dynamics AX

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...namics-ax.aspx
==============

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



The results will look as follows:

TABLE_NAME

RatioOfReads

RatioOfWrites

TotalReadOperations

TotalWriteOperations

TotalOperations

REQTRANS

0.4712597612623479424

0.5287402387376520575

1568631

1759960

3328591

SYSDATABASELOG

0.2968271219786954271

0.7031728780213045728

299327

709095

1008422

SALESSHIPPINGSTAT

0.1857588421387402567

0.8142411578612597432

133172

583736

716908

SMMTRANSLOG

0.5236542880462582721

0.4763457119537417278

326566

297063

623629

INVENTSUMLOGTTS

0.2288269604666234607

0.7711730395333765392

79443

267732

347175

LEDGERTRANS

0.4046390537009612028

0.5953609462990387971

171588

252464

424052

INVENTTRANS

0.9204778317203861103

0.0795221682796138896

2215858

191433

2407291

INVENTSUMDELTA

0.2312710628890821230

0.7687289371109178769

53459

177694

231153

NUMBERSEQUENCELIST

0.3341698307533857008

0.6658301692466142991

86955

173257

260212

INTERCOMPANYINVENTDIM

0.2038198652382568229

0.7961801347617431770

42046

164244

206290

In this example, you can see that the 2nd most written too table in the database is SYSDATABASELOG. If this table is in the top 10 written too 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://blogs.msdn.com/b/axinthefield...namics-ax.aspx