Перенос индексов в новую файловую группу: Microsoft по-прежнему вас хэйтит

Автор: Erik Darling, Moving Indexes To A New Filegroup: Microsoft Still Hates You

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

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

Но это не так.

Насколько всё плохо, зависит от того, что именно вы переносите. Давайте пройдёмся от наименее болезненного к самому болезненному, потому что боль здесь поучительна.

Обычные индексы

Под «обычным» мы будем понимать индекс, который не таскает за собой тонны LOB-данных. Если у вас именно так, жизнь немного легче. Не проще, но легче.

То, что удивляет людей: вы не можете просто перестроить индекс в новой файловой группе. Нет такой команды ALTER INDEX REBUILD WITH (MOVE_THIS_SOMEWHERE_USEFUL = ON). Это было бы слишком чертовски просто, а мы здесь простоты не любим (если только это не подсказка RECOMPILE).

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

Затем вы пересоздаёте его в новой файловой группе с включённым параметром DROP_EXISTING.

CREATE UNIQUE NONCLUSTERED INDEX 
    whatever
ON dbo.SomeTable
(
    column_one,
    column_two
)
INCLUDE
(
    column_three
)
WHERE column_one > 0
WITH
(
    DROP_EXISTING = ON,
    ONLINE = ON, 
    (and all the other stuff you can or might want do, like PAGE compression)
)
ON [NewFileGroup];

Да, вы можете сделать новую файловую группу используемой по умолчанию, чтобы не беспокоиться о включении её в каждый скрипт, но кто знает?

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

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

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

Это просто утомительно и не прощает ошибок, а это само по себе сложность. Как книга Кормака Маккарти (Cormac McCarthy).

Кучи (Heaps)

Если у вас кучи, ваша жизнь сейчас станет хуже.

Отстой в том, что я это напечатал и понял, что это звучит как то, что сказала бы LLM.

А, чёрт с ним.

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

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

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

Но если таблица должна оставаться кучей, вам придётся создать скрипт для удаления только что созданного кластерного индекса. Это превратит её обратно в кучу в новой файловой группе. Таким образом, перемещение стоит вам создания и удаления для того, что изначально не должно было иметь индекса.

LOB-данные

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

Хотя бы раз.

Есть продукты, которые должны быть испытаны теми, кто их создаёт, и этот — один из них. У меня такое чувство, что часто это не так.

Это относится к кластерным таблицам с LOB-столбцами, а также к вашим кучам с LOB-данными, потому что LOB делает всё хуже равномерно. О, и если у вас есть некластерные индексы с LOB-данными в них, ну… вас это тоже касается.

Когда вы выполняете танец с CREATE и DROP_EXISTING для перемещения таблицы, данные в строках перемещаются. LOB-данные — нет. Они остаются там, где были, глядя на вас и отказываясь переезжать. Вы можете проверить это сами, изучив единицы распределения до и после и увидев, что единица LOB_DATA осталась на том же месте.

Исправление приходит из статьи Кимберли Трипп (Kimberly Tripp), которая спасла многих людей от многих проблем на протяжении многих лет (Understanding LOB data). Этот трюк основан на особенности того, как SQL Server обрабатывает секционирование: LOB-данные физически перемещаются, когда объект переходит из несекционированного в секционированный или из одной схемы секционирования в другую. Таким образом, вы делаете таблицу секционированной, что заставляет LOB-данные переместиться, даже если у вас нет реального интереса к секционированию.

Последовательность действий такова:

1. Создайте функцию секционирования и схему секционирования.
2. Примените схему к таблице, создав индекс на ней с DROP_EXISTING. Это переместит данные в эту схему.
3. Затем создайте индекс СНОВА, на этот раз в обычной файловой группе, снова с DROP_EXISTING, что сделает таблицу снова несекционированной и переместит всё, включая LOB, в вашу целевую файловую группу.

Вы прочитали правильно. Требуется два создания индекса с DROP_EXISTING, чтобы переместить LOB-данные. Таблица на короткое время становится секционированной только для того, чтобы обмануть движок и заставить его подхватить единицу распределения LOB и перенести её вместе с собой.

CREATE PARTITION FUNCTION pf_temp_move (bigint)
    AS RANGE RIGHT
    FOR VALUES (9223372036854775807);

CREATE PARTITION SCHEME ps_temp_move
    AS PARTITION pf_temp_move
    ALL TO ([NewFileGroup]);

/* Перемещение в схему. LOB перемещается вместе с ней. */
CREATE UNIQUE CLUSTERED INDEX 
    whatever
ON dbo.SomeTable 
    (some_bigint_column)
WITH
(
    DROP_EXISTING = ON
)
ON ps_temp_move (some_bigint_column);

/* Перемещение обратно в обычную файловую группу. Таблица больше не секционирована. */
CREATE UNIQUE CLUSTERED INDEX 
    whatever
ON dbo.SomeTable 
    (some_bigint_column)
WITH
(
    DROP_EXISTING = ON
)
ON [NewFileGroup];

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

Вы тоже это правильно читаете. Каждый некластерный индекс перестраивается дважды.

