Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-09-15 : 11:23:13
|
There have been several occasions where I was able to eliminate deadlock problems by setting the database to READ_COMMITTED_SHAPSHOT without having to change any code. This was especially useful for a vendor supplied application where there was zero chance of getting the vendor to change the code.It has been fairly painless fix for me, so far. Has anyone set on READ_COMMITTED_SHAPSHOT and experienced any problems with using it?CODO ERGO SUM |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-15 : 18:40:38
|
We are using READ_COMMITTED_SNAPSHOT in most of our production systems. This is one of the first things that Microsoft checks when you call them asking for help with performance or deadlocks. Switching to this isolation level has dramatically increased performance for us.It eliminated deadlocks for us when the deadlocks involved a read blocker and a write blocker. It does not eliminate deadlocks when both are write blockers. READ_COMMITTED_SNAPSHOT is the recommended isolation level for all systems. I'm not sure why they chose to keep READ_COMMITTED as the default, perhaps for backwards compatibility.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-09-16 : 02:23:59
|
There is no option for DATABASEPROPERTY() nor DATABASEPROPERTYEX() function to check if the option is enabled.SELECT name, is_read_committed_snapshot_onFROM sys.databases N 56°04'39.26"E 12°55'05.63" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-09-16 : 09:30:23
|
Yes, I am referring to this:alter database MyDatabase set READ_COMMITTED_SNAPSHOT on; My question isn’t about how to set it on or how to determine if it is already on. I just wanted to know if anyone has had any problems due to having READ_COMMITTED_SNAPSHOT on. Performance issues, etc.?CODO ERGO SUM |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|