Paul White continues a series on the Halloween Problem. Part 2 looks at insert and delete statements:
In the first part of this series, we saw how the Halloween Problem applies to
UPDATEqueries. To recap briefly, the problem was that an index used to locate records to update had its keys modified by the update operation itself (another good reason to use included columns in an index rather than extending the keys). The query optimizer introduced an Eager Table Spool operator to separate the reading and writing sides of the execution plan to avoid the problem. In this post, we will see how the same underlying issue can affect
In the right circumstances, the SQL Server optimizer can recognize that the
MERGEstatement is hole-filling, which is just another way of saying that the statement only adds rows where there is an existing gap in the target table’s key.
For this optimization to be applied, the values used in the
WHEN NOT MATCHED BY TARGETclause must exactly match the
ONpart of the
USINGclause. Also, the target table must have a unique key (a requirement satisfied by the
PRIMARY KEYin the present case).
Where these requirements are met, the
MERGEstatement does not require protection from the Halloween Problem.
If only it weren’t busted in so many other ways!
The SQL Server optimizer has specific features that allow it to reason about the level of Halloween Protection (HP) required at each point in the query plan, and the detailed effect each operator has. These extra features are incorporated into the same property framework the optimizer uses to keep track of hundreds of other important bits of information during its search activities.
Each operator has a required HP property and a delivered HP property. The required property indicates the level of HP needed at that point in the tree for correct results. The delivered property reflects the HP provided by the current operator and the cumulative HP effects provided by its subtree.
This last one goes into some nice detail.