question

SambashivaReddyT-0835 avatar image
0 Votes"
SambashivaReddyT-0835 asked Criszhan-msft answered

In-memory optimized SQL instance casuing 100% CPU utilization and incresng dbs recovery time in SQL server 2019

Hi All,

SQL Server 2019 instance which is hosting 15 DBs (in-memory optimized) with a total all in-memory tables size (16 GB) in a server(16CPU,64GB RAM ). CPU is at 100% utilization whenever SQL service or server restarts, while all DBs are in in-recovery status. Also this is causing DB recovery time and never completing the recovery process in some times..

I am aware that there is a bug with SQL server 2014 and 2017 for this issue and trace flag 9944 is available as a workaround. However, the recovery process is taking 3 hours with 100% CPU usage. We do not have an option to see recovery % details in SQL server error log as like on-disk dbs.

Any one of you faced this issue with the 2019 version?. Please let me know if you have a fix\workaround to reduce the recovery time and 100% CPU utilization?
I tried ADR ( Accelerated database recovery) too but no luck for in-memory tables.

In-simple words: 250 in-memory tables with small size (all together approx. 1GB) in one DB
one SQL Instance is hosting 15 DBs (15*1GB approx. = approx. 16 GB total memory data size) in server (configurations 16CPUs and 64GB RAM)

Thanks in advance.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,
I haven't seen any improvements or fixes in the latest CU currently released by SQL Server 2019 about the in-memory OLTP database that takes a long time to recover. However, it is recommended to always install the latest updates for your SQL Server instance.


Restore and recovery of memory-optimized tables

Factors that affect load time
During recovery or restore operations, the In-Memory OLTP engine reads data and delta files for loading into physical memory. The load time is determined by:

The amount of data to load.

Sequential I/O bandwidth.

The degree of parallelism, determined by the number of file containers and processor cores.

The number of log records in the active portion of the log that need to be redone.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.