Those who have studied In-Memory OLTP are aware that in the event of “database restart”, durable memory-optimized data must be streamed from disk to memory. But that’s not the only time data must be streamed, and the complete set of events that cause this is not intuitive. To be clear, if your database had to stream databack to memory, that means all your memory-optimized data was cleared from memory. The amount of time it takes to do this depends on:
the amount of data that must be streamed
the number of indexes that must be rebuilt
the number of containers in the memory-optimized database, and how many volumes they’re spread across
how many indexes must be recreated (SQL 2017 has a much faster index rebuild process, see below)
the number of LOB columns
- BUCKET count being properly configured for HASH indexes
Read on for the list of scenarios that might cause a standalone SQL Server instance to need to stream data from disk into memory to re-hydrate memory-optimized tables and indexes.