Halloween Protection – The Complete Series

Paul White — Wed, 20 Feb 2013 23:29:42 GMT

I have just published a four-part series for SQLPerformance.com on the Halloween Problem. Some of you will never have heard of this issue, and those that have might associate it only with T-SQL UPDATE queries. In fact, the Halloween problem affects execution plans for INSERT, UPDATE, DELETE and MERGE statements.

This is a topic I have been meaning to write about properly for years, ever since I read Craig Freedman’s 2008 blog post on the topic, which ended with the cryptic comment:

“…although I've used update statements for all of the examples in this post, some insert and delete statements also require Halloween protection, but I'll save that topic for a future post.”

That future post never materialized, sadly, so I thought I would have a go. The four parts of the series are summarized and linked below, I hope you find the material interesting.

Part 1 – The Halloween Problem and UPDATE statements

The SQL standard and three-phase separation
Logical update processing
Pipelined execution
The Halloween problem
Avoiding the problem in UPDATE statements

Part 2 – The Halloween Problem in INSERT and DELETE queries

INSERT examples
DELETE examples
Constraint checking and phase separation

Part 3 – Halloween Problem optimizations for MERGE

MERGE contains several optimizations the other DML statements do not
Hole-filling with merge join
Hole-filling with nested loops
Avoiding an extra B-tree navigation
Avoiding the join

Part 4 – The Halloween Problem and the Query Optimizer

Early optimization approaches
The SQL Server optimizer approach
The case of the redundant sort
HP levels and properties
Plan changes for Halloween Protection
Non-spool options
Row versioning
Heaps and forwarded records
T-SQL functions

As always, I appreciate your comments and feedback.

Paul White
@SQL_Kiwi
SQLkiwi@gmail.com

Search results matching tag 'Inserts'

Halloween Protection – The Complete Series

Part 1 – The Halloween Problem and UPDATE statements

Part 2 – The Halloween Problem in INSERT and DELETE queries

Part 3 – Halloween Problem optimizations for MERGE

Part 4 – The Halloween Problem and the Query Optimizer