In-Memory Engine DURABILITY = SCHEMA_ONLY And Transaction Rollback

I was fortunate enough to be selected to speak at SQL Saturday Dublin, the talk I gave was on leveraging the in-memory engine, the basic flow of the presentation is thus:

• I ask the audience how many people are actually using the in-memory engine, typically I only get a handful of hands raised in response.
• I outline why I think this is the case, the answer is multi faceted and covers the facts that the in-memory engine only just became a version 2 feature with SQL Server 2016, up until SQL Server 2016 SP1 it was an enterprise edition feature and therefore it had a high cost barrier to entry. The final blocker, at least in my mind anyway, is the fact I do not think many people realise that there are some nice easy to implement solutions to be had from the in-memory engine.
• I then show the audience some code and go into how the in memory engine differs from the  legacy engine and why its needed.
• Finally I present a bunch of “Low hanging fruit” style of use cases , followed by questions.

One of the questions I got asked was about whether DURABILITY=SCHEMA_ONLY allows transactions to be rolled back. But, before answering this lets look at how what the in-memory engines does regarding logging and durability:

The key points to note are:

1. There is no write-ahead-logging (WAL)
2. Only logical operations are logged
3. Log records are aggressively batched together
4. Check-pointing behaves in a completely different way to that in the legacy disk engine, more specifically, this is now mines the transaction log in order to determine what to checkpoint. Also, this does not affect any conventional data files (they do not exist in the world of the in-memory engine), instead it writes to “Data files” and delta files in the in-memory file group or container(s)

Why Not Keep The Old Logging Status Quo ?

Under the old regime the tail of the transaction log could become a bottleneck, also if we consider the logging infrastructure for the legacy engine, which the in-memory engine uses:

The naive assumption in the SQL Server community is that high WRITELOG waits are down to slow storage and to a certain degree this answer is correct a lot of the time. Generally speaking you should always aim to put transaction logs on the fastest storage available, however, if the database engine is being hit particularly hard by an OLTP workload, pressure on LOGCACHE_ACCESS and LOGFLUSHQ spin locks can mean that you will see average write log waits that are higher than the average write stall on the transaction log itself. Ergo, if you do away with write-ahead-logging, this reduces the pressure on the LOGCACHE_ACCESS spinlock and if you have fewer and larger log records to write, this reduces the pressure on the LOGFLUCHQ spin lock. Furthermore, because all memory optimised tables are akin to clustered indexes by default and unlike their disk based counterparts, data is not replicated in secondary indexes, this reduces the logging overhead also.

Forgoing Durability, Have You Lost The Plot ?!?

Emphatically not, there are some scenarios when “Taking a walk on the wild side” by setting the durability of a memory optimised table to SCHEMA_ONLY makes sense. Consider staging data as part of an ETL process for example, if your ETL processes are designed and written such that they are re-startable if they fall over, then memory-optimised tables with DURABILITY=SCHEMA_ONLY make excellent staging tables. In fact memory optimized tables with DURABILITY=SCHEMA_ONLY provide the fastest means by far of staging data into SQL Server. Following on from this it is not hard to imagine that memory optimised tables with no logging make excellant substitutes for temp tables or table variables.

Back To The Question At Hand . . .

To illustrate the answer I will create three tables and populate each one with 50,000 rows:

CREATE TABLE DiskRowStoreEngine (
SomeColumn char(256) DEFAULT 'X'
,Id         int       NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_DiskRowStoreEngine PRIMARY KEY(Id))
GO
INSERT INTO DiskRowStoreEngine DEFAULT VALUES
GO 50000
CREATE TABLE ImMemoryNonDurable (
SomeColumn char(256) DEFAULT 'X'
,Id         int       NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_ImMemoryNonDurable PRIMARY KEY NONCLUSTERED (Id) )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO
INSERT INTO ImMemoryNonDurable DEFAULT VALUES
GO 50000
CREATE TABLE ImMemoryDurable (
SomeColumn char(256) DEFAULT 'X'
,Id         int       NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_ImMemoryDurable PRIMARY KEY NONCLUSTERED (Id) )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
INSERT INTO ImMemoryDurable DEFAULT VALUES
GO 50000

Using fn_dblog we can see what has been logged for the INSERTs into the conventional disk based table:

And now something very similar for the memory-optimised table with full durability:

And finally the memory optimised table with no durability:

Takeaways

The statement “There is zero logging when DURABILITY=SCHEMA_ONLY” is not factually correct, its more like a minimally logged operation. What is surprising is the fact that logged as advertised for the in-memory engine should result in far fewer log records than the equivalent workload for the legacy engine, clearly this is not the case in this particular example and something I need to dig into somewhat deeper. Also note that the version of SQL Server being used is SQL Server 2016 SP1 CU3, which should be stable. One final point, in order to make sure that fn_dblog and fn_dblog_xtp produced clean results for me each time, I took the quick and dirty option of re-creating my test database each time.

What Happens If We Re-Start The Instance ?

As expected if we re-start the instance we get 50,000 rows back for both the disk based table and the memory optimised table with full durability, but zero rows for the memory optimised table with durability set to SCHEMA_ONLY.

What About DURABILITY=SCHEMA_ONLY and Transaction Rollback ?

In theory this should make no difference as the in memory row version store provides the means to roll transactions back, however lets test this:

Clearly, if DURABILITY=SCHEMA_ONLY had any impact on the in-memory engines ability to rollback transactions the SELECT statement at the end of the T-SQL excerpt would return 11.