Hi Kristine,
We’ve had similar experiences with SQL performance. In our case, the issue was largely the result of ‘excessive’ use of change logging… essentially too many fields logged on key tables with standard and UD fields. One revelation our consultant shared is that for a single user level ‘transaction’, we see in SQL both the standard transactions but often additional method and data directives firing so the single user transaction really invokes any number of changes on fields being tracked - in some cases it is a 10:1 or 13:1 ratio of record and field access. Our blocking/locking rate in SQL went through the roof.
We depend on DMT, customizations on Allocations, inventory transactions, receiving transactions, picking, custom QA processes, and packing transactions all touching the part lot level and it was simply a train wreck. Does this have any applicability in your context?
The most interesting aspect is that in testing (at low volume) we saw none of this and frankly were not looking either. It was only at actual production rates of transactions that the train wrecked. Lesson learned on all accounts - change logs cost a lot, especially with emphasis or high dependency on key tables like Part Lot, and there is no substitute for “at expected volume/rate” testing (hard as it is to actually accomplish with busy users).