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.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Deadlock elimination with READ_COMMITTED_SHAPSHOT

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-16 : 02:16:14
Are you referring to this option?
http://msdn.microsoft.com/en-us/library/ms175095.aspx


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

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_on
FROM sys.databases


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-16 : 13:16:04
We haven't had any issues with READ_COMMITTED_SNAPSHOT. We've had the exact opposite, only improvements with it.

I would recommend that everyone use it for production databases, but of course test first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://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."
Go to Top of Page
   

- Advertisement -