Всем привет! Меня зовут Николай, я DBA-разработчик SimbirSoft. Если вам приходилось иметь дело с табличной моделью данных SQL Server Analysis Services (SSAS), то вы наверняка знаете, что на сервере она работает только в режиме In-Memory и использует под свои процессы выделенную часть оперативной памяти. А когда памяти недостаточно для нормальной работы, то возникает системное сообщение о ее нехватке с таким содержанием:
Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available'.
В этой статье я кратко рассмотрел причины и общие стратегии по решению данной проблемы, а также рассказал о своем опыте на примере табличной модели объемом около 100 ГБ на MS SQL Server 2019 Ent. Информация может быть интересна разработчикам и администраторам баз данных.
Понимание проблемы
Итак, в одном из моих проектов размер модели на момент появления ошибки составлял около 100 ГБ, из которых основная таблица фактов занимает 90 ГБ. С некоторого времени сообщение о нехватке памяти стало появляться не только при полной обработке модели, но и во время частичной загрузки данных в основную таблицу фактов. В результате такая ошибка сделала невозможным выполнение полного процессинга модели по расписанию, а затраты времени на ее обслуживание стали проблемой и требовали решения.
Возможные решения и рекомендации
В первую очередь мы задействовали SQL Server Profiler. Инструмент мониторинга для анализа производительности запросов и работы модели SSAS ожидаемо добавил много подробностей, но в итоге подтвердил выводы о недостатке памяти. Этот шаг стал основой для анализа и дальнейших способов решения проблемы (Рис. 1). При их описании я не буду претендовать на исчерпывающее изложение материала. В какой-то мере его можно будет найти по ссылкам в тексте. Но мне хотелось бы дать вам возможность увидеть общий контекст проявления проблемы, способов ее преодоления и контуры технического решения, на котором мы в итоге остановились.
Увеличение ресурсов сервера
Первый и, безусловно, самый логичный шаг — это обновление аппаратных ресурсов. Это могло быть самым простым и эффективным решением, особенно для нашего случая, поскольку наш сервер используется только для SSAS. Увеличение объема оперативной памяти могло бы сразу устранить проблему и существенно улучшить производительность.
Самый неудобный вопрос — сколько же ее надо? Тут много оговорок: быстродействие самой памяти, вся база данных в In-Memory, опять же количество подключений, ядер процессоров, рассуждений «избыточность оперативки не увеличивает производительность», «кто админ?», и т.д. по длинному списку. Вероятно, поэтому ждать точный технически выверенный ответ от корпорации Microsoft довольно бесперспективно. Но, как и всегда, спасают эксперты. Они рекомендуют придерживаться не менее 2–3 объемов модели. При этом у них считается, что по сравнению со стоимостью настройки и сопровождения системы квалифицированным разработчиком SSAS память дешевле. А уж по отношению к стоимости лицензий... тут и говорить нечего. Но правда в том, что периодическое расширение физической памяти на сервере может быть единственно возможным выбором. Больше информации по ссылкам (тут и тут).
Настройка конфигурации SSAS
Оптимизация конфигурации SSAS – это важный момент. Необходимо правильно настроить параметры использования памяти в файле msmdsrv.ini. Призываю вас, если есть возможность поиграться с ними, поиграйтесь:
MemoryLimit ограничивает общий объем памяти, используемой SSAS. Желательно установить значение, которое близко доступной памяти на сервере (например, 80-90% от доступной оперативной памяти).
LowMemoryLimit — обычно 70%. На этом пороге SSAS начинает очищать кэш.
HardMemoryLimit — максимальный объем памяти, который может выделить SSAS. Если в работе превышается жесткий лимит, то система агрессивно завершает активные сеансы, чтобы уменьшить использование памяти. Но в этом случае будет не лишним напомнить, что сеансы, прерванные по этой причине, также получат сообщение об ошибке из-за нехватки памяти.
VertiPaqPagingPolicy — управляет подкачкой данных, управляемых механизмом хранения VertiPaq. В SSAS 2019 у этого параметра есть всего два значения 0 и 1. Здесь о нем больше.
Правильная настройка параметров памяти в файле msmdsrv.ini может помочь оптимизировать использование ресурсов. Еще больше подробностей по параметрам можно прочитать у Marco Russo.
Оптимизация моделей данных
Оптимизация структуры данных играет не последнюю роль в решении проблем с памятью. Будет ошибкой не говорить о ней. И способов оптимизации предостаточно. Вот некоторые из них:
Внедрение агрегации и фильтрации данных. Например, использование на источнике предварительно агрегированных данных уменьшает объем информации, загружаемой в модель.
Аудит столбцов и таблиц. Время от времени будет полезно проанализировать, какие данные в виде измерений, атрибутов и за какой период имеют наименьшую ценность для текущих отчетов и возможно ли их исключение из модельного представления. Главный принцип — иметь только то, что действительно необходимо.
Использование подходящего типа данных. Например: Integer в качестве типа для целых чисел вместо String, Date для хранения временных меток, чтобы избежать контекста строковой обработки.
Есть еще радикальный способ разделения больших кубов на несколько маленьких, что встречается довольно редко, но тем не менее он может быть вполне рабочим.
Считаю, что наиболее полно раскрывает эти моменты без преувеличения одно из лучших на сегодня издание The Definitive Guide to DAX.
Разделение загрузки данных. Ручной режим
Способ имеет решающее значение для управления потреблением памяти и предотвращения ошибок, связанных с недостатком ресурсов. У него есть множество вариантов как для ручного, так и для автоматического исполнения. Поэтому он идеально подходит для тех случаев, когда на серверную память бюджет не предусмотрен.
На уровне модели
Запуск процессинга на всю модель целиком (full-processing) приводит сначала к сохранению ее рабочей копии, а уже затем в фоновом режиме происходит обработка этой копии. Оригинал модели при этом доступен для запросов пользователям. По завершении процессинга рабочей копии и появления в ней обновленных данных, оригинальная модель со старыми данными автоматически удаляется из памяти, а обновленная рабочая копия замещает оригинал и становится доступной для запросов. Другими словами, для режима full-processing потребуется столько оперативной памяти, сколько занимает 2 копии модели на самом пике обработки (Рис. 2).
Снизить требования к памяти можно, если full-processing делать поэтапно. Перед обработкой очищаем от данных существующую, доступную для таких операций модель, а уже затем запускаем ее обработку. В этом случае требования к памяти сокращаются до одного объема модели, поскольку второй объем был удален. Этот вариант подходит для нерабочего времени суток, т. к. до окончания обработки запросы пользователей будут невозможны.
На уровне модели также эффективно будет разделение данных по отдельным таблицам или логическим сегментам в зависимости от определенных критериев (например, категория продукции, регионы, группы клиентов) и последующая обработка их по очереди.
На уровне таблиц
Самые объемные таблицы в табулярной модели SSAS — это обычно таблицы фактов (ТФ). Их раздельная обработка выполняется при помощи партиционирования, т.е. логического разделения таблицы на отдельные части-разделы (партиции). Наиболее распространенная техника используют горизонтальное разделение на основе заданного временного интервала значений: месяц (как в нашем проекте), квартал или год.
После создания разделов в ТФ (с использованием SQL Server Management Studio (SSMS) или другого интерфейса) возможна ее раздельная обработка (см. пример 1). Эта тема настолько хорошо представлена в сети, что добавить тут особо и нечего.
Если в модель внесены изменения, а объем данных какой-нибудь таблицы фактов сопоставим с размером выделенной памяти для SSAS, то партиционирование этой таблицы может быть единственным способом, который позволит последовательно обрабатывать разделы один за другим, но все-таки выполнить процессинг всей модели.
В итоге самый заметный плюс, который дает нам партиционирование больших таблиц фактов при недостатке памяти, это возможность вместо рискованного full-processing отправлять в обработку только те разделы, которые должны пополниться свежими данными, либо разделы, выбор которых обусловлен необходимостью поэтапного завершения обработки всей модели. И, конечно, платить за такую красивую возможность придется дополнительным объемом регламентных работ, таких как:
Выбор и реализация способа full-processing на уровне модели.
Создание новых или удаление старых партиций таблиц фактов.
Выбор и назначение партиций для инкрементной обработки.
Выборочная отправка партиций в обработку.
Очевидно, что объем таких работ увеличится кратно, если партиционирование будет создаваться на более коротком таймфрейме (неделя или день).
К общему удовольствию в сети можно найти большое количество примеров автоматизации некоторых работ из списка (см. пример 2, 3, 4, 5), используя стандартные инструменты Microsoft, такие как:
SQL Server Integration Services
SQL Server Agent
TMSL
TOM-модель
Разделение загрузки данных. Автоматический режим
В нашем случае необходимо было сделать так, чтобы в режиме разделения загрузки таблицы фактов (объемом более 90 ГБ) модель SSAS сама бы выполняла все регламентные работы и оставалась максимально независимой от услуг администратора уровня middle. Значит, наша цель — автоматизация всех работ из регламента.
Настройка автоматизации
Для достижения своей цели прежде всего нам необходимо автоматизировать обработку разделов таблицы фактов (ТФ) модели SSAS. Полученный результат не составит большого труда внедрить в обработку (процессинг) всей модели. Поэтому далее описание логики касается только ТФ. Структура таблиц, которая будет приведена ниже, не является образцом для реализации, а их публикация призвана лишь объяснить и показать логику самого решения.
Итак, на платформе MS SQL Server потребуются:
1. Аналитическая модель данных SSAS. В ТФ должен быть создан раздел-шаблон с названием pattern, а в тексте его запроса условие WHERE 1=2. Такое условие обеспечит отсутствие каких-либо записей, а сам раздел послужит основой для создания новых. ТФ может содержать любое количество других разделов и с другими условиями, но иметь их перед началом обработки не обязательно.
2. TabConfig. Конфигурационная таблица. Хранит параметры партиций для новых разделов ТФ, которые будут создаваться в ходе процесса. Одна строка — одна ТФ.
DBServer — сервер, на котором располагается табулярная модель данных.
Tabular_Database_Name — название модели.
Table_name — название ТФ.
PartitionNamePrefix — префикс, который будет добавлен к названию нового раздела.
WhereObject1 — поле из ТФ, по которому будет формироваться условие Where при создании разделов. В случае, если таких условий несколько, разделяющихся операторами AND или OR, то рядом можно создать столбцы WhereObject2, WhereObject3 и т.д.
PartitionsGrain — опорный интервал времени. “М”— месяц.
FromDatePartitions — дата начала партиционирования.
3. [TabExisting]. Необходимо создать таблицу для размещения списка разделов, которые в настоящее время существуют в ТФ. Она будет заполняться в процессе.
4. TabRequired. Основной список изменений для разделов ТФ. Содержит все, что необходимо будет для последующих финальных действий создать/удалить/обработать.
5. Script#1 для SSIS – подключается к модели данных и сканирует разделы ТФ в модели SSAS. От разработчика потребуются начальные знания C#.
6. sp_PartitionsExisting – хранимая процедура. Вызывается из Script#1.
7. sp_PartitionsRequired – хранимая процедура. Формируется TabRequired для финальных действий.
8. Script#2 для SSIS подключается к модели данных, читает TabRequired, производит необходимые действия над разделами ТФ.
9. Службы SQL Server Integration Services для организации процесса, который включает в себя последовательный запуск процедур и скриптов из пп. 5 и 6,7,8.
10. Библиотека TOM (Tabular Object Model) и некоторые другие.
11. SQL Server Agent – для запуска SSIS-пакета по расписанию.
Логика решения
Создается конфигурационная таблица и заполняется необходимыми сведениями: сервер, модель, таблица фактов для обработки, ключевое поле партиционирования, префикс наименования новых разделов, параметры для расчета календарных границ периода процессинга.
Script#1.
Скрипт подключается к серверу и получает доступ к существующей модели и ТФ. Затем для каждого раздела ТФ в цикле выполняется хранимая процедура sp_PartitionsExisting. Параметры для хранимой процедуры берутся из свойств раздела, а результатом ее работы будет строка, которая вставляется в таблицу TabExisting.Подготовка списка требуемых действий.
Работает процедура sp_PartitionsRequired. В основной части скрипта сравниваются существующие и требуемые разделы, формируется финальный набор разделов и инструкций к ним. Результат пишется в таблицу TabRequired. Один раздел – одна строка. Для всех партиций Create и тех Existing, которые участвуют в процессинге, полю Process_Flag присваивается значение Process (кроме случая полного процессинга куба).Script#2.
После подключения к базе данных и Analysis Services находится ТФ и раздел — шаблон для копирования. Далее организуется цикл по каждой строке TabRequired. В зависимости от значения Partition_Action_Flag (Create или Delete) в ТФ выполняются соответствующие действия: создание нового раздела или удаление существующего. Если создается новый, то условие WHERE из шаблона подменяется на значение поля Partition_Filter. При наличии флага Process в строке таблицы, производится обновление (процессинг) данных раздела. После каждого удачного завершения обработки раздела транзакция сохраняется.Запуск последовательности скриптов и процедур происходит по событиям внутри логики обработки всей модели. Эта логика в статье не рассматривается, т.к. после завершения обработки ТФ решающего влияния на потребление памяти иметь не будет.
Опробовано для MS SQL Server 2017, 2019.
Выводы
Проблему нехватки памяти для обработки моделей SSAS можно решить несколькими способами. У каждого из них есть свои плюсы и минусы. Например, увеличение физической памяти на сервере может быть быстрым решением, но не всегда экономически выгодным. Оптимизация модели данных требует времени и экспертизы, но может дать долгосрочные преимущества. Автоматизация процессов требует начальных вложений в разработку скриптов и настройку, но в долгосрочной перспективе окупится за счет снижения операционных затрат.
Комбинируйте подходы. Начните с оптимизации модели и настройки SSAS. Если памяти недостаточно — внедрите партиционирование. По возможности автоматизируйте свои процессы. Адаптируйте решения под свои ресурсы и требования.
Спасибо за внимание!
Больше авторских материалов для backend-разработчиков от моих коллег читайте в соцсетях SimbirSoft — ВКонтакте, Telegram и YouTube.