Обслуживание индексов и статистик MS SQL Server
Эта статья написана для администраторов, обслуживающих сервера СУБД MS SQL Server, которые используются вместе с 1С:Предприятием. Статья скорее практическая, чем разъяснительная, но я постарался хотя бы кратко обосновать те или иные решения, хотя большая часть информации дана несколько упрощенно и поверхностно.
Индексы и статистики в MS SQL Server — основа эффективного выполнения запросов. Без них сервер не сможет выполнять запросы за разумное время.
Статистика — небольшая таблица, до 200 строк, в которой хранится обобщенная информация о том, какие значения и как часто встречаются в таблице. На основании статистики сервер принимает решение, какой индекс использовать при выполнении запроса.
Индекс — особым образом структурированные данные (хранящиеся в базе данных), которые позволяют быстро найти нужные записи. Устроен он примерно так, как оглавление в книге или предметный указатель. Большинство баз данных 1С по объёму более чем наполовину состоят из индексов. Для каждого индекса обязательно хранится его статистика.
За подробностями внутреннего устройства, как обычно отсылаю в BOL:
В целом MS SQL Server сам справляется с поддержкой целостности и эффективности статистик и индексов, но если никак ему не помогать, то постепенно накапливаются следующие проблемы:
- Статистика становится существенно неточной, причем это выясняется сервером именно в тот момент, когда она нужна.
- Индексы становятся сильно фрагментированными и перемешанными.
- Часть данных на строк, когда-то участвовавших в индексе уже удалена, и из-за этого индекс занимает на диске больше места и требует при выполнении запросов больше операций ввода-вывода.
Для того, чтобы эти накопленные эффекты не влияли на производительность, рекомендуется выполнять регламентное обслуживание статистик и индексов. Так затратив ресурсы на обновление и упорядочивание данных в незагруженное время мы скорее всего не столкнёмся с проблемами во время интенсивной эксплуатации.
Стоит учесть, что 1C для облегчения переносимости архитектуры между разными видами СУБД использует лишь небольшую часть современных возможностей индексирования MS SQL Server. За счет этого обслуживание индексов и статистик несколько упрощается.
Итак, что такое это "обслуживание"? Всё просто.
- Статистика просто пересчитывается. Читается вся таблица или часть случайно выбранных страниц и полностью пересчитывается статистика.
- Индексы могут быть дефрагментированы двумя способами:
- Перестроение — полное построение индекса. При этом обычно станицы становятся максимально плотно забиты данными, а статистика обязательно обновляется (всё равно все данные читать). Обычно данные индексированные данные полностью недоступны при перестроении индекса.
- Реорганизация — серия небольших локальных перемещений страниц так, чтобы индекс не был фрагментирован. При этом статистика не пересчитывается, данные всё время выполнения доступны (точнее, недоступна лишь совсем небольшая часть в каждый момент времени). Но при большой степени фрагментации эта процедура значительно дольше.
Для обслуживания есть специальные "кирпичики" в планах обслуживания (maitenance plan), которые так и называются:
- Update Statistics Task
- Rebuild Index Task
- Reorganize Index Task
Казалось бы всё просто: накидал кирпичиков, соединил стрелочкой и поехали. Такое решение возможно, но оно очень неэффективно:
- Индексы перестраиваются/реорганизуются только все сразу в данной базе. То есть даже если таблица никогда не меняется, её индексы будут перестраиваться. Это очень расточительно, а при полной модели восстановления еще и приводит к огромному росту журналов транзакций.
- Статистики тоже перестраиваются вне зависимости от актуальности, причем даже если они были только что обновлены при перестроении индексов.
- Нет никаких гарантий, что операция обслуживания завершится за то время, которое вы ей выделили.
Решение очень простое: пишется свой скрипт обслуживания, который убирает эти ограничения. Такой скрипт можно запускать из задания (job) MS SQL Server Agent или из "кирпичика" Execute T-SQL Statement Task в планах обслуживания (кому как удобнее). В интернете можно найти много подобных скриптов (в простейшем виде они даже в документации есть), но мне ни один не подошёл, и поэтому я пользуюсь своим "велосипедом". Этот скрипт и приведён ниже. Он подходит без изменений для большинства баз данных 1С до примерно 0,5-0,7 ТБ (дальше его уже лучше немного доработать, если кому-то интересно/актуально могу пояснить в комментариях).
Особенности скрипта:
- Как и в большинстве подобных скриптов, анализируется динамическое представление sys.dm_db_index_physical_stats, по которому выясняется степень фрагментации и заполненности страниц индекса.
- Можно задать для обработки лишь часть баз данных, можно, наоборот, исключить некоторые БД из обслуживания.
- Контролируется время выполнения скрипта.
- Очень грубо, но оценивается размер записи в журналы транзакций.
- Есть возможность исключить из обработки совсем небольшие таблицы.
- У скрипта есть режим "эмуляции" работы, чтобы оценить то, как он будет работать.
- Сначала обрабатываются самые большие таблицы (так как обычно их обслуживание важнее).
- Скрипт работает на SQL Server 2008 и более поздних (на 2005 тоже должен работать, но мне уже негде проверить)
- Результат вывода в режиме эмуляции сам является корректным TSQL скриптом.
- Ну и конечно, при регулярном выполнении этот скрипт на порядок легче, чем стандартные операции плана обслуживания.
С чем нужно быть осторожным при запуске скрипта:
- Нежелательно пересечение работы скрипта с интенсивной работой пользователей или с полным резервным копированием.
- Чтение из sys.dm_db_index_physical_stats в режиме DETAILED достаточно интенсивно читает с дисков.
- Скрипт предназначен для баз 1С или подобных. Не стоит экспериментировать с ним на совсем специфичных базах данных с нестандартными индексами.
- Если у вас есть таблицы 100-200 ГБ и больше, то при распараллеливании построения индекса, после перестроения он формально снова может оказаться фрагментированным.
- Статистики пересчитываются без полного сканирования. Это заметно быстрее. Если вам нужно полное сканирование каких-то таблиц, то пишите отдельный скрипт.
Рекомендации по запуску:
- Никаких регулярных "шринков" на рабочих базах быть не должно. Еще раз: шринкам не место в регулярном обслуживании!
- При полной модели восстановления я бы поставил полное резервное копирование после обслуживания индексов. Иначе при необходимости восстановления придётся донакатывать достаточно тяжёлый кусок журналов транзакций после восстановления основного образа. Простую модель восстановления на промышленно используемых БД я считаю либо редким исключением, либо частым недоразумением.
- Первый запуск лучше выполнить вручную в SSMS чтобы оценить время работы.
Остальное можно прочитать в коде и в комментариях.
PS: Движок сайта некорректно отобажает текст со знаками больше-меньше, поэтому скрипт приложен файлом, а в статье оставлено только начало скрипта.
-- Параметры скрипта
declare @database_names as nvarchar(max) = N''; -- имена баз задавать через запятую, если не заданы, то все несистемные базы
-- пока парсер примитивный - строка просто делится по запятым и обрезаются крайние пробелы
-- (если в имени базы будет запятая или в начале или конце имени пробел, то система не работает)
-- если указано "-ИмяБазы", то база будет исключена,
declare @index_size_threshhold as int = 1024; -- минимальный размер в КБ для перестраиваемого индекса. Нет смысла перестраивать индексы на десяток страниц
declare @index_rebuild_threshhold as numeric(5,2) = 25; -- показатель фрагментации, начиная с которого происходит перестроение индекса
declare @index_defrag_threshhold as numeric(5,2) = 12; -- показатель фрагментации, начиная с которого происходит дефрагментация индекса
declare @index_rebuild_space_used_threshhold as numeric(5,2) = 50; -- процент заполненности страниц меньше которого требуется перестроение индекса
declare @timeout as int = 7200; -- максимальное время работы скрипта
declare @max_size as bigint = 536870912; -- максимальный суммарный обрабатываемый размер в КБ (чтобы не нагенерировать логов на терабайты) -- 512*1024*1024 КБ = 0,5 ТБ
declare @is_emulate as bit = 1; -- 0 - выполнять, 1 - только вывести команды
