1

I have a question on amount of time it takes for an in-memory OLTP SQL Server 2019 database (no Accelerated DB recovery configured) to come online.

I have 2 tables each with 40GB in size (in-memory) part of the database. My question is, does SQL Server have to load the complete 2 tables into memory first before bringing the DB online?

Let's say each table takes 20seconds to load from disk to memory depending on the disk throughput, can we say the DB will not be available in online state for at least 40 seconds (2 *20 seconds for each table) + regular recovery process of analysis,redo and undo phases for DB recovery?

Is my understanding correct? Can I get some pointers for the above concepts please?

Improve this question

2 Answers 2

Reset to default
4

Not only must all memory-optimized data be loaded into memory before any tables are available in the entire database, but because non changes to indexes on memory-optimized tables are logged, all indexes must be recreated on all memory-optimized tables as well.

That's why it's critical to have fast storage, and spread your containers across multiple volumes.

http://nedotter.com/archive/2016/09/in-memory-oltp-the-moving-target-that-is-rto/

http://nedotter.com/archive/2016/02/backup-and-recovery-for-sql-server-databases-that-contain-durable-memory-optimized-data/

Improve this answer
6
  • ADR does not work with memory-optimized data, btw. And if you use FCI, all data will again have to be streamed from storage to memory upon failover. AGs are different - data is kept up-to-date on all secondary replicas for memory-otpimized tables, so it does not have to be (re) streamed upon failover.
    – NedOtter
    Commented Aug 12, 2020 at 13:32
  • 1
    You can just edit your answer if you'd like to add more information.
    – LowlyDBA - John M
    Commented Aug 12, 2020 at 13:46
  • Thnks for response.Hows AGs in-memory DBs are different after automatic failover from primary?.Do you mean to say the complete in-memory table need not be loaded from disk to memory after failover?.I'm trying to understand the above response from above response from NedOtter - AGs are different - data is kept up-to-date on all secondary replicas for memory-otpimized tables, so it does not have to be (re) streamed upon failover.
    – kevin
    Commented Aug 12, 2020 at 14:05
  • Correct, nothing needs to be loaded from disk upon failover for memory-optimized tables that are in a database that participates in an AG. But if you check the blog posts I referenced, there are conditions unrelated to failover where all data must be loaded from storage.
    – NedOtter
    Commented Aug 12, 2020 at 18:56
  • Thanks for great insights NedOtter and LowlyDBA. one last question,If my DATA and Delta files for my memory optimized table resides on 3 containers spread across 3 disks, then in that case, will the time taken for DB recovery be less in this case with each disk throughput of 1GB/sec (assuming) . Does SQL try to load in-memory tables parallely from disk to memory ?. Just wanted to undertstand the concept better?
    – kevin
    Commented Aug 14, 2020 at 3:14
2

All salient information is covered in the documentation under Restore and recovery of memory optimized tables.

Notably,

But unlike disk-based tables, memory-optimized tables must be loaded into memory before the database is available for user access.

and

Loading memory-optimized tables into memory can affect performance of the recovery time objective (RTO).

I would encourage a lot of testing if you are working with this much in-memory data to ensure your recovery process meets your needs.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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