It’s not the usual time of year to think about Halloween, but listening to Erik Darling and Kendra Little recently on the Dear SQL DBA podcast they mentioned a completely new topic to me: Halloween protection.
No, it doesn’t prevent horrors in your code, or shops putting out festive decorations in October. It’s actually much more interesting and thought it was worth sharing for anyone else who hasn’t stumbled upon it.
The Halloween problem
The Halloween problem – so called as it was identified on Halloween – is not SQL Server specific, but a more general database problem. It’s an issue where changes to data move a record to another location where it may be updated again.
Here’s the type of query which could cause the issue:
UPDATE dbo.Employee
SET Salary = Salary * 1.10
WHERE Salary < 25000;
The goal was to provide a 10% increase to any employees earning less than $25k. The data is indexed by Salary. Sounds simple.
The preference for SQL Server is to stream results through an execution plan. If you run a simple SELECT statement from a large table, you’ll start to receive records before the engine has made it anywhere near the end. This avoids memory grants and keeps things flowing. It will only persist records in memory if it needs to – for example a Sort operator where it needs all records to perform the sort.
The impact this has on the query above is that when the update works through the Salary index to check eligible employees, an update would move the employee later in the index. This would mean the record would be re-evaluated again and if it still meets the predicate it would update again – and this could happen repeatedly. It’s similar to how you can see multiple instances of the same record when using a NOLOCK hint.
Without protection, the above query would move everyone’s salaries above $25k, even those who were previously much, much lower.
This is the Halloween problem. Thankfully SQL Server has solutions.
Halloween protection
The solution to this comes in the form of Halloween protection. This is done by separating records that need to be updated from those which have already been updated. This avoids records being evaluated again and subsequent changes.
Problem solved. But at a cost.
The tradeoff with this is that now we have to gather the records first. This is often done by introducing a blocking operator, for example a Sort, or more commonly with a Spool operator. This not only slows down the query, but can require a memory grant, with a risk of spilling to disk.
Below is an example of inserting existing records from a table back into itself. The spool is added so the engine doesn’t pick up records being added and try to add them again:
From my observations – and depending on the type of modification – the engine will choose more optimal (non blocking) plans when possible. For example when performing an UPDATE on a column which is indexed, a Clustered Index Scan may be used instead of an Index Seek. Even though the index may be more selective, a clustered scan reduces the risk of row movement within the index during modifications.
Optimised protection
SQL Server 2025 was originally slated to ship with optimized Halloween protection to make this protection cheaper. However this feature has been temporarily pulled from the release candidate due to a data integrity concern.
The optimisation in this release was designed to avoid the need for blocking operators such as spools, to allow more streaming with fewer resources, and faster.
The way this works is that each statement gets a unique identifier and modified records are stamped with it. If the engine gets to any records which already have that identifier (i.e. they’ve already been processed), they’re ignored. It’s the “oh I’ve seen you before” approach.
The identifier used isn’t new tech either. That already exists as part of versioning to support the Accelerated Database Recovery (ADR) feature which has existed for the last couple of releases. What this does mean though is that ADR needs to be enabled on the database for this new feature to work.
If you’d like to read more about the missing (for now) feature there are more details here.
Wrap up
In this post we’ve looked at the phenomenon of Halloween protection and how the issue is thankfully solved for us.
Whilst the solution does have performance drawbacks, the alternative of not having protection could be disastrous for data integrity. Correctness is a priority. Performance is something we can optimise separately.
Unfortunately we’re going to have to wait a little longer for the optimised protection in SQL Server 2025 to cook. However once it’s available, it’s one less potential performance headache to worry about. Unlike festive decorations in October. That’s still a problem. Bah humbug.
Andy Brownsword 