MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: How to Check It Is On

MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT is a database option, so you read it from sys.databases and not from DBCC USEROPTIONS. That is the answer to a common surprise. You switch the option on, run the DBCC command, and still see read committed.

Why DBCC USEROPTIONS Shows Read Committed

DBCC USEROPTIONS reports the settings of your session. The isolation level on that list is the level your session uses. The elevate option does not change your session. It tells SQL Server how to treat memory-optimized tables in that database. The rule applies when a transaction reads them at a lower level.

So the session keeps saying read committed, and the database keeps the setting. The two answers sit in different places. The first script builds a small database with a memory-optimized filegroup, so that you can see both. The path comes from the instance default, so the script runs on a default install.

USE master;
GO
IF DB_ID(N'ElevateSnapshotDemo') IS NULL
BEGIN
    DECLARE @path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
    DECLARE @sql nvarchar(max) =
        N'CREATE DATABASE ElevateSnapshotDemo ON PRIMARY (NAME = ElevateSnapshotDemo_data, FILENAME = N''' + @path + N'ElevateSnapshotDemo.mdf''), '
      + N'FILEGROUP MemData CONTAINS MEMORY_OPTIMIZED_DATA (NAME = ElevateSnapshotDemo_mod, FILENAME = N''' + @path + N'ElevateSnapshotDemo_mod'') '
      + N'LOG ON (NAME = ElevateSnapshotDemo_log, FILENAME = N''' + @path + N'ElevateSnapshotDemo_log.ldf'');';
    EXEC (@sql);
END;

Read the Option From sys.databases

The column is_memory_optimized_elevate_to_snapshot_on in sys.databases holds the setting. A 1 means the option is on, and a 0 means it is off.

SELECT name, is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
WHERE name = N'ElevateSnapshotDemo';

A new database starts at 0. The next script reads the setting, turns it on and reads it again. One result shows both values.

DECLARE @before bit = CONVERT(bit, DATABASEPROPERTYEX(N'ElevateSnapshotDemo', 'IsMemoryOptimizedElevateToSnapshotEnabled'));
ALTER DATABASE ElevateSnapshotDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
SELECT name, @before AS BeforeAlter, is_memory_optimized_elevate_to_snapshot_on AS AfterAlter
FROM sys.databases
WHERE name = N'ElevateSnapshotDemo';

The function DATABASEPROPERTYEX answers the same question, and it fits better inside a script. The property name is IsMemoryOptimizedElevateToSnapshotEnabled. The test above used it for the before value.

Check What DBCC USEROPTIONS Shows

Now run the DBCC command. It lists the session options, and one row is the isolation level.

DBCC USEROPTIONS;

The isolation level row still says read committed, even though the database option is on. There is no row for the elevate option at all. That is the correct result, not a failure of the option.

For the isolation level of your own session, a view gives a number that is easy to script. The value 2 means read committed. The values 1, 3, 4 and 5 mean read uncommitted, repeatable read, serializable and snapshot.

SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

What a Reading of 1 Changes

Memory-optimized tables run their own transaction engine. Inside an explicit transaction, that engine refuses to read them at the read committed level. A transaction that touches both kinds of table is the usual victim, because two engines meet in it. With MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT on, SQL Server raises the memory-optimized part to snapshot isolation, and the transaction runs.

The setting belongs to one database. It does not change the server, and it does not change your session. You need permission to alter the database to change it. Anyone who can read sys.databases can read it.

Find Every Database That Has the Option

On a server with many databases, ask for all of them at once. The next query lists the databases that have a memory-optimized or FILESTREAM container, with the setting beside each one.

SELECT d.name AS DatabaseName,
       d.is_memory_optimized_elevate_to_snapshot_on AS ElevateOn
FROM sys.databases AS d
WHERE EXISTS (SELECT 1
              FROM sys.master_files AS mf
              WHERE mf.database_id = d.database_id
                AND mf.type_desc = N'FILESTREAM')
ORDER BY d.name;

Read a 1 with care. The ALTER statement also succeeds on a database without a memory-optimized filegroup, and it then sets the column to 1. A 1 tells you the option is on. It does not tell you that the database uses memory-optimized tables.

A Guarded Script for Deployments

In a deployment script, check the value first, and change MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT only when it is off. The script can then run twice without harm. The undo is the same statement with OFF.

IF DATABASEPROPERTYEX(N'ElevateSnapshotDemo', 'IsMemoryOptimizedElevateToSnapshotEnabled') = 0
    ALTER DATABASE ElevateSnapshotDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
ELSE
    PRINT N'The option is already on. To undo it later, run ALTER DATABASE ElevateSnapshotDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF;';

The option exists for one reason. A transaction that reads a memory-optimized table together with a disk-based table fails at the read committed level. The next article covers that failure and the fix: Error 41368: Memory-Optimized Tables and Transactions.

You could argue that a database option is a poor place for this setting. A table hint such as WITH (SNAPSHOT) is more precise. That is true for one query. The option covers every query that touches both kinds of table, so you do not edit each statement.

What to Remember

Read the option from sys.databases or DATABASEPROPERTYEX, and never from DBCC USEROPTIONS. The session level and the database option answer different questions.

Check the value before you change it, and keep the OFF statement as your way back. A 1 shows the option is on, nothing more. Add the column to your server health check, so a database that needs the option never reads 0 by accident. When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'ElevateSnapshotDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ElevateSnapshotDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ElevateSnapshotDemo;
END;

A database option is not a session setting, it is a switch you read from the database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.