DB startup time for IN-memory SQL2019

Question

Hello Team,

I have a question on amount of time it takes for an in-memory OLTP SQL2019 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 the SQL has to load the complete 2 tables in to memory first before bringing the DB online?. Lets say each table takes 20seconds to load the table from disk to memory depending on the disk throughput, can we say the DB will not be available in online state for atleast

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?

Answer 1

HI sqluser786,

1.database is not available until db is online.

2.Regarding '40 seconds (2 *20 seconds for each table) + regular recovery process of analysis,redo and undo phases for DB recovery? Is my understanding correct'

SQL Server Loads in-memory tables and does disk-tables recovery parallelly.

---

https://sqlserverbang.blogspot.com/

Answer 2

Hi sqluser786,

>> does the SQL has to load the complete 2 tables in to memory first before bringing the DB online?

Unlike disk-based tables, memory-optimized tables must be loaded into memory before the database is available for user access. This requirement adds a new step in the database recovery.

>> 40 seconds (2 *20 seconds for each table) + regular recovery process of analysis,redo and undo phases for DB recovery?

As Liweiyin mentioned, SQL Server Loads in-memory tables and does disk-tables recovery parallelly.

Suggest you read MS document Restore and recovery of memory-optimized tables to get detail information.

Best regards,
Cathy 

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 3

Thanks for the response. 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 parallel form disk to memory ? I'm trying to understand how to reduce the time it takes the DB to come online after restart. Just wanted to undertstand the concept here?