Solved

READ_COMMITED_SNAPSHOT - Pros/Cons

Posted on 2011-09-26
4
873 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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