Blocking when inserting into CUSTTRANS table

(0) Share

Report

Posted on by pnwguy

245

This is Dynamics AX 2012 R2, on SQL 2012 SP2.

DynamicsPerf is showing a ton of blocking when inserting into CUSTTRANS table, looks like the lead blocker is blocking other inserts to same table. The blocked process report shows it is due to a key lock on the clustered index key, which is defined with is_unique = 0. I am thinking if the clustered index were to be unique, then the issue would be resolved. Would that be the proper solution here?

Looking at the clustered index definition, the key columns are : [PARTITION], [DATAAREAID], [ACCOUNTNUM], [TRANSDATE], [VOUCHER]. Looking at the data, the only column that could be added to ensure uniqueness is RECID. But looking at the indexes, there is already a nonclustered index on with key column RECID, and INCLUDES the same five columns - PARTITION, DATAAREAID, ACCOUNTNUM, TRANSDATE, VOUCHER.

Looking at sp_blitzindex stats for the table, over the past month and a half, both indexes have equal number of lock escalation attemps (~30K), singleton lookups (~8 mil), seeks (~1.7k). But the clustered index has ~1.5k lookups.

I am wondering then if the clustering key could be swapped to the RECID index instead?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Vilmos Kintera 46,149 on at

Like (0)

Report

Do you have the SQL Trace Flags set which are recommended for Microsoft Dynamics AX to avoid lock escalation? That might help, before you start breaking your standard AX system. Also I would take a look at disk latency bottlenecks first.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi pnwguy,

It is possible to use another cluster index, but that would probably not solve your issue. You have to investigate why there is a lock on the clustered index. Probably this is no big deal. AX needs to lock records for some updates. Are you experiencing performance issues due to this locking? Are your indexes fragmented?

Was this reply helpful? Yes No
pnwguy 245 on at

Like (0)

Report

Hi Vilmos, I have all the recommended trace flags, and disk shouldn't be a bottleneck, we have all SSDs support 2K IOPs/sec. The trace flags we have enabled are below, the only one not enabled is 4139, not sure why it is not.

1117

1118

1224

2371

2861 -- enabled by SolarWinds DB monitoring

3226 -- suppresses backup messages in errorlog

4199

Was this reply helpful? Yes No
pnwguy 245 on at

Like (0)

Report

From dynamicsperf, there is about 30 seconds of blocking on this transaction id. At the time of the blocking, don't know if there was fragmentation or not, but we use Ola's index optimation scripts to do stats update daily, and index maintenance weekly.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi pnwguy,

I assume with flag 4139 you mean 4136? Read about this flag and if it is useful or not on the next blog from my colleagea: kaya-consulting.com/sql-trace-flag-dynamics-ax-need. In fact if you can set AX to use dataarea and partition literals this flag should not be used.

You can easily check the fragmentation during the day. The scripts from Ola are intended to have a quick setup which would ensure you do have a maintenance on indexes, but it is far from perfect in some scenarios. You have to monitor what exactly is happening.

However. Check the provided blog and related information if setting to use literals would help in this case.

Was this reply helpful? Yes No
pnwguy 245 on at

Like (0)

Report
Hi Andre, I did mean 4139. And 4136 was recommended to be disabled by dynamicsperf, as you suggested in your blog. See the snippet from file Analyze_SQL_Configuration.sql.

-- Recommended Trace Flags -- 1117 - Evenly grow database files -- 1224 - Override lock escalation, only enable on large memory systems -- 2371 - SQL 2008 R2 SP1 and later, auto-update statistics occurs more frequently -- 4139 - SQL 2012 SP1 CU1 and later, moves last data point in index_histogram -- 4199 - Enable all optimizer changes implmented since RTM, should almost always have this on -- Informational Trace flags -- 1118 - Eliminate Mixed Extents (can increase performance at expense of disk space) -- 7646 - Trace Flag to reduce contention on Fulltext indexes -- NEVER turn on Trace Flags -- 4136 - Causes SQL Optimizer to use Density Vector instead of Histogram

On side note, fragmentation is minimal on the CUSTTRANS table, all indexes, including clustered index, has less than 5% fragmentation.

Regarding literals, we have a single company only. I am pretty sure I enabled it anyways on the dynamics side.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi pnwguy,

To be able to see what is causing the delay, you can try to use the Trace parser. I have seen delays caused by e.g. wrong cache settings on tables (e.g. entire table cache on a transaction table).

Was this reply helpful? Yes No