Memory Optimized intermittently very slow perfomance

Question

I have a memory optimized table with (in my test) 10 million rows. Running some UPDATE in parallel against it, I get intermittent spikes in CPU and huge spikes in execution time (>1s from 1ms) of queries. The CPU spike is caused by SQLServer. During those spikes, all queries are slow, even ones not using the same table or GETUTCDATE.

It's running on a SQL Server 2019 CU10, 8 Cores and 100GB RAM. I also let it run on a barebopes Server (24 Cores), a fresh VM and on my laptop, with more or less the same results. The table has 10 million rows in my test system, 40 mio in prod. There are four NONCLUSTERED indizes, and two NONCLUSTERED HASH, which don't have long chains and have a lot of unfilled buckets.

Prod uses many InMemory Tables, Foreign keys and Natively compiled procedures, so I'm having some problems reducing the problem while keeping the exact same characteristics. In my reproduction of the issue I'm running 10 threads in C# in parallel with a wait of 1-5ms between each update:

UPDATE ContainerAutoTest SET LastComment = 'TestEntry' WHERE Name = @TestContainer

Each thread has it's own row to update, so there shouldn't be any conflicts on the row level. It doesn't matter if I run the queries from the same machine or over network.

In prod we are getting spikes about every 30s while we are writing data, in my tests I can reproduce spikes at about 20s or even every 2s, depending on the machine, the amount of threads I'm running and the wait between the updates.

I already rebuild a completely new table, where I can reproduce the problem:

CREATE TABLE [dbo].[ContainerAutoTest]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ResourceId] [int] NULL,
    [CurrentStepId] [int] NULL,
    [ProductId] [int] NULL,
    [Level] [nvarchar](50) NULL,
    [LastComment] [nvarchar](100) NULL,
    [SysStart] [datetime2](7) NULL,
    [Name] NVARCHAR(100) NOT NULL,
INDEX [Ix_CurrentStep] NONCLUSTERED([CurrentStepId] ASC),
INDEX [Ix_Level] NONCLUSTERED([Level] ASC),
INDEX [Ix_Product] NONCLUSTERED([ProductId] ASC),
INDEX [Ix_Resource] NONCLUSTERED([ResourceId] ASC),
PRIMARY KEY NONCLUSTERED HASH([Id]) WITH ( BUCKET_COUNT = 16777216),
UNIQUE NONCLUSTERED HASH([Name]) WITH ( BUCKET_COUNT = 16777216)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO
ALTER TABLE [dbo].[ContainerAutoTest] ADD  DEFAULT ('Modul') FOR [Level]
GO
ALTER TABLE [dbo].[ContainerAutoTest] ADD  DEFAULT (SYSUTCDATETIME()) FOR [SysStart]
GO

PRINT('Clearing table...')
DELETE FROM [dbo].[ContainerAutoTest] WHERE 1=1
GO
PRINT('Filling table...')
DECLARE @RowsToInsert INT = 10;
DECLARE @RowsInserted INT = 0;
WHILE @RowsInserted < @RowsToInsert
BEGIN
    INSERT INTO ContainerAutoTest (Name)
        SELECT TOP 1000000 NEWID()
            FROM sys.all_columns ac1
                CROSS JOIN sys.all_columns ac2;
    SET @RowsInserted = @RowsInserted + 1;
END
PRINT('Filled table')
GO

I have some problems reproducing the exact problem with SQL only, so I'm currently running the tests using C#.

The database is created mostly using defaults, here is the exported CREATE, I tried to remove most off the defaults that shouldn't affect the problem.

CREATE DATABASE [TestDbSlowInMemory]
    ON  PRIMARY
    ( NAME = N'TestDbSlowInMemory', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDbSlowInMemory'  ),
    FILEGROUP [TestDbSlowInMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
    ( NAME = N'TestDbSlowInMemoryInMemory', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDbSlowInMemoryInMemory' )
    LOG ON
    ( NAME = N'TestDbSlowInMemory_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDbSlowInMemory_log'  )

ALTER DATABASE [TestDbSlowInMemory] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [TestDbSlowInMemory] SET AUTO_UPDATE_STATISTICS_ASYNC ON 
GO
ALTER DATABASE [TestDbSlowInMemory] SET ALLOW_SNAPSHOT_ISOLATION ON 
GO
ALTER DATABASE [TestDbSlowInMemory] SET READ_COMMITTED_SNAPSHOT ON 
GO
ALTER DATABASE [TestDbSlowInMemory] SET MULTI_USER 
GO
ALTER DATABASE [TestDbSlowInMemory] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [TestDbSlowInMemory] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [TestDbSlowInMemory] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [TestDbSlowInMemory] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [TestDbSlowInMemory] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [TestDbSlowInMemory] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [TestDbSlowInMemory] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [TestDbSlowInMemory] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
ALTER DATABASE [TestDbSlowInMemory] SET QUERY_STORE = ON
GO
ALTER DATABASE [TestDbSlowInMemory] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = ALL, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [TestDbSlowInMemory]
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;
GO
ALTER DATABASE [TestDbSlowInMemory] SET READ_WRITE 
GO

Answer 1

I'd need to know more, but is it possible that these spikes are garbage collection? The server needs to discard no-longer-active row versions. We do lots of large updates (10K updates, 20K rows per update, per minute) on some big tables and we'll get occasional CPU spikes that we are attributing to GC.