1

We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains.

Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view unioning both together.

Create Table Demo_Cold
(
    [ID] INT NOT NULL PRIMARY KEY,
    [Description] VARCHAR(MAX) NOT NULL
);
GO

Create Table Demo_Hot
(
    [ID] INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
    [Description] VARCHAR(MAX) NOT NULL
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );
GO

CREATE VIEW Demo
AS
Select *
From Demo_Hot
UNION ALL
Select *
From Demo_Cold;
GO

When I attempt to query this (either directly or view the view) everything is fine:

Select * From Demo

But when I try to set the isolation level to snapshot, I get an error instead:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Select * From Demo

Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

We use snapshot isolation in this database at the moment to allow unrelated parallel commits, and fail on concurrent commits on the same data. According to the MS Documentation, Snapshot isolation is supported by IM-OLTP.

I've even tried setting the escalation setting on to allow this:

ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

But it doesn't change the error.

If I set the isolation level to REPEATABLE READ, I get another error:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo

The following transactions must access memory optimized tables and natively compiled modules under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

The same for accessing just the memory-optimised table:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Select * From Demo_Hot

Am I missing a key step required? Ultimately I'd like to be able to continue using Snapshot isolation from our code (to minimise any other changes).

1

If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:

http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/

Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either cause a stack dump or give an error saying it's unsupported.

Though Microsoft has marketed memory-optimized tables as a hot/cold solution, it can't be done in most cases, at least not without tons of manual intervention. There is no way to validate a FK across the traditional/memory-optimized divide. See my post here:

http://nedotter.com/archive/2016/05/in-memory-oltp-relationship-status-its-complicated/

I've written a few posts on memory-optimized tables and isolation levels here:

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-1/

http://nedotter.com/archive/2017/08/all-about-in-memory-isolation-levels-part-2/

Have you proven that the performance issues you are experiencing would be solved by In-Memory OLTP? The use cases are quite narrow.

6
  • Yeah I'll be creating a separate job to transfer between hot and cold as needed, and the intention would be inserts would directly target the hot table. Records are typically immutable once inserted. I haven't proven out the OLTP performance yet, but we've had consultants in suggesting it. I need to be able to read the memory tables in order to queue it up for a performance test. – Obsidian Phoenix Jan 14 at 13:49
  • I created a memory-optimized db, and ran the code you posted. I did not get any errors. – NedOtter Jan 14 at 15:19
  • Interesting... wonder what I'm missing then. I am using a Linux version of 2016 (via docker), but I wouldn't expect that to cause issues. Could be wrong though, might need to test elsewhere to be sure. – Obsidian Phoenix Jan 14 at 16:09
  • I tested on SQL 2017 on Windows 10 Are you sure that In-Memory OLTP is supported on Docker? – NedOtter Jan 14 at 16:10
  • I tell a lie: Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64> – Obsidian Phoenix Jan 14 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.