The issue I have is that once you set a database to support in-memory OLTP tables the time to execute a "restore log" increases significantly; about 100 times slower.
I tested, I thought prudently, the use of In-Memory tables but I did not measure the increase in time to perform a "restore log" command.
On one of our production servers we execute "backup log" every 5 minutes, but the "restore log" command takes 6 minutes after turning on In-Memory OLTP; used to take 2 seconds. Yikes...
And as far as I know there is no way to undo a database once you have set it to support in-memory tables.
Has another one else seen this behavior?
the following is a simple script that duplicates the problem.
--use master
--drop database [TestA]
--drop database [TestABackup]
go
use master
select 'create database', getdate()
create database [TestA] on (name=TestA_dat,filename='D:\ZZTemp\TestA.mdf',size=10) log on (name=TestA_log,filename='D:\ZZTemp\TestA.ldf')
go
select 'backup database', getdate()
backup database [TestA] to disk='D:\ZZTemp\TestA.bak'
go
select 'restore database', getdate()
restore database [TestABackup] from disk='D:\ZZTemp\TestA.bak' with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf'
go
select 'backup log', getdate()
backup log [TestA] to disk='D:\ZZTemp\TestA-001.trn'
go
declare @S datetime
set @S=getdate()
-- this takes less than 1 second
restore log [TestABackup]
from disk='D:\ZZTemp\TestA-001.trn'
with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf'
select 'restore log ms=',datediff(ms,@S,getdate())
go
select 'alter database memory optimized', getdate()
alter database [TestA] ADD FILEGROUP TestA_mod CONTAINS MEMORY_OPTIMIZED_DATA
alter database [TestA] ADD FILE (name='TestA_mod1', filename='D:\ZZTemp\TestA_mod1') TO FILEGROUP TestA_mod
go
select 'backup log', getdate()
backup log [TestA] to disk='D:\ZZTemp\TestA-002.trn'
go
-- this now takes 4 seconds (for a larger data this will take minutes instead of seconds)
declare @S datetime
set @S=getdate()
restore log [TestABackup]
from disk='D:\ZZTemp\TestA-002.trn'
with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf',
move 'TestA_mod1' to 'D:\ZZTemp\TestABackup_mod1'
select 'restore log (memoryoptimized) ms=',datediff(ms,@S,getdate())
go
use TestA
select 'create table A memory optimzed', getdate()
create table A (ID [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED) with (MEMORY_OPTIMIZED = ON,DURABILITY = SCHEMA_ONLY)
go
use master
select 'backup log', getdate()
backup log [TestA] to disk='D:\ZZTemp\TestA-003.trn'
go
-- this now takes 5 seconds (for a larger data this will take minutes instead of seconds)
declare @S datetime
set @S=getdate()
restore log [TestABackup]
from disk='D:\ZZTemp\TestA-003.trn'
with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf',
move 'TestA_mod1' to 'D:\ZZTemp\TestABackup_mod1'
select 'restore log (memoryoptimized with table) ms=',datediff(ms,@S,getdate())
go
use TestA
declare @S datetime
set @S=getdate()
checkpoint
select 'checkpoint ms=',datediff(ms,@S,getdate())
go
use master
select 'backup log-start', getdate()
backup log [TestA] to disk='D:\ZZTemp\TestA-004.trn'
go
declare @S datetime
set @S=getdate()
-- this now takes 5 seconds (for a larger data this will take minutes instead of seconds)
restore log [TestABackup]
from disk='D:\ZZTemp\TestA-004.trn'
with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf',
move 'TestA_mod1' to 'D:\ZZTemp\TestABackup_mod1'
select 'restore log (memoryoptimized with table) ms=',datediff(ms,@S,getdate())
go
use TestA
-- drop the in-memory table
select 'drop table A', getdate()
drop table A
go
use master
select 'backup log', getdate()
backup log [TestA] to disk='D:\ZZTemp\TestA-005.trn'
go
declare @S datetime
set @S=getdate()
-- still takes 5 seconds (for a larger data this will take minutes instead of seconds)
restore log [TestABackup]
from disk='D:\ZZTemp\TestA-005.trn'
with standby='D:\ZZTemp\TestABackup.DAT',
move 'TestA_dat' to 'D:\ZZTemp\TestABackup.mdf',
move 'TestA_log' to 'D:\ZZTemp\TestA2Backup.ldf',
move 'TestA_mod1' to 'D:\ZZTemp\TestABackup_mod1'
select 'restore log (memoryoptimized after drop table) ms=',datediff(ms,@S,getdate())
go
/*
No way to undo this... Please help.
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup
Once you create a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.
*/
