I'm working with SQL Server 2016, and working on large group of sprocs that are all called by one parent sproc. All the sprocs do nothing but reads to return one result set. I've confirmed all necessary objects are in cache and no physical reads are happening. All reads are done on indexes with no table scans. This process used to have temp tables all over the place. In an effort to help with speeding up this process, I've replaced all of the temp tables with the in memory optimized tables with schema durability only. After doing that, under load, I'm seeing this get logged by the web api a lot.
Transaction exceeded the maximum number of commit dependencies and the last statement was aborted. Retry the statement. The statement has been terminated.
None of the sprocs use transactions. I've done some googling on it, but I'm still struggling with that message.
As the docs from Microsoft mention, you need to put delete from MemOptimizedTable where your sprocs would have had create #TempTable . I thought I did that, but I ended up missing one spot that was causing collisions. After putting that one extra delete, everything is okay now.