Solved

READ_COMMITED_SNAPSHOT - Pros/Cons

Posted on 2011-09-26
4
861 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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