MEMORY_OPTIMIZED TEMPDB_METADATA на MSSQL и как с этим жить

Меня зовут Андрей Литвинов, в 1С я уже около 10 лет. Работаю в «Самокате» – пришел на позицию технического эксперта, а сейчас занимаю должность заместителя руководителя архитектурного отдела.

В своей работе я стараюсь быть полезным: помогать компании, коллегам и всем, кому могу. В основном я занимаюсь выстраиванием процессов, прививаю любовь к документации и помогаю реализовывать как свои, так и чужие идеи.

По теме MEMORY_OPTIMIZED TEMPDB_METADATA в MS SQL, особенно в связке с 1С, информации не так много, а то, что есть, в основном сводится к простому совету: «включите – и все будет супер».

Я постараюсь рассказать, как мы боролись с проблемами, когда пробовали внедрить этот механизм, что из этого получилось и к каким выводам пришли.

Описание проблемной системы и начальной диагностики

Описанная ситуация произошла в 2022 году. Речь идет о базе данных на платформе MS SQL Server 2019 с 15-й накопительной сборкой (cumulative update). Почему именно это важно – поясню чуть позже.

В системе работало около тысячи пользователей. Объем базы данных составлял порядка 50 терабайт. Она функционировала на УТ 11.4. Также было настроено 32 файла tempdb – техническая деталь, которая, как выяснилось, имела значение.

От пользователей поступало множество жалоб на медленную работу системы. В ходе анализа производительности мы выяснили, что в топе ожиданий на стороне СУБД доминировали PAGELATCH_SH и PAGELATCH_EX.

Практически все эти ожидания происходили на странице 2:1:128 – это страница системной таблицы sysobjvalues.

Мы начали искать пути решения и наткнулись на, казалось бы, идеальный выход – режим MEMORY_OPTIMIZED TEMPDB_METADATA для tempdb. Об этом режиме часто говорят, что его достаточно просто включить и все будет супер.

Теория: что такое tempdb и memory-optimized metadata

Но все не так просто – под этим решением скрывается множество подводных камней. Чтобы двигаться дальше, давайте немного вспомним теорию.

Назначение базы tempdb:

• Хранение временных таблиц и табличных переменных.

• Хранение рабочих файлов, необходимых для операций хэш-соединения/агрегирования.

• Данные, возвращаемые функциями, возвращающими табличное значение.

• Операции сортировки.

• Используемые версии строк: изоляция моментальных снимков, перестроение индекса в режиме online, триггеры AFTER и множественные результирующие наборы.

Перечисленные операции нас не сильно интересуют, нас интересует только главное: в tempdb хранятся временные таблицы от всех баз, которые находятся на конкретном экземпляре MSSQL. Именно это – ключевой момент для данной статьи.

Также важно понимать, что tempdb можно условно свести к двум типам данных:

• Метаданные, описывающие структуру данных, которые там хранятся,

• Сами данные – содержимое этих таблиц.

В статье речь пойдет о метаданных.

Итак, что такое MEMORY_OPTIMIZED TEMPDB_METADATA? При произношении этих слов каждый раз возникает чувство, будто читаешь заклинание. Решение выглядело как спасительный механизм, потому что с версией SQL Server 2019 Microsoft представила технологию In-Memory, которая позволяет вынести структуры, описывающие метаданные базы tempdb с диска в оперативную память. Это значительно повышает производительность системы: мы меньше обращаемся к диску, ничего на него не скидываем и почти ничего с него не читаем. А таблицы, оптимизированные для памяти, обеспечивают высокую пропускную способность, низкую задержку и ускоряют время отклика.

Кажется, что это идеальное решение, которое должно быть включено по умолчанию – особенно на нагруженных системах, активно использующих временные таблицы. Но, несмотря на все преимущества, этот механизм практически никто не использует.

Разберемся, почему так происходит, и определим, кому на самом деле стоит его включать и в каких случаях.

Когда стоит применять memory-optimized metadata

Если при анализе ожиданий в СУБД вы видите, что в топе находятся PAGELATCH_SH и PAGELATCH_EX на страницах tempdb, особенно тех, чей ID начинается с «2».

