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
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_GETNEXTand also some
WRITELOG. 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.
HASHindizes 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?