Источник:
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