SQL в вопросах и ответах: Что надо знать о журналах

Журналы транзакций — обязательный компонент любого экземпляра SQL Server. Очень важно правильно и эффективно управлять ими.

Пол С. Рэндал

Быстрый рост

Вопрос Я успешно использовал перестроение индексов в оперативном режиме с момента их появления SQL Server 2005. Недавно мы обновили сервер до SQL Server 2008, и теперь журнал транзакций растет больше, чем раньше. Не могли бы пояснить, что происходит?

Ответ До SQL Server 2005 операции перестроения всегда налагали блокировку на время операции перестроения. Перестроение кластеризованного индекса означало монопольную блокировку таблицы (запрещение любых операций чтения или записи). При перестроении некластеризованного индекса требовалась совмещаемая блокировка таблицы (запрещены параллельные операции записи).

С появлением операций с индексами в оперативном режиме в SQL Server 2005 Enterprise Edition блокирование было заменено операциями в оперативном режиме. Блокировки выполняются только на краткое время в начале и в конце операции (подробнее см. запись в блоге).

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

Начиная с SQL Server 2008, выполняется полное протоколирование индексных операций в интерактивном режиме во всех моделях восстановления. Это гарантирует, что в процессе восстановления базы данных, в котором задействованы операции с индексами в оперативном режиме, не возникнет никаких проблем. (Подробнее см. стать).

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

Выполняйте резервное копирование своих резервных копий

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

Ответ Вы говорите, что «импортированные данные не всегда попадали в резервную копию». Из этого я предполагаю, что процесс импорта вы выполняете как одну большую транзакцию.

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

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

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

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

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

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

Перестроение или реорганизация

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

Ответ С того момента, когда я написал команду DBCC INDEXDEFRAG для SQL Server 2000, существуют два метода устранения фрагментации. Нужны два метода, потому что у них совершенно разные характеристики. К сожалению нет документа, который бы адекватно описывал различия между тремя методами, начиная с SQL Server 2005, — ALTER INDEX … REORGANIZE (новое название: DBCC INDEXDEFRAG), ALTER INDEX … REBUILD (новое название: DBCC DBREINDEX) и версия оперативного режима ALTER INDEX … REBUILD.

Вот краткая сравнительная характеристика параметров REBUILD и REORGANIZE команды ALTER INDEX:

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

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

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

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

• 0–10 % — ничего не предпринимать
• 10–30 % — использовать ALTER INDEX … REORGANIZE
• 30 % и больше — использовать ALTER INDEX … REBUILD

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

Для экономии времени можно также воспользоваться готовыми сценариями обслуживания индексов, например разработанными Ола Халлегреном (Ola Hallengren).

Максимумы и минимумы размера журнала

Вопрос Я только что стал администратором базы данных и испытываю трудности с созданием нескольких новых баз данных для наших разработчиков. Проблема в том, что я затрудняясь с размером журнала транзакций, — каким он должен быть? Какой бы я размер не задал, объем журнала выходит за заданные рамки, после чего размер практически не меняется. Я урезаю журнал, но он снова возвращается к прежнему размеру. Есть ли способ «правильно» задать размер журнала при создании базы данных?

Ответ Прежде всего, не надо регулярно уменьшать размер журнала транзакций. Когда журнал транзакций «хочет» увеличиться, новое выделенное для него пространство нужно инициализировать нулями. Это означает, что транзакциям, которым надо внести записи в журнал, придется подождать, пока не завершится инициализация.

Инициировав циклы «сокращения-расширения» журнала транзакций вы нанесете удар по производительности сервера SQL Server. Лучше оставить размер таким, каким его выбрал сам журнал. К сокращению данных или журналов транзакций прибегают очень редко.

Оценить размер журнала транзакций можно на основе операций в базе данных. Подробнее почитайте в моей статье по адресу https://technet.microsoft.com/ru-ru/magazine/gg552991.aspx. Если после каждого сокращения журнал транзакций увеличивается до определенного размера, который затем остается практически неизменным, то, скорее всего, так и надо. Оставьте этот размер в покое, если только он не слишком велик. Если же размер очень большой, выясните операцию, которая вызывает рост, и попробуйте разбить ее на части, чтобы у журнала транзакций была возможность очиститься.

Пол С. Рэндал (Paul S. Randal)носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/@PaulRandal.