Скажите, а вы не собирались уже давно подчистить некоторые индексы?

Выбор граничного значения

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

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

• int: используйте максимум для int, 2147483647
• bigint: используйте максимум для bigint, 9223372036854775807
• uniqueidentifier: используйте FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF, но прочитайте примечание ниже, прежде чем доверять этому
• date, datetime, datetime2: 99991231 — стандартный выбор

Что касается GUID, будьте осторожны. SQL Server сортирует значения uniqueidentifier не так, как вы читаете байты слева направо на экране. Он сортирует по последней группе из шести байтов сначала, а затем двигается по группам в обратном порядке. Это действительно странный порядок, и он постоянно сбивает людей с толку. Хорошая новость в том, что GUID из всех F всё равно будет сортироваться выше всех, как бы вы его ни нарезали, потому что каждый байт уже максимален, поэтому FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF — это безопасная верхняя граница. Только не предполагайте, что остальной порядок сортировки GUID соответствует тому, что говорят ваши глаза.

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

И ещё те, у кого кластеризация по строке

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

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

/* Граница выше любого значения в ключе кластеризации varchar(50) */
DECLARE @boundary varchar(50) = REPLICATE('z', 50);

И даже это зависит от того, решит ли ваша сортировка, что «z» стоит выше той ерунды, которая там на самом деле. Смешанный регистр, символы с диакритическими знаками и чувствительность к регистру — всё это имеет голос. Так что вы не можете просто выбрать максимальное значение из таблицы, как все мы. Вам придётся задуматься о порядке сортировки.

Ваша операция ONLINE не так «онлайн», как вы думаете

Вы устанавливаете ONLINE = ON, говорите себе, что ведёте себя как ответственный взрослый, и ожидаете, что перемещение пройдёт незаметно для всех. Затем вы наблюдаете, как перетаскивание всех этих LOB-данных создаёт огромную конкуренцию за tempdb, и внезапно ваша «онлайн» операция всё равно вызывает блокировки, но уже через чёрный ход.

Таким образом, ваша «онлайн» перестройка «онлайн» только в узком смысле, что она не удерживает долгую блокировку схемы на самой таблице, но она нагружает tempdb настолько сильно, что всё остальное, борющееся за страницы tempdb, вынуждено ждать в очереди за вами. Блокировки никуда не делись. Они просто переместились туда, куда вы не смотрели. Следите за конкуренцией за страницы распределения tempdb во время этого процесса, потому что именно там проявляется боль, а не на таблице, которую вы переносите.

ONLINE — это предложение, а не обещание

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

Кендра Литтл (Kendra Little) описала отличный пример онлайн-перестройки, которая выполнялась в офлайн-режиме и удерживала эксклюзивные блокировки на всём протяжении, без предупреждения и без ошибки (Ugly Bug: SQL Server Online Index Rebuild Sometimes Happens Offline Without Warning).

Её воспроизведение использовало ALTER INDEX REBUILD WITH (ONLINE = ON) для таблицы, из которой ранее был удалён LOB-столбец, что приводит к состоянию, когда движок переключается на офлайн-операцию и удерживает X-блокировки на всём протяжении.

Джо Оббиш (Joe Obbish) также написал о похожем здесь. Однако статья Джо посвящёна columnstore-индексам. Это может не относиться к вам.

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

Более широкий смысл важен даже тогда, когда вы не наступаете на эту конкретную ошибку. Ваша операция создания индекса в онлайне всё равно должна захватывать свои блокировки, какими бы минимальными они ни предполагались. И если что-то уже стоит на пути, когда она пытается их захватить, ваша онлайн-операция блокируется. Теперь она сидит и ждёт, и всё, что появляется за ней, тоже блокируется, потому что она удерживает своё место в очереди блокировок, ожидая нужной блокировки. Одна зависшая онлайн-перестройка превращается в цепочку блокировок, и эта цепочка может стоять очень, очень долго, пока вы гадаете, почему операция «без простоя» привела к падению вашего приложения. Это также заставляет вас выглядеть как придурком, потому что вы сказали: «Я могу сделать это полностью онлайн и не вызвать никаких блокировок, шеф».

Здесь вас также поджидает некоторая версионная магия. Поскольку перемещение индекса в новую файловую группу вынуждает вас использовать CREATE INDEX с DROP_EXISTING вместо ALTER INDEX REBUILD, ваш доступ к WAIT_AT_LOW_PRIORITY зависит от вашей версии.

Для ALTER INDEX эта опция доступна начиная с SQL Server 2014. Для CREATE INDEX синтаксис WAIT_AT_LOW_PRIORITY появился только в SQL Server 2022, а также в Azure SQL Database и Managed Instance.

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

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

Короткое резюме

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

Ничто из этого не должно было быть таким сложным. Но мы здесь, и данные сами себя не перенесут.

Ещё один момент, который стоит учесть: в SQL Server существует множество стратегий построения индексов. Он может выбрать однопоточное построение индексов на гигантских таблицах. Он даже может выбрать однопоточное построение всех 23 некластерных индексов на огромной таблице, пока вы выполняете секционирование и снятие секционирования для перемещения LOB-данных.

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