Создание улучшений индексов и улучшений возобновляемых ограничений

SQL Server 2022 включает новые усовершенствования, помогающие контролировать создание индексов, а также управление уникальными и первичными ключевыми ограничениями для больших таблиц. Эти улучшения включают расширение действия предложения WAIT_AT_LOW_PRIORITY на команду CREATE INDEX и введение функции возобновляемой операции для добавления ограничений таблицы с помощью команды ALTER TABLE ADD CONSTRAINT. Включив эти функции, мы можем лучше управлять конфликтом блокировки, свести к минимуму, как операции влияют на другие запросы и транзакции, и в конечном итоге повысить общую производительность и удобство обслуживания базы данных.

Возобновляемое добавление ограничений таблицы

Функция возобновляемого добавления ограничения таблицы в SQL Server 2022 обеспечивает повышенную гибкость и контроль над операциями ограничения в больших таблицах, позволяя приостановить и возобновить операцию ALTER TABLE ADD CONSTRAINT для учета периодов технического обслуживания, восстановления после сбоев, таких как отработка отказа базы данных или недостаток места на диске, и выполнения операций ограничения в больших таблицах, даже при ограниченном размере журнала транзакций.

Гибкость создания ограничений с помощью предиката RESUMABLE

Представьте, что вы являетесь администратором базы данных (DBA), ответственным за поддержание производительности и целостности данных большой базы данных ERP. Необходимо добавить ограничение первичного ключа в таблицу AdventureWorks2012.Sales.SalesOrderDetail , чтобы обеспечить согласованность данных, но также необходимо свести к минимуму влияние этой операции на другие транзакции и системные ресурсы.

С помощью функции возобновляемого добавления ограничений таблицы в SQL Server 2022 вы можете достичь этой цели, выполнив операции ALTER TABLE ADD CONSTRAINT с параметром RESUMABLE. Ниже приведены два примера, демонстрирующие использование повторной функции для добавления ограничений таблицы:

-- Example 1: Add a PRIMARY KEY constraint with resumable operation and a MAX_DURATION of 120 minutes
ALTER TABLE [AdventureWorks2022].[Sales].[SalesOrderDetail]
ADD CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] 
	PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 120);

Приостановка и возобновление создания ограничений

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

-- You might want to check if the actual constraint is already paused or still running and what percentage of the operation is complete 
SELECT sql_text, state_desc, percent_complete FROM sys.index_resumable_operations;

-- To pause the constraint run the following command
ALTER INDEX ALL ON [AdventureWorks2012].[Sales].[SalesOrderDetail] PAUSE;

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

ALTER INDEX ALL ON [AdventureWorks2012].[Sales].[SalesOrderDetail] RESUME;

ОЖИДАНИЕ_С_НИЗКИМ_ПРИОРИТЕТОМ

SQL Server 2022 предоставляет три действия для CREATE INDEX онлайн операций индексов с помощью опции WAIT_AT_LOW_PRIORITY при ожидании блокировки стабильности схемы (Sch-S) и изменения схемы (Sch-M). Действие NONE продолжает ожидание блокировки с обычным приоритетом, действие SELF завершает операцию онлайн-индекса без принятия каких-либо действий, а действие BLOCKERS завершает все пользовательские транзакции, блокирующие операцию индекса в сети.

Улучшите производительность запросов с помощью функции WAIT_AT_LOW_PRIORITY.

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

С помощью параметра WAIT_AT_LOW_PRIORITY в SQL Server 2022 вы можете достичь этой цели, указав поведение ожидания блокировки во время создания индекса в сети. Ниже приведены два примера выбора завершения команды создания индекса или команд блокировщика с помощью параметров WAIT_AT_LOW_PRIORITY:

-- Example 1: Terminate the index creation after waiting 10 minutes for low priority locks
CREATE NONCLUSTERED INDEX idx_CustomerID_OrderDate ON [AdventureWorks2012].[Sales].[SalesOrderHeader] (CustomerID, OrderDate)
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = SELF)));

-- Example 2: Terminate blocking transactions after waiting 10 minutes for low priority locks
CREATE NONCLUSTERED INDEX idx_CustomerID_OrderDate ON [AdventureWorks2022].[Sales].[SalesOrderHeader] (CustomerID, OrderDate)
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));

В SQL Server 2022 введена опция WAIT_AT_LOW_PRIORITY в команде CREATE INDEX и добавлена возможность добавления ограничений таблиц с возможностью возобновления, что усиливает контроль над операциями базы данных. Администраторы баз данных могут управлять конфликтами блокировок, уменьшать нагрузку на ресурсы, а также повышать производительность и удобство обслуживания. Эти улучшения подчеркивают приверженность оптимизации SQL Server для современных администраторов баз данных.