Новое в SQL Server 2022: опция WAIT_AT_LOW_PRIORITY для создания индексов

Автор оригинала: Daniel Calbimonte

Эта новинка в SQL Server 2022 позволяет создавать, изменять и перестраивать индексы, и при этом пережидать перед получением доступа к ресурсу другие задачи, чтобы снизить конфликты за этот ресурс, которые могут возникать во время обслуживания индексов.

В SQL Server 2022 в синтаксисе команд работы с индексами добавлен новый параметр (WAIT_AT_LOW_PRIORITY), позволяющий ожидать с низким приоритетом, приостанавливая работу над индексом, пока необходимые ресурсы не высвободятся. Эта возможность призвана снизить влияние на длительные запросы, выполнение которых более приоритетно для пользователей. При обслуживании большой таблицы создание\перестроение индекса может занять несколько часов. Поэтому полезно иметь несколько вариантов определения приоритетов подобных операций. В этой статье мы рассмотрим возможный выбор параметров, с которыми этот новый аргумент может использоваться для создания\перестроения индекса. Важно также отметить, что эта возможность доступна только для редакций Enterprise и Developer.

Синтаксис

Давайте посмотрим на синтаксис команды с новым аргументом, чтобы понять, как это работает:

CREATE CLUSTERED INDEX INDEX_NAME
ON TABLENAME (COLUMNNAME)
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = X MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
        )
     );
GO 

• INDEX_NAME — имя индекса;

• TABLENAME — имя таблицы, в которой будет создан индекс;

• COLUMNNAME — столбец или столбцы, по которым будет создан индекс;

• ONLINE = ON — это необходимый параметр, без которого не получится использовать новую опцию и из-за чего вся эта радость доступна только для версий Enterprise или Developer;

• WAIT_AT_LOW_PRIORITY — новая опция;

  • MAX_DURATION — максимальное время ожидания в минутах до выполнения действия.

  • ABORT_AFTER_WAIT — используется для прерывания операции, если она превышает время ожидания.

    • NONE — ждать блокировки с обычным приоритетом

    • SELF — прерывание этой ONLINE операции DDL.

    • BLOCKERS — убивает транзакции, блокирующие построение индекса.

Создание тестовой таблицы

Давайте создадим тестовую таблицу с именем dbo.salesOrderDetailTest, используя для её наполнения таблицу в базе данных AdventureWorks.

SELECT * INTO dbo.salesOrderDetailTest FROM [Sales].[SalesOrderDetail]

CREATE INDEX с опцией WAIT_AT_LOW_PRIORITY

Создадим для тестовой таблицы индекс с опцией WAIT_AT_LOW_PRIORITY.

CREATE CLUSTERED INDEX cindex 
ON [dbo].[salesOrderDetailTest] ([SalesOrderDetailID]) 
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 50 MINUTES, ABORT_AFTER_WAIT = SELF)
        )
     );
GO

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

Следующий пример аналогичен, но он «убивает» сеансы, которые блокируют создание индекса по истечении 50 минут, а затем создаёт индекс.

CREATE CLUSTERED INDEX cindex 
ON [dbo].[salesOrderDetailTest] ([SalesOrderDetailID]) 
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 50 MINUTES, ABORT_AFTER_WAIT = KILL)
        )
     );
GO

ALTER INDEX с опцией WAIT_AT_LOW_PRIORITY

Если нужно перестроить\реорганизовать индекс, или внести в него изменения, можно использовать следующую конструкцию в ALTER INDEX:

ALTER INDEX cindex 
ON [dbo].[salesOrderDetailTest]
REBUILD
WITH (ONLINE = ON 
        (WAIT_AT_LOW_PRIORITY 
           (MAX_DURATION = 30 MINUTES, ABORT_AFTER_WAIT = SELF)
        )
     );
GO