Schema-Only Optimized Tables Can Still Roll Back

Chris Adkin investigates whether schema-only memory-optimized tables are logged and whether they support transactions the way other tables do:

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.

This post definitely ranks in the “Microsoft did this right” category.

Related Posts

Out Of Memory Conditions And In-Memory OLTP

Ned Otter explains how a memory-optimized database can run out of its most critical resource: What can cause a memory-optimized database to run out of memory? It could be that resource consumption (memory) exceeded: the relevant percentage of committed_target_kb from the sys.dm_os_sys_info DMV (explained in a moment) MAX_MEMORY_PERCENT value of a Resource Pool that the database is […]

Read More

Migrating A Memory-Optimized Database

Michael Bourgon notes that there’s an extra step when migrating a database with a memory-optimized filegroup from one server to another: So, I was trying to get an in-memory database moved from one server to another (long story, involving IMOLTP melting down on me and resulting in a 2 terabyte log file). I thought it’d […]

Read More


July 2017
« Jun Aug »