We've started using OLTP In-Memory tables (schema durability only) in a small DB (DB properties show 137 MB of Row data, 1GB of In-Memory data, BAK size of 152MB) and the recovery times (either restarting the SQL service or restoring the DB from a BAK file) are extremely slow in SQL Standard 2022 on Ubuntu Linux.
On a Windows SQL server VM, it restores and recovers in <90 seconds. On a comparable Linux server VM (same VM and disk infrastructure as the Windows VM), it takes 30-110 minutes (running iostat on the Linux machine shows only normal IO while the CPU is pegged at 100% for the entire time). Using a dacpac (via sqlpackage) for that same DB to create a new DB on the Windows SQL server takes about 20 minutes to complete while the same dacpac (via sqlpackage) on the Linux server takes 12 hours to complete. During the dacpac load on Linux I was seeing each table taking >20 seconds to create and the observed WAITs for each table create were “PREEMPTIVE_OS_CREATEDIRECTORY”. Research on this lead me to Microsoft pages that talk about applying KB4090789 and KB4052338 for SQL 2017 and enabling TraceFlag 9944. I assume those KBs for SQL 2017 would already be included in SQL 2022. I enabled TraceFlag 9944 and restarted the SQL server. The normal (non-OLTP) DBs started up just fine. The OLTP DB failed to start and was marked as SUSPECT. I could not get it to come back online/recover it as long as the 9944 TraceFlag was enabled. I've also tried using 9944 on several Containers running SQL Express 2022 as they all take >10 minutes to recover and they always result in the DB being marked as SUSPECT.
I've gone through the SQL for Linux tuning pages (FileSystem settings, Kernel/CPU tuning, updates/patches, SQL server config, etc) and haven't found anything different than I'm doing.
Any ideas/suggestions would be greatly appreciated. Thanks!
