The Halloween Problem can have a number of important effects on execution plans. In this final part of the series, we look at the tricks the optimizer can employ to avoid the Halloween Problem when compiling plans for queries that add, change or delete data.
About This Blog
Wednesday 20 February 2013
Monday 18 February 2013
The Halloween Problem – Part 3
The MERGE
statement (introduced in SQL Server 2008) allows us to perform a mixture of INSERT
, UPDATE
, and DELETE
operations using a single statement.
The Halloween Protection issues for MERGE
are mostly a combination of the requirements of the individual operations, but there are some important differences and a couple of interesting optimizations that apply only to MERGE
.
Friday 15 February 2013
The Halloween Problem – Part 2
In the first part of this series, we saw how the Halloween Problem applies to UPDATE
queries. 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 INSERT
and DELETE
statements.
Wednesday 13 February 2013
The Halloween Problem – Part 1
Much has been written over the years about understanding and optimizing SELECT
queries, but rather less about data modification. This series looks at an issue that is specific to INSERT
, UPDATE
, DELETE
and MERGE
queries – the Halloween Problem.
The phrase “Halloween Problem” was originally coined with reference to a SQL UPDATE
query that was supposed to give a 10% raise to every employee who earned less than $25,000. The problem was that the query kept giving 10% raises until everyone earned at least $25,000.
We will see later on in this series that the underlying issue also applies to INSERT
, DELETE
and MERGE
queries, but for this first entry, it will be helpful to examine the UPDATE
problem in a bit of detail.