Если же у вас другие типы ожиданий или они связаны с другими страницами, то, скорее всего, этот механизм вам не нужен – ваша система и так работает нормально.

Кстати, если вы замечаете ожидания PAGELATCH_UP – это уже совсем другая проблема. Как правило, это указывает на то, что вам не хватает файлов tempdb, нужно провести анализ и увеличить их количество.

Также отмечу, что не надо путать эти ожидания с такими же ожиданиями, которые относятся к дисковой системе – PAGEIOLATCH_SH и PAGEIOLATCH_EX.

Теперь посмотрим, какие именно таблицы переносятся в оперативную память. Я не стал переводить описание на русский – получается какая-то бессмыслица. Оставил оригинал, дополнив таблицу наиболее распространенными страницами, которые обычно с ними связаны. Это помогает быстрее понять, какая системная таблица соответствует той или иной проблемной странице, на которой возникают ожидания.

Включение этого режима позволяет практически полностью устранить ожидания PAGELATCH_SH и PAGELATCH_EX.

Первые попытки включения и возникшие проблемы

Мы подумали: «Супер! Включаем!» И что происходит, как вы думаете? Правильно – падает прод.

При этом мы, конечно, все тестировали. Был выделенный тестовый стенд, на котором изначально включили этот механизм и провели проверку. Но, как это часто бывает, на тесте мы не воспроизводим всех сценариев, которые возникают в продовой среде.

Начали разбираться, в чем дело. Выяснили, что в топе по потреблению памяти находится LOB Page Allocator. Этот потребитель продолжал удерживать память даже при отсутствии активных транзакций и не возвращал ее обратно.

Как выяснилось позже, причина была в баге 15-й накопительной сборки (CU15) для SQL Server 2019. Для устранения проблемы требовалось обновиться до 16-й сборки (CU 16 KB5011644) – после чего все должно было заработать стабильно.

Откуда мы об этом узнали? Есть довольно простой скрипт, который показывает потребление памяти по потребителям (memory consumers). Выглядит это примерно так:

SELECT

    CAST(SUM(xtp.used_bytes)/1024.0/1024.0/1024.0 AS DECIMAL(15,2)) AS [Used (GB)],

    CAST(SUM(xtp.allocated_bytes)/1024.0/1024.0/1024.0 AS DECIMAL(15,2)) AS [Allocated (GB)]

    , memory_consumer_desc

    , OBJECT_NAME(object_id)

FROM

    tempdb.sys.dm_db_xtp_memory_consumers AS xtp

WHERE

    xtp.used_bytes > 10000 AND xtp.allocated_bytes > 10000

GROUP BY

    memory_consumer_desc

    , OBJECT_NAME(object_id)



ORDER BY

    [Used (GB)] DESC

Если этот консьюмер находится в топе (в данном случае на втором месте) и потребляет много памяти, то посмотрите, какие обновления у вас стоят, возможно, нужно обновиться.

Мы посмотрели, накатили обновление на тестовый контур, снова все протестировали, провели стресс-тесты, нагрузили tempdb – вроде все работает отлично.

Катим обновление на прод, снова включаем механизм. Проходит примерно сутки и, как думаете, что происходит? Конечно, прод падает снова. Только теперь причина уже другая.

Материмся, извиняемся перед командой, но все еще упрямо верим, что это решение – потрясающее, лучшее из возможных, и что оно обязательно нас спасет.

Проблема с памятью и необходимость создания пула ресурсов

На этот раз проблема была в том, что переполнился default-пул памяти.

Что с этим делать? Возникла проблема, что при включении этого механизма оптимизированные для памяти таблицы используют память из default-пула. И по умолчанию этот пул работает не так, как он работает, когда мы включаем оптимизированные для памяти таблицы. Об этом расскажу подробнее дальше

Понятно стало одно: нужно срочно что-то менять. Решение нашли простое – создать отдельный пул ресурсов, выделить его под оптимизированные для памяти таблицы и настроить какие-то метрики, потому что мы уже дважды уронили прод, не сильно понимаем почему и начинаем анализировать потом.

Настройка пула ресурсов и мониторинг

--Создаем пул ресурсов

CREATE RESOURCE POOL NamePool  

  WITH  

    ( MIN_MEMORY_PERCENT = 30,  

    MAX_MEMORY_PERCENT = 30 );

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

--Связываем пул ресурсов с оптимизированными для памяти таблицами

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'NamePool');

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

Если в это время другие процессы в других пулах активно потребляют память, то при попытке выделить память для таблиц, оптимизированных для памяти, свободных ресурсов может не оказаться. В результате – выделение памяти не произойдет, и, как следствие, служба SQL Server может упасть.

Именно поэтому Microsoft рекомендует всегда указывать в параметре MIN_MEMORY_PERCENT то значение, которое вам необходимо зарезервировать для оптимизированных для памяти таблиц, чтобы эта память всегда была доступна. Здесь надо исходить из того, сколько у вас оперативной памяти, как работает ваша система, потому что не всегда оптимизированными для памяти таблицами эта память будет потребляться. Могут пройти часы, а эта память не будет потребляться и будет просто простаивать.

После создания пула ресурсов его надо связать с оптимизированными для памяти таблицами. Рекомендую выполнять все настройки за один раз – ведь после последней команды потребуется перезагрузка СУБД, а делать это дважды не хотелось бы.

Также важно организовать мониторинг. Есть простой скрипт, который показывает состояние всех пулов ресурсов. Выглядит это примерно так:

-- Информация по используемым пулам ресурсов

SELECT pool_id

     , Name

     , statistics_start_time

     , total_cpu_active_ms/1000.0 AS [Total CPU Active (S)]

     , total_cpu_usage_ms/1000.0 AS [Total CPU Usage (S)]

     , CAST(cache_memory_kb/1024.0/1024.0 AS DECIMAL(15,2)) AS [Cache Memory (GB)]

     , CAST(used_memgrant_kb/1024.0/1024.0 AS DECIMAL(15,2)) AS [Used Memgrant (GB)]

     , CAST(used_memory_kb/1024.0/1024.0 AS DECIMAL(15,2)) AS [Used memory (GB)]

     , CAST(max_memory_kb/1024.0/1024.0 AS DECIMAL(15,2)) AS [Max memory (GB)]    

     , CAST(target_memory_kb/1024.0/1024.0 AS DECIMAL(15,2)) AS [Target memory (GB)]

     , min_memory_percent

     , max_memory_percent

FROM sys.dm_resource_governor_resource_pools

На нем можно увидеть стандартные пулы – internal и default, а также наш новый пул, созданный специально для оптимизированных для памяти таблиц. Мы выделили ему 30% памяти. Создали, связали – вроде бы все готово.

Но теперь нужно за этим всем следить. В основном для мониторинга мы используем Grafana. Метрики собираем через Windows Exporter и Prometheus.

В Grafana есть метрика windows_mssql_databases_xtp_memory_used_bytes, которая, казалось бы, должна показывать используемую память. Но не все так просто.

В метрике написано, что она возвращает использованную память, но на самом деле это не так – под капотом эта метрика возвращает выделенную память, а не используемую. Это важно понимать, потому что иногда мы можем смотреть на график, видеть цифры, но эти цифры показывают нам не то, что мы хотели.

Также важно помнить: пул памяти никогда не заполняется на 100%. Microsoft указывает, что порог критической нагрузки – около 80%. В ходе наших тестов мы выяснили, что этот предел колеблется в районе 78–82% в зависимости от нагрузки и версии СУБД.

Пулы работают по-разному – и мы уже почти подобрались к сути этих различий.

Повторное падение и особенности работы пулов памяти

Мы настроили алерт на 60–70%. Думали, как только память начнет приближаться к критическому уровню – успеем отреагировать и что-то предпринять.

Как думаете, помогло это? Конечно, нет. Прод снова упал.

Мы проработали в таком режиме, наверное, дня два – в целом все было стабильно, но внезапно – снова падение. Начинаем разбираться, в чем причина.

Выяснили, что снова упала служба, но из-за переполнения не default-пула, а нового пула, который мы создали.

И тут мы обнаруживаем важную деталь: пулы памяти работают по-разному.

В стандартном default-пуле, когда память заканчивается, запросы не падают – они просто встают в очередь, появляются ожидания на выделение памяти, система тормозит. Но это управляемое состояние: как только нагрузка спадет или проблема будет устранена, система восстанавливается и продолжает работать.

А вот с пулами, которые мы создали самостоятельно, все иначе. Особенно это относится к оптимизированным для памяти таблицам. Как только использование памяти в таком пуле достигает примерно 80%, SQL падает с ошибкой. При этом в официальной документации Microsoft утверждается, что при нехватке памяти в пуле запросы просто начнут выполняться с ошибкой, но никакого падения не произойдет.

Однако на практике это не так. Или, по крайней мере, это не работает так в связке с 1С. Каждый раз, без исключения, при переполнении пула падает служба.

Настройка ручного сборщика мусора

С этим мы тоже попытались что-то сделать. Провели анализ, разобрались в ситуации и пришли к выводу: нужно настроить ручной сборщик мусора.

Когда мы включаем механизм оптимизированных для памяти таблиц tempdb, на СУБД каждые пять минут запускается автоматический сборщик мусора, который подчищает выделенную, но не использованную память. Например, память под временную таблицу была выделена, запрос завершился, но сама память осталась занятой. Сборщик как раз и должен ее почистить.

Однако у этого механизма есть одна особенность. Перед тем как начать очистку, он проверяет время самой старой активной транзакции в tempdb. При следующем запуске прежде чем начать очистку, он смотрит, изменилось ли время самой старой транзакции. Если нет – сборщик просто не запускается, даже если за прошедшие 5 минут освободилось много памяти. Так как у нас в системе далеко не все операции заканчиваются за пять минут, пришлось искать из этого какой-то выход.

Мы решили настроить ручной запуск сборщика мусора, который будем запускать раз в 10 минут. Все параметры и частоту запуска подбирали экспериментально, исходя из нагрузки.

-- Скрипт доступен только с SQL 2019 CU15

-- С SQL 2022 дублировать не нужно

/* Yes, 2 times for both*/

Exec sys.sp_xtp_force_gc 'tempdb'

GO

Exec sys.sp_xtp_force_gc 'tempdb'

GO

Exec sys.sp_xtp_force_gc

GO

Exec sys.sp_xtp_force_gc

Есть вот такой скрипт. В нем нет ошибки, он так написан. Он работает только с SQL 2019 CU15. Да, в нем задублирован код, и приписка на английском оставлена от Microsoft, что его действительно нужно запускать именно так. Когда я впервые его увидел, подумал, что это какая-то глупость и в коде просто ошиблись. Попробовал убрать дубли – и скрипт стал работать нестабильно: то срабатывал, то нет.

К счастью, начиная с SQL Server 2022 (CU22), эту проблему исправили, и теперь дублирование больше не требуется – все работает корректно и без него.

Кроме настройки сборщика мусора, нам периодически бывает важно знать, сколько памяти выделено под конкретный запрос. Потому что, если мы приближаемся к пику, и вся память заканчивается, то даже после запуска сборщика мусора память могла не освободиться, и мы понимали, что вот-вот упадем.

Для этого пригодился простой скрипт, который показывает выделенную память по сессиям на СУБД. Относится и к обычному анализу работы на СУБД. Выглядит он примерно так:

-- Сколько памяти выделено под конкретный запрос

SELECT

session_id,

requested_memory_kb/1024.0/1024.0 AS [Requested Memory (GB)],

granted_memory_kb/1024.0/1024.0 AS [Granted Memory (GB)],

text

FROM

sys.dm_exec_query_memory_grants

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

ORDER BY

[Granted Memory (GB)] DESC

Он будет возвращать вам информацию о сессии, сам запрос и сколько памяти под это выделено.

Финальное падение и невозможность контроля

И вот здесь мы подумали, что все – победили. Настроили все: метрики, пулы ресурсов, настроили ручной сборщик мусора – вроде бы все работает идеально.

