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.

SQL SERVER - Detecting Database Isolation Level MEMORY_OPTIMIZED_ ELEVATE_TO_SNAPSHOT isolationlevel-800x131

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version