Заблуждения о перестроении индексов (распределение, режим BULK_LOGGED, блокировки)

Автор: Paul Randal, Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

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

Миф 1: перестроение индекса предварительно распределяет необходимое пространство

У этого мифа есть два варианта:

1. Пространство для новой копии индекса предварительно распределено.
2. Пространство для этапа сортировки при перестроении предварительно распределено.

Ни то, ни другое не соответствует действительности. Перестроение индекса (будь то в режиме online или offline, и по крайней мере начиная с версии 7.0) создаёт новую копию индекса перед удалением старой. Страницы и экстенты, необходимые для этого, всегда распределяются по мере необходимости, как и при любой другой операции в SQL Server. Фаза сортировки при перестроении индекса, если она требуется (в некоторых случаях она пропускается), следует тому же поведению распределения.

Миф 2: индексы перестраиваются в пределах одного файла в файловой группе с несколькими файлами

Это новое заблуждение, которое я услышал – (перефразируя) «В файловой группе с двумя файлами индекс из файла 1 будет перестроен в файл 2. В следующий раз, когда он будет перестроен, он будет построен в файле 1. И так далее».

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

Миф 3: некластерные индексы всегда перестраиваются при перестроении кластерного индекса

Это неверно. Правила здесь немного сложны, но их можно суммировать следующим образом:

• В версии 2005+ перестроение уникального или неуникального кластерного индекса (без изменения его определения) НЕ приведёт к перестроению некластерных индексов.
• В версии 2000:
  • Перестроение неуникального кластерного индекса ПРИВЕДЁТ к перестроению некластерных индексов.
  • Перестроение уникального кластерного индекса НЕ приведёт к перестроению некластерных индексов.

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

Для более подробного обсуждения этого вопроса см. мою запись в блоге прошлой осени – Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?.

Миф 4: режим восстановления BULK_LOGGED уменьшает размер журнала транзакций и резервных копий журнала при перестроении индекса

Этот миф частично правдив.

• Переключение в режим восстановления BULK_LOGGED во время операции перестроения индекса ДЕЙСТВИТЕЛЬНО уменьшит объём сгенерированного журнала транзакций, что очень полезно для ограничения размера файла журнала транзакций (обратите внимание, я говорю «файла», а не «файлов» – вам нужен только один файл журнала).
• Переключение в режим восстановления BULK_LOGGED во время перестроения индекса НЕ уменьшит размер РЕЗЕРВНОЙ КОПИИ журнала транзакций. Хотя операция будет минимально логируемой, следующая резервная копия журнала транзакций считает весь журнал транзакций с момента последнего резервного копирования плюс все экстенты, которые были изменены минимально логируемым перестроением индекса. Это приведёт к тому, что резервная копия журнала будет почти точно такого же размера, как и при полностью логируемом перестроении индекса. ЕДИНСТВЕННЫЙ случай, когда резервная копия журнала будет содержать экстенты данных, – это когда с момента последнего резервного копирования журнала произошла минимально логируемая операция – дополнительную информацию см. здесь на MSDN.

Если вы рассматриваете возможность использования режима восстановления BULK_LOGGED, имейте в виду, что вы теряете возможность восстановления на момент времени до ЛЮБОЙ точки, охватываемой резервной копией журнала транзакций, которая содержит даже одну минимально логируемую операцию. Убедитесь, что в базе данных не происходит ничего другого, что вам может потребоваться эффективно откатить с помощью восстановления на момент времени (P.I.T. recovery). Операции, которые вам следует выполнить, если вы собираетесь это сделать:

1. В режиме восстановления FULL сделайте резервную копию журнала непосредственно перед переключением в BULK_LOGGED.
2. Переключитесь в BULK_LOGGED и выполните перестроение индекса.
3. Вернитесь в FULL и немедленно сделайте резервную копию журнала.

Это ограничивает период времени, в течение которого вы не можете выполнить восстановление на момент времени.

Миф 5: онлайн-перестроение индекса не использует никаких блокировок

Этот миф неверен. Термин «онлайн» в «онлайн-операциях с индексами» является несколько неточным. Онлайн-операции с индексами должны устанавливать две очень краткосрочные блокировки таблицы. Блокировка таблицы в режиме S (Shared, разделяемая) в начале операции, чтобы заставить все планы записи, которые могут затрагивать индекс, перекомпилироваться, и блокировка таблицы в режиме SCH-M (Schema-Modification, модификация схемы – считайте её эксклюзивной) в конце операции, чтобы заставить все планы чтения и записи, которые могут затрагивать индекс, перекомпилироваться.

Последний раз, когда этот вопрос поднимался на форумах, кто-то заметил, что запросы на вставку завершаются с таймаутом после того, как операция онлайн-перестроения индекса только что началась. Проблема в том, что блокировка таблицы, которая требуется для онлайн-перестроения индекса, должна быть помещена в очередь предоставления в диспетчере блокировок до тех пор, пока она не будет получена – и она останется там до тех пор, пока существующие транзакции, удерживающие конфликтующие блокировки, либо не завершатся, либо не откатятся. Любая транзакция, требующая конфликтующей блокировки ПОСЛЕ того, как блокировка перестроения индекса была поставлена в очередь, но не получена (а затем отпущена), будет ждать за ней в очереди предоставления блокировок. Если таймаут запроса будет достигнут до того, как транзакция сможет получить свою блокировку, произойдёт таймаут.

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