Brent Ozar takes us through a problem:
Well, here it is in 2023, and recently I’ve talked to a couple of architects who wish they could go back in time and watch that video. In both cases, they suffered from the same issue.
The short story is that the more data you put into durable In-Memory OLTP tables – and even just 5GB of data can hit this issue – the more your startups, failovers, and restores turn into long stories, to the point where other databases on your SQL Server are practically unusable.
Click through for the scenario. In-Memory OLTP is one of those features which frustrates me to no end. It had the potential to be outstanding, but due to the difficulty of further development (e.g., getting cross-database queries to work when you have a mix of memory-optimized and non-optimized databases and tables) and the limitations of what it actually made faster (mostly inserts, not selects), the actual number of great use cases for the product is a lot lower than I think it could have been.