SQL Server 2016 Memory Optimised Table: High Disk Usage

If you’ve converted a table in SQL Server to an in memory table with durability turned on, and noticed that the files on disk keep filling your drive up, then it’s time to patch SQL Server
There’s a bug in SQL Server 2016 where it keeps pre-allocating space to use for the durable files, way beyond what it needs. For a 100MB table, we saw this allocating 900MB of disk space an hour, which eventually filled the drive up completely. When this happened, SQL Server would clear all the files down and the process would start again.

Run this SQL to see if your in memory tables are affected by this bug:

USE DATABASE_WITH_IN_MEMORY_TABLE
SELECT state_desc, file_type_desc, COUNT(*) AS [count], SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type

If you see the PRECREATED rows suspiciously large (and getting larger quickly) then update SQL Server:
InMemoryLargePrecreatedFiles

The bug is fixed in SQL Server 2016 SP1 CU2, and 2016 CU4 with no SP:
https://support.microsoft.com/en-us/help/3206584/fix-checkpoint-files-grow-excessively-when-you-insert-data-into-memory

Leave a Reply

Your email address will not be published. Required fields are marked *