snapshot isolation and deadlocks

Question

snapshot isolation and deadlocks

Indianrock

SSC-Insane

Points: 20333
More actions
July 16, 2010 at 1:26 pm

#141319

I'm considering turning on snapshot isolation for a number of databases, but the one in question at the moment is a Microsoft CRM database. Why should one turn that on. What are the downsides, if any? I am seeing an occasional deadlock.
select name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on,
recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,
is_auto_create_stats_on,is_auto_update_stats_on,is_auto_update_stats_async_on,
is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,log_reuse_wait,log_reuse_wait_desc,
is_date_correlation_on from sys.databases

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 1

I think this is more for a development environment where if you want to rollback to a previous state it is easier to do than perhaps a full restore. At least that's what I've read. If this is a production database I'd stick with a traditional backup plan.

Of course that's just one person's thoughts 🙂

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

I'm talking about the following: "When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations."

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 3

Oh, I'm sorry I should have read that more thoroughly oops!

Sorry I do not have any experience with this option 🙁 I try to use Read Uncommitted to avoid blocking so this is a new one for me. I'll have to read more.

Hawkeye_DBA SSCarpal Tunnel Points: 4365 More actions · Answer 4

Here's an article about the Snapshot Isolation Level. It looks like the preference is Read Committed in most environments however.

http://www.sql-server-performance.com/articles/per/new_isolation_levels_p2.aspx

It states that it will use tempdb more heavily so your performance bottleneck may be with tempdb. If you can isolate it on it's own RAID 1 that may help improve performance.

Hope the article helps!

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Hi

Read committed snapshot isolation may result in Non-repeatable and phantom reads and also increased TempDB utilization.

Thank You,

Best Regards,

SQLBuddy

Indianrock SSC-Insane Points: 20333 More actions · Answer 6

I guess we'll have to decide if more tempdb usage etc is preferred to the blocking/deadlocks we're currently getting.