March 19, 2015 at 9:52 am
Hi all, I just found out that in one of many DBs we have, the isolation level is set to Read Committed Snapshot and all the other DB, isolation level is set to Read Committed. I don't know the major difference between them. Issue is that we experience blocking quite often and one of the DBA changed the setting to Read Committed Snapshot, however, it didn't help at all, but I am just curious and wondering what else can be done and if there is any major difference between those 2.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 19, 2015 at 9:54 am
There's massive differences between the two. Start with the sections on isolation levels in the SQL Books Online, there's a lot of information there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 10:09 am
Interesting. If the db is "READ_COMMITTED_SNAPSHOT ON", that will typically reduce locking/blocking considerably. When tasks are in READ_COMMITTED isolation level -- which is the default -- the only blocking that can still occur is modifies (DELETE|INSERT|UPDATE) blocking other modifies, but that's inevitable in any relational db. No code changes are required to get this effect. Without RCS ON, readers can be blocked by modifiers, and modifiers can be blocked by readers. With RCS ON, they no longer block each other. To accomplish that, SQL uses additional resources to insure that unmodified rows are available to extisting readers even if the row is later modified.
There's a different "level" of snapshot if the db is set to "ALLOW_SNAPSHOT_ISOLATION ON", but that requires explicitly setting the task's isolation level to ALLOW_SNAPSHOT_ISOLATION.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 19, 2015 at 10:23 am
We use SolarWinds DPA tool to monitor our environment. Everyday I see alerts regarding the blocking. For example, Query 1 gets executed 3700 times and has 17 hours of blocking (cumulative). So even with RCS on, we are still seeing blocking.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 19, 2015 at 10:41 am
Yes, and?
Read committed doesn't mean no blocking. It means that readers don't block writers, that's all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 11:05 am
I think I got it. Thanks!
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 19, 2015 at 11:19 am
Readers don't block writers, and writers don't block readers. Without RCS on, they do. This is a wonderful option, but it comes with a fairly high overhead cost, increasing as activity increases. [Btw, Oracle does the equivalent of RCS on, but it's not an option, you must endure the extra overhead.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply