Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

READ_COMMITED_SNAPSHOT - Pros/Cons

Posted on 2011-09-26
4
Medium Priority
?
879 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 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

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.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
Viewers will learn how the fundamental information of how to create a table.

718 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