patternsqlMinor
In-memory OLTP databases take very long to recover during startup
Viewed 0 times
databasesduringtakelongrecovermemoryveryoltpstartup
Problem
we use SQL Server 2019 on Windows with in-memory oltp activated on some databases. After a server reboot/service restart, the in-memory databases take very long to be available (more than an hour) even though most tables are not durable. The size of memory optimized objects is very small: 10 MB
We see a background session on master db with wait type (110514580ms)WAIT_XTP_RECOVERY and almost no reads. CPU cores are at 100%. Disks are idling.
We use transparent data encryption (TDE) for this database. This database uses synonyms to access another db on the same instance. It uses service broker. Instance has transactional replication set-up on databases without in-memory activated.
Adding CPU makes it faster. This is a lowend machine, but not crap. XTP engine 2.11.
Any idea what's going on?
We see a background session on master db with wait type (110514580ms)WAIT_XTP_RECOVERY and almost no reads. CPU cores are at 100%. Disks are idling.
We use transparent data encryption (TDE) for this database. This database uses synonyms to access another db on the same instance. It uses service broker. Instance has transactional replication set-up on databases without in-memory activated.
Adding CPU makes it faster. This is a lowend machine, but not crap. XTP engine 2.11.
Any idea what's going on?
Solution
After a server reboot/service restart, the in-memory databases take very long to be available (more than an hour) even though most tables are not durable.
There are many facets to this outside of durable or non-durable. The durable tables will need to have their data/delta files read back into memory and that will take time. Is it taking all the time? Don't know, we'd need more data.
If you have many in-memory objects (tables, stored procedures) all of those objects regardless of type will need to be recompiled and loaded into memory before the associated (durable) tables can have their indexes loaded into memory. The compilation process shouldn't take a long time individually, but may take a long time together. This isn't including things such as anti* software scanning what you're doing 1500 times and getting in the way.
If you look at your wait stats, is it
Lastly, what are the specs of this server? What do the performance counters show? CPU is high which points me to either many items and/or Anti* software hitting you (or both!). What's CPU at? Do you have an etw trace covering the startup time? What is the VLF and log situation, how much log is needing to be read?
Summarization of comments with responses
What I'm asking myself now is, how we can prevent recompiling the DLLs on startup?
You really don't, that's how it currently work in SQL Server. Compiling code can/is expensive which is what is happening here, only having 4 processors is the bottleneck right now.
SQL Server does implicit checkpoints on shutdown. Is this good enough? Or should we call explicit CHECKPOINTs prior to shutdown?
None of the data given shows this is an issue.
What about KB3147012? I experimented with setting TF 9929 in addition to TF 9944. What is your recommendation on that?
Do you want to have the file start at a low file size? That's what 9929 does, I don't see that as your issue.
There are many facets to this outside of durable or non-durable. The durable tables will need to have their data/delta files read back into memory and that will take time. Is it taking all the time? Don't know, we'd need more data.
If you have many in-memory objects (tables, stored procedures) all of those objects regardless of type will need to be recompiled and loaded into memory before the associated (durable) tables can have their indexes loaded into memory. The compilation process shouldn't take a long time individually, but may take a long time together. This isn't including things such as anti* software scanning what you're doing 1500 times and getting in the way.
If you look at your wait stats, is it
PREMPTIVE_OS_FINDFILE or PREEMPTIVE_OS_CREATEDIRECTORY which would indicate contention with the number of objects being compiled and loaded on the system - for which TF 9944 will disable the creation of debug symbols (PDBs) and output files among other optimizations. This can have a substantial impact on systems with a large number of memory optimized items.Lastly, what are the specs of this server? What do the performance counters show? CPU is high which points me to either many items and/or Anti* software hitting you (or both!). What's CPU at? Do you have an etw trace covering the startup time? What is the VLF and log situation, how much log is needing to be read?
Summarization of comments with responses
- PREEMPTIVE_OS_CREATEDIRECTORY is shown for most of the wait time
- we use four cores of a Xeon Gold 6354 with 96 GB of memory; storage has about 20.000 I/O. vmware.
- TF 9944 helps and we have quick startups now
- Compiling the in-memory objects took 15 minutes instead of two hours now
- the database has 800 objects but only 100 are MEMORY_OPTIMIZED
What I'm asking myself now is, how we can prevent recompiling the DLLs on startup?
You really don't, that's how it currently work in SQL Server. Compiling code can/is expensive which is what is happening here, only having 4 processors is the bottleneck right now.
SQL Server does implicit checkpoints on shutdown. Is this good enough? Or should we call explicit CHECKPOINTs prior to shutdown?
None of the data given shows this is an issue.
What about KB3147012? I experimented with setting TF 9929 in addition to TF 9944. What is your recommendation on that?
Do you want to have the file start at a low file size? That's what 9929 does, I don't see that as your issue.
Context
StackExchange Database Administrators Q#335345, answer score: 9
Revisions (0)
No revisions yet.