Decisions can be hard. There are negligible decisions like paper or plastic but also dicey decisions like figuring out what your wife really wants for dinner when she asks you to decide.
To try speeding up the decision-making process, Amazon has followed the decision framework around two-way doors and one-way doors. The idea is that decisions that are easily reversible are two-way doors and should be made fast because there’s less pain if the initial decision isn’t optimal. For decisions that are not reversible and justify more thought, these are considered one-way doors.
If you didn’t know any better, you may have experienced latch and lock contention issues in SQL Server and decided to turn on In-Memory OLTP in an effort to alleviate the issues.
If you enabled it but decided it wasn’t working out how you expected and wanted to turn it back off, you were in for a rude awakening. Once In-Memory OLTP was turned on, it could not be turned off. You could drop every memory-optimized table, stored procedure, etc., but the In-Memory OLTP engine would continue chugging on in the background.
Unlocking Doors
One helpful change with SQL Server 2025 is that In-Memory OLTP can become a two-way door and can be turned off.
Microsoft lays out the steps in detail here if you’re planning to do this yourself. The general steps are:
- Drop all memory-optimized tables, natively compiled modules, and memory-optimized table types.
- Remove the memory-optimized containers with
ALTER DATABASE ... REMOVE FILE. - Remove the memory-optimized filegroup with
ALTER DATABASE ... REMOVE FILEGROUP.
It sounds simple in theory but is not as simple in practice.
Niko Neugebauer posted about walking through the whole process on a test database and found it to be more painful than it looks on paper. Considering the trouble a simple test database gave, it’s going to take some thorough testing if you’re trying to follow these in a production environment.
Note that if you’re on Azure, you’re still out of luck, as the removal option isn’t available yet for Azure SQL Database or Azure SQL Managed Instance.
Break on Through
It’s a good reminder to double-check which kind of door you’re walking through before you walk through it. In-Memory OLTP looked like a fast, reversible decision for years, and it wasn’t. Now, at least, there’s finally a path to walk back out.
Thanks for reading!
Chad Callihan 