in-memory DB startup time SQL Server 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?
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/