Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why use SNAPSHOT isolation with Change Tracking? Expand / Collapse
Author
Message
Posted Wednesday, December 23, 2009 6:17 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 08, 2013 5:45 PM
Points: 7, Visits: 109
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
Post #838823
Posted Monday, December 28, 2009 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 03, 2014 1:23 PM
Points: 4, Visits: 82
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
Post #839594
Posted Sunday, January 03, 2010 10:29 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 08, 2013 5:45 PM
Points: 7, Visits: 109
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
Post #841300
Posted Wednesday, September 19, 2012 5:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 18, 2014 11:58 AM
Points: 18, Visits: 510
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
Post #1361697
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse