January 31, 2012 at 11:58 am
If i enable Snapshot Isolation and Row Versioning by running the following alter commands;
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
what would be the result of the stored procedures that specifically use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED command.
My thought is that i should alter all the stored procedures that use it. Thoughts?
January 31, 2012 at 12:11 pm
Correct, otherwise you're not going to use the snapshot. Snapshot Isolation is basically an option for isolation levels, and anywhere that specifically calls out a different one will ignore it. You'll want to look for NOLOCK hints too.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 12:13 pm
yeah, the with (nolocks) hints i know to look out for too. Just need confirmation on dirty read behavior.
thanks alot.
January 31, 2012 at 1:11 pm
Geoff A (1/31/2012)
what would be the result of the stored procedures that specifically use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED command.
They run in read uncommitted isolation level. The SET TRANSACTION ISOLATION LEVEL overrides the default (read committed or read committed snapshot)
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply