SQL Server Halloween effect – Quick look at what is Halloween effect?

Dear Friends,

No, definitely it does not appear like following image in SQL Server but impact on data retrieval can be severe so you can imagine something similar on a lighter note :).


Ok, SQL Server Halloween effect refers to a situation where data moves to a position within result set and consequently could be changed many times. This is driven by data modification and not by data reads. Before data is updated, it must be read first. This is achieved in SQL Server using two cursors; one for read and the other one for write. If data is updated by write cursor before it was read in and there is a possibility that a record will move its original position because of the update and is a potential candidate to be read second time and updated again. We can say, reading data using an index whose key is to be updated is good example of Halloween effect.

As we understand this is not an acceptable situation and best thing is Storage engine on SQL Server has appropriate mechanisms in place to avoid such disasters. To go back a bit; during update SQL Server uses two cursors, one for reading and other for writing or you can call it updating. SQL Server injects a blocking operator such as spool into the plan to make sure data available to the write has been read fully. Though it is not mandatory to use spool but SQL Server uses this operator in general because it is having the lowest cost. It means, data has to be inserted into tempdb before they are used by write cursor to ensure all data is read before any modification. SQL Server looks out for Halloween effect when creating the plan. It introduces blocking operator only when there is a chance of this effect occurring.


You should always remember performance overhead because of the Halloween effect when you decide indexing strategy and it is equally important to consider impact on tempdb when deciding on indexing or performance tuning.

To learn more on Halloween effect and how  SQL Server protects us from it , you can read some excellent posts at Halloween effect issue, Halloween Protection and Halloween Protection – The Complete Series.



Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.