| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2012-02-15 : 13:23:43
|
| I believe both of these settings are OFF by default in SQL Server 2008. I've read that turning them ON can result in substantial performance gains. Curious as to how many of you have experimented with these settings and your thoughts on their use.I wasn't aware of these settings but came across them when researching NOLOCK. These settings seem to be superior to NOLOCK in that you don't get dirty reads.I've read some good documents exploring the theory behind them...http://msdn.microsoft.com/en-us/library/ms188277.aspxBut always like to hear what DBAs think in real world situations.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-15 : 13:38:17
|
| We are using RCSI wherever possible. It has boosted our performance tremendously. My manager (an awesome SQL DBA and also an Oracle DBA) always likes to say how RCSI finally makes SQL Server a competitor to Oracle. RCSI has been available since SQL2k5. It is off by default for backward compatibility reasons, but mainly because you may have to design for RCSI before enabling it. We even set RCSI on the model database to ensure all new databases are created that way.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 13:48:27
|
| You typically wouldn't turn both on, one or the other.Turning read committed snapshot on changes the way that SQL implements the default read committed isolation level from locks to row versions. It can have some side effects, so it does need testing.Turning allow snapshot isolation on allows you to request the snapshot isolation level in procedures or batches (SET TRANSACTION ISOLATION LEVEL SNAPSHOT). That's an isolation level that's equivalent to serialisable, but using row versions not locks. Just turning that setting on provides no benefit, you have to explicitly request the isolation level in your code.--Gail ShawSQL Server MVP |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-02-15 : 13:56:20
|
quote:
you may have to design for RCSI before enabling it.
I'm going to turn it on in staging and see what breaks. We don't explicitly use transactions much. Do you have any advice on what to look for specifically?EDIT: My developer just informed me that all of our new .NET code is transaction based so apparently our app DOES use transactions explicitly more than I thought. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-02-15 : 18:26:45
|
| Awesome. One logistical follow up. It seems you can't turn this setting on without exclusive database access (no other connections). What is the best way to achieve this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-17 : 03:50:01
|
| In my script I set USE MASTER first so I'm not in the database being set (dunno if that is important though, and my script also sets COMPATIBILITY_LEVEL and PAGE_VERIFY CHECKSUM - so not being in the database might relate to those - or something else that was important at the time! |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-02-17 : 16:00:38
|
| I'm about to expose my ignorance - how do I check if it is on? |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-02-17 : 16:13:15
|
| I cured my own ignorance...SELECT is_read_committed_snapshot_on FROMsys.databases WHERE name= 'DBName' |
|
|
|
|
Read committed Snapshot VS Snapshot Isolation Leve