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.