Новое в SQL Server 2025: Оптимизация Halloween Protection

Автор: Dimitri Furman, MICROSOFT, 19 мая 2025г. SQL Server 2025: introducing optimized Halloween protection

Оптимизированная в SQL Server 2025 (начиная с CTP 2.0) защита «Halloween Protection», сокращает потребление места в tempdb и повышает производительность запросов, снижает потребление ресурсов, а также решает саму проблему Хэллоуина. При этом не требуется вносить какие-либо изменения в запросы пользователей. Тестирование ряда выбранных для примера приложений показали что утилизация процессоров и время исполнения таких запросов сократились примерно наполовину, а также полностью исчезло использование места в базе данных tempdb.

Проблема Хэллоуина

Halloween Protection получил такое название потому, что эта проблема была обнаружена в Хэллоуин в 1976 года. Возникает когда оператор DML изменяет данные так, что один и тот же оператор ошибочно затрагивает одну и ту же строку несколько раз. Традиционно SQL Server защищает операторы DML от проблемы Хэллоуина, добавляя в план запроса оператор Spool или полагается на другой блокирующий оператор, уже имеющийся в плане, например, Sort или Hash Match. Если используется оператор Spool то, прежде чем вносить какие-либо изменения в данные в таблице, он временно материализует копию данных которые нужно изменить.

Хотя Spool позволяет избежать проблемы Хэллоуина, у него есть свои недостатки:

·       Для буферизации требуются дополнительные ресурсы: место в базе данных tempdb, дисковый ввод-вывод, память и процессор.

·       Работа следующих операторов в плане запроса блокируется до тех пор, пока данные не будут полностью записаны в буфер.

·       Буферизация усложняет план запроса, что может привести к тому, что оптимизатор запросов сгенерирует менее оптимальный план.

Optimized Halloween Protection устраняет эти недостатки, делая ненужным использование оператора Spool.

Как это работает

При включении Accelerated Database Recovery (ADR) каждый оператор в транзакции получает уникальный идентификатор: «Nest ID». Когда строка изменяется оператором DML, она помечается этим идентификатором. Это необходимо для обеспечения семантики транзакции ACID с ADR.

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

Как использовать Optimized Halloween Protection

Для включения Optimized Halloween Protection необходимы следующие предварительные условия:

·       Для базы данных должна быть включена функция ADR.

·       База данных должна использовать уровень совместимости 170.

·       Необходимо включить конфигурацию OPTIMIZED_HALLOWEEN_PROTECTION на уровне базы данных.

Конфигурация OPTIMIZED_HALLOWEEN_PROTECTION действует только на уровне базы и включена по умолчанию. Это означает что, если для базы данных с уровнем совместимости 170 включить ADR, она сможет использовать Optimized Halloween Protection. Включается всё это следующими инструкциями:

ALTER DATABASE [<database-name-placeholder>] SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON;

Также можно включить или отключить эту защиту на уровне запроса, используя подсказки ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION и DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION. Подсказки можно указывать как непосредственно в запросе, так и через подсказки Query Store. Эти подсказки работают на любом уровне совместимости и имеют приоритет над конфигурацией OPTIMIZED_HALLOWEEN_PROTECTION в области базы данных.

Для отражения использования оператором в плане запроса этой защиты, свойство оператора OptimizedHalloweenProtectionUsed в XML плане запроса устанавливается в True.

Более подробную информацию см. в документации по Оптимизированной Защите Хэллоуина.

 

Примечание переводчика: У ADR есть не только преимущества, но и накладные расходы. О некоторых таких особенностях рассказывается тут: Index Rebuilds Make Even Less Sense with ADR & RCSI

 

Приложение

Сценарий ниже поможет увидеть, как оптимизированная защита Хэллоуина удаляет Spool в плане запроса, сокращает использование tempdb, утилизацию ЦПУ и продолжительность запроса.
/*
Requires the WideWorldImporters sample database.
SQL Server backup: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak
Bacpac: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac
*/
/* Ensure that optimized Halloween protection prerequisites are in place */
ALTER DATABASE WideWorldImporters
    SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE WideWorldImporters
    SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION
    SET OPTIMIZED_HALLOWEEN_PROTECTION = ON;
GO
/* Validate configuration */
SELECT d.compatibility_level,
       d.is_accelerated_database_recovery_on,
       dsc.name,
       dsc.value
FROM sys.database_scoped_configurations AS dsc
CROSS JOIN sys.databases AS d
WHERE dsc.name = 'OPTIMIZED_HALLOWEEN_PROTECTION'
      AND
      d.name = DB_NAME();
GO
 
/* Create the test table and add data */
DROP TABLE IF EXISTS dbo.OptimizedHPDemo;
 
BEGIN TRANSACTION;
 
SELECT *
INTO dbo.OptimizedHPDemo
FROM Sales.Invoices
 
ALTER TABLE dbo.OptimizedHPDemo
ADD CONSTRAINT PK_OptimizedHPDemo
PRIMARY KEY CLUSTERED (InvoiceID)
ON USERDATA;
 
COMMIT;
GO
 
/* Ensure that Query Store is enabled and is capturing all queries */
ALTER DATABASE WideWorldImporters
    SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
 
/* Empty Query Store to start with a clean slate */
ALTER DATABASE WideWorldImporters
    SET QUERY_STORE CLEAR;
GO
 
/* Disable optimized Halloween protection as the baseline */
ALTER DATABASE SCOPED CONFIGURATION
    SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF;
GO
 
/*
Insert data selecting from the same table.
This requires Halloween protection so that
the same row cannot be selected and inserted repeatedly.
*/
BEGIN TRANSACTION;
 
INSERT INTO dbo.OptimizedHPDemo
(
InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID,
SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason,
Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition,
ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen
)
SELECT InvoiceID + 1000000 AS InvoiceID,
       CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID,
       SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason,
       Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition,
       ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen
FROM dbo.OptimizedHPDemo;
 
ROLLBACK;
GO
 
/*
Enable optimized Halloween protection.
Execute the following statement in its own batch.
*/
ALTER DATABASE SCOPED CONFIGURATION
    SET OPTIMIZED_HALLOWEEN_PROTECTION = ON;
GO
 
/* Execute the same query again */
BEGIN TRANSACTION;
 
INSERT INTO dbo.OptimizedHPDemo
(
InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID,
SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason,
Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition,
ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen
)
SELECT InvoiceID + 1000000 AS InvoiceID,
       CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID,
       SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason,
       Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition,
       ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen
FROM dbo.OptimizedHPDemo;
 
ROLLBACK;
GO
 
/*
Examine query runtime statistics and plans
for the two executions of the same query.
*/
SELECT q.query_id,
       q.query_hash,
       qt.query_sql_text,
       p.plan_id,
       rs.count_executions,
       rs.avg_tempdb_space_used * 8 / 1024. AS tempdb_space_mb,
       FORMAT(rs.avg_cpu_time / 1000., 'N0') AS avg_cpu_time_ms,
       FORMAT(rs.avg_duration / 1000., 'N0') AS avg_duration_ms,
       TRY_CAST(p.query_plan AS xml) AS xml_query_plan
FROM sys.query_store_runtime_stats AS rs
INNER JOIN sys.query_store_plan AS p
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query AS q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS qt
ON q.query_text_id = qt.query_text_id
WHERE q.query_hash = 0xC6ADB023512BBCCC;
 
/*
For the second execution with optimized Halloween protection:
1. tempdb space usage is zero
2. CPU time and duration are reduced by about 50%
3. The Clustered Index Insert operator in the query plan has
   the OptimizedHalloweenProtection property set to True
*/