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.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
