5

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.

Cpu Spikes in ProdCpu Spiked reproduced

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
7
  • Questions: What's the wait type in general, and also specifically when you see the long execution times? What kind of storage is the transaction log on ? How many containers for the in-mem FG? If >1 container, how many separate disks are the containers spread across? Why do you use HASH indexes? It would be great to see the CREATE DATABASE statement.
    – NedOtter
    May 25 '21 at 15:55
  • @NedOtter According to sp_blitzfirst I have a lot of PWAIT_DIRECTLOGCONSUMER_GETNEXT and 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. 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?
    – Juri Robl
    May 25 '21 at 16:15
  • @NedOtter I added the database create statement.
    – Juri Robl
    May 25 '21 at 16:38
  • HASH is fastest for point lookups, but I don't think they are worth the hassle, as they don't grow dynamically, and that can cause issues. It's just one more index to recreate upon db startup. What version/edition/patch level of SQL are you running? Having a single container is a worst-practice, but it's only related to the speed of db recovery. See my post here: nedotter.com/archive/2016/09/… And if using Enterprise, are you using a Resource Pool?
    – NedOtter
    May 25 '21 at 16:58
  • Looking at your CREATE DATABASE script. No size/growth increment is stated for the log file, which I would think is a potential problem. Also, SIMPLE recovery model does not apply to anything in the memory-optimized world, everything is 100% fully logged.
    – NedOtter
    May 25 '21 at 17:09
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.