MS SQL Server. Таблицы оптимизированные для памяти и пропажа места на диске

К нам в поддержку обратился клиент с ошибкой "MAT/PIT export/import encountered a failure for memory optimized table or natively compiled stored procedure with object ID 1873539326 in database ID 5. The error code was 0x80030070." в приложении работающем с MS SQL Server.

Так как в тексте ошибки упомянута таблица оптимизированная для памяти, то моя первая мысль была, что на сервере недостаточно памяти или она глюканула. Поиск описания ошибки по коду 0x80030070 вывел на STG_E_MEDIUMFULL "There is insufficient disk space to complete operation", а потом на ERROR_DISK_FULL 112 (0x70) "There is not enough space on the disk". Действительно, как мне потом написали, у клиента "всё починилось добавлением места". Как связаны таблицы оптимизированные для памяти и недостаток места на диске для завершения операции?

Таблица оптимизированная для памяти имеет два представления: одно представление в оперативной памяти, а другое на жестком диске. Да, да – на диске. Перед использованием оптимизированных для памяти таблиц необходимо сначала создать оптимизированную для памяти файловую группу (MEMORY_OPTIMIZED_DATA), которая основана на FILESTREAM. В ней MS SQL Server создает и хранит "data" и "delta" файлы. Эти файлы создаются парами и называются "checkpoint file pair" (CFP). Оба файла append-only, т.е. в них ведется только добавление данных. Во время DML-операций в data-файл вставляются новые или обновленные строки, а в delta-файл ссылки на удаленные или старые версии строк. Пользователям доступна только последняя версия записи. Специальный сборщик мусора иногда объединяет эти файлы в новые, а старые удалят (аналог autovacuum у PostgreSQL). Все это обеспечивает Hekaton (MS SQL Server In-Memory OLTP) – движок версионной СУБД, оптимизированный для работы в памяти и полностью интегрированный в SQL Server начиная с версии 2014.

У меня на MS SQL Server 2019 при создании тестовой таблицы с параметрами MEMORY_OPTIMIZED = ON и DURABILITY = SCHEMA_ONLY в FILESTREAM-папке оптимизированной для памяти файловой группы создал сразу 17 hkckp-файлов, которые суммарно сожрали на диске 936МБ!

Файлы делятся по типам:

• ROOT – корневые файлы содержащие системные метаданные для оптимизированных для памяти и скомпилированных в собственном коде объектов (1 файл);
• DATA – data-файлы (2 файла);
• DELTA – delta-файлы (2 файла);
• FREE – пустые файлы, доступные для выделения (12 файлов).

Тоже самое происходит, если вместо таблицы создать пользовательский табличный тип оптимизированный для памяти (CREATE TYPE с параметром MEMORY_OPTIMIZED = ON). Размер создаваемых фалов зависят от объема оперативной памяти на сервере: data-файлы - от 16МБ до 1 ГБ, а delta-файлы от 1МБ до 128МБ. Каждая контрольная точка базы данных ничего не пишет в файл с данными или файл журнала транзакций MS SQL Server, но создает новые файлы в папке оптимизированной для памяти файловой группы. У меня вызов CHECKPOINT при пустой тестовой таблице добавил новый ROOT-файл размером 16МБ, а старый ROOT-файл сменил состояние с ACTIVE на WAITING FOR LOG TRUNCATION. Состояние WAITING FOR LOG TRUNCATION говорит сборщику мусора, что этот файл готов освободить занимаемое место.

Ниже приведены три таблицы, которые получены с помощью процедуры sp_spaceused. Они иллюстрируют, как менялось используемое и зарезервированное базой данных дисковое пространство: верхняя – новая пустая база данных, средняя – база данных после добавления таблицы оптимизированной для памяти, а нижняя – база данных после создания контрольной точки.

Обратите внимание на колонку xtp_pending_truncation – это суммарный размер файлов контрольных точек с состоянием WAITING FOR LOG TRUNCATION.

Использование оптимизированных для памяти таблиц и табличных типов – это отличная замена традиционных временных таблиц, табличных переменных и параметров табличного типа (TVP). Они избавляют нас от постоянной записи на жесткий диск в tempdb и файл журнала транзакций, что дает существенный прирост производительности. Но в этой бочке меда есть и капля дегтя. Оптимизированные для памяти таблицы и табличные типы увеличивают расход места на диске.