Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

READ_COMMITED_SNAPSHOT - Pros/Cons

Posted on 2011-09-26
4
Medium Priority
?
884 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 1200 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 400 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 40

Assisted Solution

by:lcohan
lcohan earned 400 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 40

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

877 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