Программа похудения для журнала транзакций

Автор: Paul Randal, Trimming the Transaction Log Fat

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

Задержка записи в журнал транзакций

Задержку операций записи в журнал транзакций можно отслеживать с помощью DMV sys.dm_io_virtual_file_stats и сопоставлять с ожиданиями WRITELOG, возникающими в системе. 

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

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

Существуют две основные причины генерации лишних записей журнала: неиспользуемые некластерные индексы и фрагментация индексов.

Неиспользуемые некластерные индексы

Всякий раз, когда запись вставляется в таблицу, запись должна быть вставлена в каждый некластерный индекс, определённый для этой таблицы (за исключением фильтрованных индексов с соответствующими фильтрами, которые я буду игнорировать). Это означает, что генерируются дополнительные записи журнала — как минимум по одной на каждый некластерный индекс — для каждой вставки в таблицу. То же самое относится и к удалению записи из таблицы — соответствующие записи должны быть удалены из всех некластерных индексов. При обновлении записи в таблице записи некластерного индекса обновляются только в том случае, если ключевые столбцы или включённые столбцы некластерного индекса были частью обновления.

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

Неиспользуемые индексы появляются из разных источников: кто-то по ошибке создаёт индекс на каждый столбец таблицы, кто-то создаёт все индексы, предложенные DMV по отсутствующим индексам, или все индексы, рекомендованные программным помощником по настройке базы данных. Также может быть, что характеристики рабочей нагрузки изменились, и то, что раньше было полезным, больше не используется.

Откуда бы они ни взялись, неиспользуемые индексы следует удалять, чтобы снизить их нагрузку. Вы можете определить, какие индексы не используются, с помощью DMV sys.dm_db_index_usage_stats, и я рекомендую прочитать статьи моих коллег Кимберли Л. Трипп (здесь) и Джо Сэка (здесь и здесь), в которых объясняется, как правильно использовать это DMV.

Фрагментация индексов

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

Фрагментация вызывается операцией, называемой разделением страницы (page split). Простейшая причина разделения страницы — это когда запись индекса должна быть вставлена на определённую страницу (из-за значения её ключа), и на странице недостаточно свободного места. В этом случае выполняются следующие операции:

1. Выделяется и форматируется новая страница индекса.
2. Часть записей с полной страницы перемещается на новую страницу, создавая свободное место на нужной странице.
3. Новая страница связывается со структурой индекса.
4. Новая запись вставляется на нужную страницу.

Все эти операции генерируют записи журнала, и, как можно себе представить, это может быть значительно больше, чем требуется для вставки новой записи на страницу, которая не требует разделения. Ещё в 2009 году я описал в блоге анализ стоимости разделения страницы с точки зрения журнала транзакций и обнаружил случаи, когда разделение страницы генерировало более чем в 40 раз больше записей журнала, чем обычная вставка!

Первый шаг по снижению дополнительных затрат — удалить неиспользуемые индексы, чтобы они не генерировали разделения страниц. Второй шаг — выявить оставшиеся индексы, которые фрагментируются (и, следовательно, страдают от разделений страниц), с помощью DMV sys.dm_db_index_physical_stats (или нового SQL Sentry Fragmentation Manager) и заранее создать в них свободное пространство, используя коэффициент заполнения индекса (fillfactor). Коэффициент заполнения указывает SQL Server оставлять пустое место на страницах индекса при его создании, перестроении или реорганизации, чтобы было место для вставки новых записей без необходимости разделения страницы, что сокращает количество дополнительных записей журнала.

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

Резюме

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

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