Restoring of OLTP In-Memory DBs in SQL 2022 on Ubuntu really slow
1
vote
0
answers
39
views
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 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!
Asked by Hugh Clark
(11 rep)
Jul 15, 2024, 09:59 PM