Как работали бы индексы на читаемых вторичных репликах групп доступности (AG)?

Автор: Paul Randal, How would indexes on AG readable secondaries work?

В рассылке MVP появилось предложение ввести временные некластерные индексы на читаемых вторичных репликах AG — по аналогии с временной статистикой. Я ответил, что, на мой взгляд, реализовать это чрезвычайно трудно, и пообещал объяснить почему. Ниже — моя аргументация. Замечу: это не исчерпывающий перечень, а лишь основные проблемы, которые я вижу.

Где их хранить?

Напрашивается несколько вариантов того, где располагать такие временные индексы, каждый со своей долей трудностей:

1. Автоматически создать временную группу файлов на читаемой вторичной реплике
   1. Проблема в том, что читаемая вторичная реплика работает в режиме только для чтения, а добавление группы файлов потребует записей в целый ряд системных таблиц (включая sys.sysfiles1, sys.sysdbfiles, sys.sysbrickfiles, sys.sysprufiles). Даже если эту преграду удастся обойти, проблема остаётся…
   2. Вторичная реплика — только для чтения, значит, где хранить всю информацию об индексе как таковом? Немало системных таблиц содержит сведения об индексе (включая sys.sysallocunits, sys.sysrowsets, sys.sysrscols, sys.sysidxstats, sys.sysiscols). И даже если это преодолимо, остаётся проблема сопровождения индекса (см. ниже).
2. Создать пустую группу файлов на первичной реплике, чтобы на читаемой вторичной реплике была пустая группа для использования
   1. Это решает только пункт 1.a выше.
3. Хранить их в tempdb, как и временную статистику
   1. Это снимает вопросы 1.a и 1.b, но добавляет новую сложность…
   2. Нужно отслеживать, что в tempdb есть индексы, фактически принадлежащие другой базе данных. С временной статистикой такое уже реализовано, следовательно, это возможно, однако есть задача куда серьёзнее…
   3. Заставить Storage Engine (конкретно подсистему Access Methods) читать данные из индекса в tempdb вместо индекса в «настоящей» базе. Это не правка в самом Storage Engine (подсистема Access Methods лишь создаёт и использует набор данных поверх того, что запросит Процессор запросов), но план запроса должен «понимать», что индекс, якобы находящийся в таблице реальной базы, на деле расположен в tempdb, чтобы запрос к Storage Engine шёл по правильному адресу. Нетрудно представить, насколько это непросто в сценариях вроде выборки по ключу на основе поиска/просмотра по некластерному индексу.
   4. На мой взгляд, это единственно потенциально практичный вариант размещения временных индексов.

С местом хранения, пожалуй, разобрались — это как раз более лёгкая часть.

Как их поддерживать в актуальном состоянии?

Допустим, что индексы хранятся по схеме № 3 выше. Тогда возникает куда более трудная задача: как держать их в согласии с базовой таблицей (то есть при выполнении INSERT, UPDATE или DELETE корректно обновлять все некластерные индексы). Само создание индекса было бы относительно несложно по методике онлайн‑построения, поэтому на этом не останавливаюсь.

Во времена SQL Server 2000 это было бы проще (хотя тогда AG ещё не существовали :-) ), потому что поддержание некластерных индексов в реальном времени входило в обязанности Storage Engine. Начиная с SQL Server 2005, это перешло в ведение Процессора запросов: он управляет обслуживанием индексов, а Storage Engine делает то, что ему велят (вставить запись, обновить столбец и т. п.). При этом у Storage Engine есть возможность попросить Процессор запросов изменить некластерные индексы без явной операции над таблицей — ею пользуется DBCC CHECKDB при исправлении отсутствующих или лишних записей в некластерных индексах во время операций восстановления.

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

Интересны лишь те записи журнала, что относятся к самой таблице (то есть изменения кучи или кластерного индекса). Но воспроизведение записей журнала выполняет часть Storage Engine, отвечающая за восстановление, а не подсистема Access Methods; следовательно, в коде восстановления нет контекста о таблице, её столбцах, индексах и прочем. Чтобы это заработало, потребуется следующее:

1. Компонент восстановления должен «знать», что записи журнала LOP_INSERT_ROWS, LOP_DELETE_ROWS, LOP_MODIFY_ROW, LOP_MODIFY_COLUMNS, а также любые другие, связанные, например, с усечением таблицы, сменой структуры с кучи на кластерный индекс и обратно, для определённых единиц размещения (то есть для таблиц с временным некластерным индексом) нужно передавать новому фрагменту кода для обслуживания временного индекса.
2. Новый фрагмент кода должен «знать» о созданных индексах и выполнять их обслуживание. Иными словами, пришлось бы заново построить механизм обслуживания некластерных индексов внутри Storage Engine — как в SQL Server 2000. Дополнительная сложность — при изменениях строк таблицы: записи журнала LOP_MODIFY_ROW и LOP_MODIFY_COLUMNS не указывают, какие столбцы меняются, — только смещение и длину изменения (напомню, это физические записи). Теоретически можно изменить формат записи журнала, добавив битовую карту идентификаторов изменяемых столбцов — присутствующую лишь тогда, когда на читаемой вторичной реплике есть временный индекс. Это означает ещё больше условного кода.
3. Другой вариант — чтобы Процессор запросов на первичной реплике «знал» о наличии временного индекса и порождал специальные логические записи операций в журнале, помогающие его обслуживать на читаемой вторичной реплике (по аналогии с транзакционной репликацией). Думаю, это легче, чем пытаться интерпретировать обычные записи журнала и выводить из них необходимые действия.
4. Весь этот дополнительный код, который придётся выполнять, потенциально замедлит воспроизведение журнала — причём замедление будет нарастать с каждым добавленным временным индексом.

Итоги

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

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