Solved

READ_COMMITED_SNAPSHOT - Pros/Cons

Posted on 2011-09-26
4
854 Views
Last Modified: 2012-05-12
In a previous question regarding sql read/write performance, a member suggested we switch the database to READ_COMMITED_SNAPSHOT. We tried this on a test server, and it appeared fine; but I don't really understand much about the implications, and am wanting to know a bit more before switching to the live environment.

Basically, we have a data table that gets hit really hard - lots and lots of inserts, lots of deletes, lots of reads. The quality of the data is not as important as you might think - ie, if someone gets a value from a second ago, it's not the end of the world. It is important that the data is up-to-date; and our 3rd party software has been queueing when writing when there are a lot of connections reading from the table.

Could anyone explain any negative impacts switching to READ_COMMITED_SNAPSHOT - and what doing this actually does?

Sorry it's a bit vague; there's plenty on Google, but I wanted to hear from someone who actually knew the reality rather than what the text-book says.

Cheers,

Phil
0
Comment
Question by:Fuzzyfish1000
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 300 total points
ID: 36600518
READ_COMMITTED_SNAPSHOT is sql server's implementation for ROW VERSIONING.  The goal is to increase throughput by reducing blocking.  With READ_COMMITTED isolation level, shared locks are issued when resources are read.  The shared locks block other processes attempting to update the locked resources.  They also block read access to resources that are in the process of being updated.  In other words:  WRITERS block READERS and READERS block WRITERS.  

With ROW_VERSIONING that all changes to a different model where WRITERS do not block READERS and READERS do not block WRITERS.  More throughput! This is accomplished by maintaining "before images" in tempdb.  The before images are used to deliver a consistent data view to an application the way it existed at the start of a transaction.  Contrast that with delivering a consistent data view by waiting for other transactions to commit.  

Does it work.  Yes.  Does it work well. Yes.  In fact, Oracle believes so strongly in the Row Versioning model that no other model is supported. The drawbacks are the obvious overhead and the implications of long-running transactions.

Use of Row Versioning gives you an immediate view of old data rather than wait for "new" data.  For short-running transactions, that's insignificant.  But long-running transactions that can be important, especially when there is time-sensitivity involved.  Imagine some kind of transaction that takes 10 minutes.  Another transaction will then see data that is up to 10 minutes old.  Would you prefer to work with the old data or wait up to 10 minutes to work with the new?  

 





 


 
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 36602150
Additionally to the above comment, keep in mind that SQL Server row versionning is done on disk rather than on memory (Oracle), so make sure that you have a separate RAID array for hosting tempdb.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 100 total points
ID: 36710609
I know ther may be lots of screaming however...just because of the comment above (SQL Server row versionning is done on disk rather than on memory) you could achieve similar thing by using query hints - WITH (NOLOCK) clause most importatnt in your SELECT's and make sure the table is partitioned plus you have good indexes.

I'm saying this because "Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb." so your ROW versioning is as good and fast as your TEMPDB is - make sure you have one tempdb phisycal file per socket up to max 4) and becasue "You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement." but you may NOT want to do it for the entire database.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 36710628
Sorry I should have included the link in support of the quotes from my prior comment:
Using Snapshot Isolation: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=VS.80).aspx

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now