Locking on the InventSumDelta table – additional tweaks

This is a follow up on the post Locking on the InventSumDelta table.

We have made a couple of more tweaks that seem to have removed the majority of lockings remaining. All though we managed to remove a lot by going through the motions in the above mentioned post the users continuously received deadlock errors when inserting lines in sales order, inventory journals and so on.

This made us look a bit deeper into the statistics and how they are created and used. What we saw was that all though we had proper indexing on the InventSumDelta table it sometimes still took the wrong index. When looking at the index statistics the RecId index was only used for scanning and not really doing anything useful taken into consideration how the table is used. Still the index were used and scanned.

We therefore decided to disable the index hoping that the statistics would come to their senses and pick a more relevant index. It did not.

Instead it took the index we created earlier having ItemId as first column although the delete from statement only used TTSId, DataAreaId and Partition. None of these columns were in the RecId index. That is when we realised the Optimizer had no clue on what to look for. We tried changing the column sequence on the two tables to have Partition and DataAreaId in the beginning, but nothing happened until we disabled the ItemId index too. That had an instant effect and practically removed the deadlock issues immediately. The index statistics showed an instant change from scanning the ItemId index to seeking the TTSId index.

Please do not hesitate if you have any questions, suggestions or comments to the two posts.

12 thoughts on “Locking on the InventSumDelta table – additional tweaks

  1. Thank you for the post, we are having the exact same problem. It’s a little unclear to which indexes you still have active. In AX I see TTSItemDimIdx, TTSDimIdx and AggregationIds. Then the two you added from your first post. Lastly, the RecIdIdx you disabled, was that on the SQL side?

    Can you please supply me with a short list of what indexes you still have up. Also, if any of them still have statistics active. Much appreciated.

    1. Hi Ben,

      Thanks for reading my blog and I am sorry that I have not responded until now.

      Currently we have 4 indexes on the InventSumDelta table.
      AggregationIdx (non-clustered)
      TTSDimIdx (non-clustered)
      TTSItemDimIdx (clustered)
      BRE_TTSIdIdx (non-clustered)

      This is the constellation we have ended up with after spending time trimming according to the use statistics.
      I would highly recommend keeping an eye on these numbers to keep your installation fit.
      All index changes are made from the AX side to make sure they won’t be cleared during synchronizations.

      I have not made a post on statistics yet; but it might be on its way. You can find a lot of queries used to get the relevant data from the statistics on several blogs. You can also contact me on my email (sor@bredana.dk) if you need further input on this.

    1. Hi Jeff,

      Thanks for reading my blog.
      Since I wrote this post I have started up in a new job so I unfortunately do not have access to the customers environment anymore. However, the indexes were not changed unless described in the blog posts. If you have any specific situations I would be happy to have a quick look if you send me some details.

  2. Hey, thanks for your documentation.
    We also disabled the recid, but still get Lockings on Table InventSumDeltaDim, not on InventSumDelta.
    Do you have a recommendation ?
    Thank you so much!

    1. Glad that it does a difference. 🙂
      If you’re still having issues with locks I would continue looking at the statistics for InventSumDeltaDim and see how the indexes are used and probably used wrong. If needed please leave me your email address and we can take it offline. I can’t promise you anything; but I’d be happy to help you out.

  3. I know this thread is old, but still relevant to our environment. I am looking at disabling the RecID index. It is the only unique index on that table. Did you run into issues disabling it? It seems redundant as the kernel should maintain unique RecID’s but still curious about the impact.

    1. The recid index isn’t relevant, but an index with TTSid is much more useful. The object is to make the SQL server able to lock on a relevant set of data and not doing a full table lock. Hope that answers your question.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s