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
PWAIT_DIRECTLOGCONSUMER_GETNEXT
and also someWRITELOG
. The storage is flash, if you need it more exact I have to ask the hardware guys. It's just a single container, with currently 11GB space used.HASH
indizes are used, because they are used for equality comparisons, and according to the doc I understand it they are then prefered. Is that not the case?