setting isolation level read uncommited

Question

setting isolation level read uncommited

prakash.gr

SSC-Addicted

Points: 428
More actions
November 25, 2008 at 11:15 pm

#75372

Hi,
first i am new to sql server:
server version is sql server 2000
we used to get lot of blocking session and we thought of use isolation level read uncommited instead of read commited(default behaviour)
could any body advise me to set this parameter in database level
thanks
prakash

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

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

Mike John SSCertifiable Points: 7216 More actions · Answer 1

I am not aware of any global setting for this and frankly would not want one. Setting read uncommitted can on occasions be one way to help resolve locking problems, but it can sometimes cause various inconsistency issues so is not the "magic button" that sometimes peopel think it is. Itzik Ben-Gan wrote an excellent article on the pitfalls of read uncommitted for SQL Server magazine. It was available on sqlmag.com. I would try and understand the root cause of why you had locking issues rather than just set read uncommitted.

Mike John

prakash.gr SSC-Addicted Points: 428 More actions · Answer 2

thx mike for your reply,

i do agree with your points. but my frontend is not critical like banking application where inconsistency effcts adversely.

since i am new to sql server u r help required

i thought of set this parameter your input is greately appreciated.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

or

ALTER DATABASE agentlog

SET READ UNCOMMITTED ON;

i felt this parameter is effecting only for select statements.

Thanks

Prakash

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

prakash.gr (11/26/2008)
i do agree with your points. but my frontend is not critical like banking application where inconsistency effcts adversely.

I don't know many apps where missing a bunch of rows or reading a bunch of rows twice (or more) is not an issue.

Rather than trying to hide the effects of blocking, do some optimisation and have a look at your indexes. By doing that, unless you;re running a massive, very, very heavily used server, you should be able to eliminate blocking.

And there's no setting that will make all logins have the read-uncommitted isolation level.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prakash.gr SSC-Addicted Points: 428 More actions · Answer 4

thx Gail Shaw for u r reply.

i will concentrate on optimization. apart from index lookup where i need to concentrate to optimize the server to improve performance.

Thanks

Prakash

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Optimise the queries so that the run as fast as possible and can use the indexes that you create.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass