Sfrgttk

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:ZZTempTestA.mdf',size=10) log on (name=TestA_log,filename='D:ZZTempTestA.ldf')

go

select 'backup database', getdate()

backup database [TestA] to disk='D:ZZTempTestA.bak'

go

select 'restore database', getdate()

restore database [TestABackup] from disk='D:ZZTempTestA.bak' with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.ldf'

go

select 'backup log', getdate()

backup log [TestA] to disk='D:ZZTempTestA-001.trn'

go

declare @S datetime

set @S=getdate()

-- this takes less than 1 second

restore log [TestABackup] 

from disk='D:ZZTempTestA-001.trn' 

with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.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:ZZTempTestA_mod1') TO FILEGROUP TestA_mod

go

select 'backup log', getdate()

backup log [TestA] to disk='D:ZZTempTestA-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:ZZTempTestA-002.trn' 

with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.ldf',

move 'TestA_mod1' to 'D:ZZTempTestABackup_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:ZZTempTestA-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:ZZTempTestA-003.trn' 

with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.ldf',

move 'TestA_mod1' to 'D:ZZTempTestABackup_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:ZZTempTestA-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:ZZTempTestA-004.trn' 

with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.ldf',

move 'TestA_mod1' to 'D:ZZTempTestABackup_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:ZZTempTestA-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:ZZTempTestA-005.trn' 

with standby='D:ZZTempTestABackup.DAT',

move 'TestA_dat' to 'D:ZZTempTestABackup.mdf',

move 'TestA_log' to 'D:ZZTempTestA2Backup.ldf',

move 'TestA_mod1' to 'D:ZZTempTestABackup_mod1'

select 'restore log (memoryoptimized after drop table) ms=',datediff(ms,@S,getdate())

go

/*

No way to undo this... Please help.

https://docs.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. 

*/

Not sure it's possible to concur with what you wrote, without knowing how much data was being backed up at various points, as well as the version and edition of SQL Server in use.

I ran the script you posted, and I saw 2x to 3x slower log restore times. When I removed STANDBY from the RESTORE LOG command, performance was greatly improved.

Restoring a log WITH STANDBY is always slower than NORECOVERY, whether In-Memory OLTP is used or not. Sounds like you are perhaps running this scenario for logshipping purposes, not sure. Otherwise, can't think of a reason to have STANDBY on the restore.

If your system is on the busy side, you might consider doing a CHECKPOINT before the log backup, which should speed restore/recovery time.