Анализ роста базы данных MS SQL Server

Бывают ситуации, когда из-за массовой загрузки новых данных или нарушений существующих процедур их очистки база данных начинает аномально расти. Поэтому, а также для грамотного управления её структурой в ходе нормального роста, рост БД важно мониторить и анализировать. В этой статье мы расскажем, как анализировать эту динамику, что делать при аномальном росте и почему важно грамотно подходить к компрессии объектов и сжатия файлов данных.

Прежде всего рост файлов вашей БД обязательно нужно отслеживать. Мы в «ДБ-сервисе» используем собственный мониторинг на основе ПО Zabbix для диагностики таких проблем. Мы готовы предоставить вам широкий спектр услуг по администрированию и поддержке баз данных, включая решение проблем с нормальным ростом БД. В целом стоит использовать следующий подход к динамике БД.

Динамику нужно отслеживать локально (последние 2-7 дней) и глобально (3-12 месяцев). Главное — определить является ли рост файлов данных нормальным или аномальным.

Пример нормально роста базы представлен на скриншоте ниже.

Как видно из скриншота, наблюдаемый рост ступенчатый, но ступеньки не резкие. Хотя за 30 дней демонстрируется существенный прирост, назвать его аномальным нельзя.

Теперь посмотрим на пример аномального роста.

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

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

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

В графическом интерфейсе потенциал роста БД делается в два шага:

2. Смотрим количество файлов данных, их местоположение и свободное место в них.

Как видно из этих скринов, в БД есть недавно добавленный файл и он имеет потенциал для роста.

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

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

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

Вне зависимости от выводов о характере роста БД, сделанных в ходе анализа выше, нужно своевременно принимать меры по сохранению ее работоспособности. В частности необходимо проводить компрессию.

Зачастую рост файлов данных связан с ростом самих данных: в этой ситуации решение об их удалении может принять только их владелец. Поэтому «ДБ-сервис» не удаляет данные без разрешения, а лишь предоставляет своим клиентам информацию о таблицах, которые нужно чистить и возможно, проанализировать БД на наличие неиспользуемых индексов.

Также мы можем проанализировать 5-10 самых крупных таблиц на возможность применения компрессии к ним (в зависимости от версий СУБД она может быть на уровне страниц или на уровне строк, но преимущественно компрессия применяется на уровне страниц в Enterprise издании).

Если версия СУБД 2016 и новее мы обязательно проверяем издание СУБД и его особенности. Например, издание Microsoft SQL Server 2019 Standard Edition не позволяет выполнять операции с индексами онлайн, но стала позволять делать постраничную компрессию, которая ранее была только в Enterprise.

Особое внимание уделяется типам данных в таблицах, к которым планируется применять компрессию, а также количеству строк в них. В случае больших текстовых полей (например [varchar](255) или [varchar](510)) или же большого количества строк в таблице (более 500 млн.) процесс применения компрессии может быть крайне требовательным к месту в журнале транзакций (вплоть до десятикратного размера сжимаемых данных).

Сжатие таких объектов нужно также оценивать с точки зрения возможных рисков. Для этого необходимо рассчитывать эффективность компрессии.

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

Чтобы точно рассчитать компрессию для таблицы мы в «ДБ-сервис» используем специальный скрипт, выполнив который, можно получить необходимую информацию.

На скрине ниже видно, что компрессия не кластерных индексов показывает хорошую эффективность, а вот сами данные (кластерный индекс), почти не сжимаются.

По результатам анализа «ДБ-сервис» может рекомендовать или не рекомендовать клиенту применение компрессии для тех или иных таблиц и индексов.

Например, если расчет показывает что для экономии 50 ГБ в базе размером около 1 ТБ нам потребуется перестраивать 5 и более индексов и это вызовет рост журнала транзакций в 2-3 раза, то мы будем предостерегать клиента от использования компрессии.

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

Как правило свободное место в файлах данных рано или поздно заполнится, кроме того их сжатие дело не быстрое и сильно сказывающееся на производительности (повышается фрагментация индексов при сжатии). Если было высвобождено порядка 10% изначального размера БД и известно, что это место заполнится, к примеру, данными за 2-3 месяца, то сжатие проводить не рекомендуется.

В случае же если было принято решение о сжатии файлов данных нужно учитывать некоторые особенности:

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

Анализ всех этих четырех параметров мы быстро можем осуществить скриптами и процедурами собственной разработки.

Сжатие журнала транзакций задача быстрая, но предварительно нужно убедиться что конечный размер будет достаточным и файл не начнет снова расти (например, из-за неоптимальной политики резервного копирования).

Как уже стало понятно из всего описанного, это задача нетривиальная, и решать её нужно грамотно взвешивая все характеристики конкретной БД. Лучше всего доверить её опытному профессионалу. У нас в «ДБ-сервисе» опыта такой работы накоплено уже очень много, поэтому мы с радостью поможем вашему бизнесу с анализом и исправлением роста базы данных.