Лучший способ развернуть новый индекс на очень большую таблицу в SQL Server 2008

У меня есть база данных в производстве с одной таблицей, которая стала чрезвычайно большой (много накопленных данных).

Чтобы повысить производительность запросов, я использовал оптимизатор sql-сервера, который предложил новый индекс.

Итак, я сделал копию производственной базы данных для тестирования, и она повышает производительность, однако моя проблема в том, что для создания индекса потребовалось около 24 часов, а при создании индекса приложение непригодно.

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

На данный момент у меня есть только несколько идей.

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

Конечно, у этого есть свой набор проблем, таких как необходимость объединить данные вместе, поэтому мне не нравится эта идея по этой причине.

Это стандартный редактор SQL Server 2008.

Обычно я устанавливаю изменения базы данных на script.

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

Ответ 1

Учитывая отсутствие вычислительной мощности, доступной на машине VM, в сочетании с тем, что, без сомнения, является довольно бедной пропускной способностью ввода-вывода, я бы на самом деле рассмотрел возможность расчета времени резервного копирования, восстановления до полупорядочного сервера, индекса, а затем резервного копирования/восстановления обратно к машине VM.

Чтобы избежать первоначальной резервной копии, занимающей много времени, вы можете сделать резервную копию ее в один прекрасный день и перемещать ее в течение дня, а затем, когда откроется окно обслуживания, создайте резервную копию журнала транзакций и переместите это через - на основе этого быть меньшим шагом. (Это предполагает режим объемного/полного журнала)

Ответ 2

Если вы использовали Enterprise, вы можете использовать опцию ONLINE CREATE INDEX, которая строит индекс без сохранения долгосрочных блокировки на столе. Во всем этом есть оговорки; см. связанную статью для деталей, и вы можете обнаружить, что влияние производительности слишком велико. Но это академическое, поскольку вы сказали, что используете стандартную версию (извините за отсутствие этого вначале).

Тот факт, что виртуальная машина сразу заставляет задуматься о временном "откачивании" виртуальной машины или даже о временном переходе на максимальную вне VM. Чтобы перестроить индекс на очень большой таблице, я думаю, что RAM и скорость ввода-вывода будут самыми большими факторами; это виртуальная машина, использующая диск напрямую или виртуализированный диск? Можете ли вы временно переместить данные на физический диск? Такого рода вещи.

FWIW, ваша идея "take-it-offline-and-do-it" - это именно то, что я сделал бы в базе данных MySQL (никогда не было в базе данных SQL Server): возьмите главный DB, возьмите снимок, очистить binlogs/enable binlogging и запустить его. Сделайте индекс на отдельной машине. Когда будете готовы, снимите DB, сделайте резервную копию обновленной базы данных (на всякий случай), верните снимок, примените binlogs и верните резервную копию базы данных. Это действительно так просто; Я ожидаю, что вы сможете это сделать и с SQL Server. Разумеется, он предполагает, что вы можете использовать 24-битные журналы для таблицы (недавно оптимизированной) в течение приемлемого временного окна!

Ответ 3

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

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

Надеюсь, что это поможет...

Ответ 4

Почему бы вам не разделить таблицу и не индексировать каждый раздел. Таким образом, вы индексируете только мелкие части, а затем можете объединить разделы позже.