Как вы думаете, что было дальше? Конечно же – снова упал прод.

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

На тот момент под пул памяти для оптимизированных таблиц было выделено порядка 400 гигабайт оперативной памяти – довольно солидный запас. Казалось, этого с хватит с запасом. Но вся эта память была исчерпана… за 40 секунд.

Да, в системе есть тяжелые отчеты, мощные обработки, много данных – и за всем этим просто не уследишь.

Возникает вопрос: что с этим делать?

Первая мысль – увеличить количество оперативной памяти для оптимизированных для памяти таблиц. Но на сколько? Добавить еще терабайт-два? Звучит нереально.

Может быть, запускать сборщик мусора чаще? Но дело, кажется, не в этом – проблема не в том, что мы не успели освободить память, а в том, что была какая-то одна транзакция, которая съела слишком много.

И здесь мы возвращаемся к ключевому моменту, о котором уже говорили: как только пул памяти, выделенный для In-Memory-таблиц, исчерпывается – служба SQL Server падает. Всегда. Абсолютно без исключений.

Падение произошло за 40 секунд, мы были готовы ко всему – и все равно ничего не смогли сделать. Это был момент полного отчаяния, депрессии и, в конце концов, принятия.

Мы поняли: этот механизм мы использовать не можем, потому что не можем его контролировать. Как бы мы ни старались, как бы ни настраивали алерты и сборщики – когда память заканчивается меньше, чем за полминуты, у нас просто нет шансов среагировать.

Итоги и выводы по использованию memory-optimized metadata

Давайте подведем итоги.

• Пул ресурсов default и созданные пулы вручную работают по-разному.

• При переполнении пула ресурсов для оптимизированных для памяти таблиц падает служба SQL. Всегда.

• Использовать можно, но осторожно. Если вы точно контролируете любой код, который выполняется в вашей системе.

Что сделать, чтобы использовать этот механизм?

• Убедиться, что у вас стоят последние кумулятивные обновления (CU).

• Создать отдельный пул ресурсов под оптимизированные для памяти таблицы.

• Выдать ему максимально возможное количество памяти.

• Настроить ручной сборщик мусора.

• Настроить метрики и алерты.

• Очень внимательно следить за новым кодом, отчетами, обработками, потому что если кто-нибудь запустил какой-нибудь отчет, где не было каких-то фильтров, или он просто не выбрал период или другие параметры, или взял слишком много данных – все упадет.

• Минимизировать длительные транзакции.

• Не допускать очень больших временных таблиц.

Но даже все это не гарантирует вам стабильной работы системы.

Альтернативное решение проблемы

Как же мы все-таки решили эту проблему? Пользователи продолжали жаловаться, а мы своими экспериментами только усугубляли ситуацию и постоянно роняли прод. Но когда это все работало, это действительно нам помогало и решало все наши проблемы.

В итоге мы пришли к выводу, что нужно оптимизировать работу с временными таблицами:

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

• Также мы пришли к рекомендации о том, что нужно убирать ручное уничтожение временных таблиц. Хотя это вроде как написано в стандартах и так делать рекомендовано, но tempdb в целом не любит, когда в ней что-то модифицируют. Идеально, если мы что-то взяли, положили и потом прочитали. А под уничтожением там на самом деле скрывается не уничтожение, а обычный TRUNCATE таблиц, то есть ее очистка. Поэтому делать этого не нужно.

• Попутно избавляемся от лишних полей во временных таблицах.

• Стараемся минимизировать модификацию временных таблиц.

На эту работу у нас ушло, наверное, недели две-три. Когда мы убрали все самое объемное, то проблема и ожидания ушли, мы в целом решили нашу проблему.

Этот не трогало нас еще год-полтора, и недавно мы снова стали получать такие проблемы на этой системе. Экспериментировать мы уже не решились, потому что и так все было понятно. Мы снова занялись оптимизацией, снова ушло пару недель работы на поиск неактуального кода, отчетов, обработок и прочего. Так мы решили наши проблемы.

*************

Статья написана по итогам доклада (видео), прочитанного на конференции INFOSTART TECH EVENT.