URL: https://techcommunity.microsoft.com/tag/sqlserverqueryprocessing?nodeId=board%3ASQLServer
Executive summary
Optimized Halloween protection, available in the public preview of SQL Server 2025 starting with the CTP 2.0 release, reduces tempdb space consumption and improves query performance by redesigning the way the database engine solves the Halloween problem.
An example in the appendix shows CPU and elapsed time of a query reduced by about 50% while eliminating all tempdb space consumption.
Update 2025-09-02
During public preview of SQL Server 2025, we identified a potential data integrity issue that might occur if optimized Halloween protection is enabled. While the probability of encountering this issue is low, we take data integrity seriously. Therefore, we temporarily removed optimized Halloween protection from SQL Server 2025, starting with the RC 0 release.
The fix for this issue is in progress. In the coming months, we plan to make optimized Halloween protection available in Azure SQL Database and Azure SQL Managed Instance with the always-up-to-date update policy. Enabling optimized Halloween protection in a future SQL Server 2025 update is under consideration as well.
The Halloween problem
The Halloween problem, named so because it was discovered on Halloween in 1976, occurs when a data modification language (DML) statement changes data in such a way that the same statement unexpectedly processes the same row more than once.
Traditionally, the SQL Server database engine protects DML statements from the Halloween problem by introducing a spool operator in the query plan, or by taking advantage of another blocking operator already present in the plan, such as a sort or a hash match.
If a spool operator is used, it creates a temporary copy of the data to be modified before any modifications are made to the data in the table.
While the protection spool avoids the Halloween problem, it comes with downsides:
The spool requires extra resources: space in tempdb, disk I/O, memory, and CPU.
Statement processing by the downstream query operators is blocked until the data is fully written into the spool.
The spool adds query plan complexity that can cause the query optimizer to generate a less optimal plan.
Optimized Halloween protection removes these downsides by making the spool operator unnecessary.
How it works
When accelerated database recovery (ADR) is enabled, each statement in a transaction obtains a unique statement identifier, known as nest ID. As each row is modified by a DML statement, it is stamped with the nest ID of the statement. This is required to provide the ACID transaction semantics with ADR.
During DML statement processing, when the storage engine reads the data, it skips any row that has the same nest ID as the current DML statement. This means that the query processor doesn't see the rows already processed by the statement, therefore avoiding the Halloween problem.
How to use optimized Halloween protection
To enable optimized Halloween protection for a database, the following prerequisites are required:
ADR must be enabled on the database.
The database must use compatibility level 170.
The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration must be enabled.
The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration is enabled by default. This means that when you enable ADR for a database using compatibility level 170, it will use optimized Halloween protection.
You can ensure that a database uses optimized Halloween protection by executing the following statements:
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;
You can also enable and disable optimized Halloween protection at the query level by using the ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION and DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION query hints, either directly in the query, or via Query Store hints. These hints work under any compatibility level and take precedence over the OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration.
When optimized Halloween protection is used for an operator in the query plan, the OptimizedHalloweenProtectionUsed property of the operator in the XML query plan is set to True.
For more details, see optimized Halloween protection in documentation.
Conclusion
Optimized Halloween protection is another Intelligent Query Processing feature that improves query performance and reduces resource consumption when you upgrade to SQL Server 2025, without having to make any changes to your query workloads.
We are looking forward to your feedback about this and other features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at intelligentqp@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback.
Appendix
The following script shows how optimized Halloween protection removes the protection spool in the query plan, and reduces tempdb usage, CPU time, and duration when enabled.
/*
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
*/
👁 Image One question that we hear quite often is why we chose to capitalize the first three letters when abbreviating this feature sometimes, and at other times, we chose not to. For example, why only abbreviate it like PSP optimization, and not just PSPO?
As one of the new members of the growing Intelligent Query Processing (IQP) family, PSPO has had a few growing pains. But PSP is continuing to take steps towards addressing one of the most common performance challenges in SQL Server, bad parameter sniffing. Not all parameter sniffing is bad, and PSPO, while in its introduction to the database world, has taken a more conservative approach when it comes to dealing with bad parameter sniffing. But this blog post is not so much about parameter sniffing, or PSP optimization fundamentals. For that, please feel free to refer to a high-level introduction to the feature - Intelligent Query Processing: feature family additions - Microsoft SQL Server Blog, afterwards head over to our official documentation page for a slightly deeper dive. Ok, if this post is not about what PSPO is, or what PSP is not, or what the future plans are for PSPO (i.e. when will it expand its current scope in order to address other types of workloads); then why does this blog post even exist? Great questions, please continue to ask them and do not forget to even post them and more to our Ideas site (https://aka.ms/sqlfeedback).
This blog post is here to share how PSPO keeps getting better with your feedback. In the previous paragraph, there were some references to our official documentation page and the Azure Ideas site. One of the things that we have added to the documentation page for PSP is a Known Issues section. This was added to ensure that the SQL community was aware of major issues that we have been tracking and fixing for the current version of PSPO. One issue that we have been working on has also been tracked since April of 2023 on the Azure Ideas site - sql data store is causing errors on sql server 2022 · Community (azure.com).
Ok, why is that relevant to this blog post and what does this have to do with PSP optimization getting better? Well, starting with the release of Cumulative Update 4 for SQL Server 2022, we have released several fixes for some of the errors that some of you in the SQL community may have encountered while using PSPO. The optimal word is some, however, we are continually improving the product and are continuing to address all the issues that we are aware of, post Cumulative Update 4. For example, one of the race conditions as described in the known issues sections of the documentation has been fixed.
**Update**
Cumulative Update 7 for SQL Server 2022 includes an additional fix that addresses the known issues with PSP and its integration with Query Store.
As we continue to make the Parameter Sensitive Plan Optimization feature more reliable and more robust, I hope you find this blog helpful and that you will continue this journey along with us in alleviating the negative performance effects of bad parameter sniffing. Oh, and before I forget, we still strongly recommend that you apply the latest Cumulative Updates for SQL Server as we ceaselessly improve the supportability, reliability, and manageability of the product.
