Yesterday one of my previous customer with whom I worked on Comprehensive Database Performance Health Check reached out me with a very simple but very interesting question related to Detecting Database Isolation Level MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT.
While doing a google search he encountered one of my previous blogs about SQL SERVER – Check the Isolation Level with DBCC useroptions. He wanted to know if the isolation for his database is set to memory-optimized elevate to snapshot or not. He enabled his database with the snapshot but whenever he ran DBCC useroptions he kept on getting the isolation level as read committed. He was wondering if there is any way he can know if the isolation for a memory-optimized snapshot is set for his database or not.
I think it is a very valid question. For many years I have run the same DBCC command to know what is the isolation level for my database, however, as SQL Server started to add more and more features, there are many features now available outside the traditional commands.
If you want to know the memory optimized elevated to snapshot configuration for your database, you will have to query now sys.databases to know it. Here is a simple query which will list if the database is set for MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT or not.
SELECT name, is_memory_optimized_elevate_to_snapshot_on FROM sys.databases
If you see the result of the column as 1 that means memory optimized snapshot is on otherwise it is off.
SQL Server is evolving and along with that, we need to evolve as well. Here are a few other blog post related to In-Memory OLTP.
- What is Lock Pages In Memory? – Interview Question of the Week #186
- SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth
- SQL SERVER – InMemory Table Creation Error Message: Msg 41337
- SQL SERVER – Script to Identify Memory Used By Each Database
Reference: Pinal Dave (https://blog.sqlauthority.com)
