Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why use SNAPSHOT isolation with Change Tracking?


Why use SNAPSHOT isolation with Change Tracking?

Author
Message
GuitarGuy
GuitarGuy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 111
Microsoft has strongly recommended that we use SNAPSHOT isolation with Change Tracking because of the improved row versioning. Can anybody tell me what benefits this has over Read Committed?

Cheers,
Tim
Mel Lusk
Mel Lusk
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 88
READ COMMITTED works differently in SQL Server than it does in other databases such as Oracle. With SQL Server, READ COMMITTED works almost similar to SERIALIZABLE. Although transactions that read data that is currently being updated is suppossed to return the data in its committed state, what really happens in SQL Server is that the data is blocked.

Snapshot isolation will create a duplicate row of the data being updated in TEMPDB that can be read by other transactions during the update process. This will reduce the amount of blocking that takes place during heavy OLTP loads. I've used it in the past with great success.

MSDN has a good article on the subject:

http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx
GuitarGuy
GuitarGuy
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 111
Cheers for the reply Mel. I was wondering what the benefits are specifically in relation to Change Tracking. As far as I can work out, it could be something to do with the reads not acquiring locks and therefore not blocking modifications, and consistency between multiple select statements but I was hoping that somebody clever than me could confirm this and maybe add to it too. Or more likely, give me the real reasons! :-)

Cheers,
Tim
AlBran LVP
AlBran LVP
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 672
I also didn't understand what would happen if I didn't use snapshot isolation with my Change Tracking process, but this helped.
http://technet.microsoft.com/en-us/magazine/ee914612.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search