Databases Reference
In-Depth Information
NOTE
For more details on the double read, please see the blog post from Craig
Halloween Ef ect
The
Halloween effect
refers to a scenario in which data moves position within the result set and
consequently could be changed multiple times. This effect is different from the double read because
it is driven by data modii cation, rather than read queries.
In order to perform an update, the data must be read i rst. This is performed using two cursors:
one for the read and the other for the write. If the data is updated by the write cursor before all the
data was read in, then it is possible that a row will move position (courtesy of the update), poten-
tially be read a second time, and consequently be updated again. In theory, this could go on forever.
Reading the data using an index whose key is going to be updated by the query is an example of the
Halloween effect.
This scenario is obviously highly undesirable, and thankfully the Storage Engine in SQL Server
protects against it. As mentioned, SQL Server uses two cursors during an update: one to perform
the read and another to perform the write. To ensure that the data available to the write has been
read fully, SQL Server needs to inject a blocking operator such as a spool into the plan. It doesn't
have to be the spool, but this operator is commonly selected because it invariably has the lowest cost
attached to it. That said, it still isn't very efi cient, as it means all the data has to be inserted into
tempdb before it can be used by the write cursor. It does ensure that all the data is read before any
modii cations take place.
To achieve a greater level of efi ciency, SQL Server actually looks out for the Halloween effect
problem when creating the plan. It introduces the blocking operator only when there is a chance
of the Halloween effect occurring. Even then it adds an extra one only if no blocking operator is
already present in the plan performing this function.
In most update scenarios the index is used to locate data, and other non-key columns are updated
in the table. You wouldn't normally expect the key to be frequently updated as well, so being able to
remove the blocking operator is an important optimization.
It is worth remembering the performance penalty of the Halloween effect when deciding on your
indexing strategy. Perhaps that index you were thinking of adding isn't such a great idea after all.
When index or performance tuning, it is always worthwhile to keep an eye on the impact that your
changes have on tempdb.
NOTE
To learn more details about how SQL Server protects you from the
Halloween effect, please see another excellent blog post from Craig Freedman
Search WWH ::
Custom Search