isolation level

Question

isolation level

Tara-1044200

SSCoach

Points: 15951
More actions
April 29, 2009 at 2:13 pm

#133604

I see a performance boost when i use READ UNCOMMITTED and want to use it on all my servers where database are used only for reading, we do not write anything there, all updates will be done in staging server and pushed to production and so i am not worried about dirty reads.
How would i setup READ UNCOMMITTED at server level or any other ways so that evry user log into the server and tried to read the data it shud be under this isolation level instead of explicilty setting this option for each session.

Viewing 11 posts - 1 through 10 (of 10 total)

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

David-1011936 Ten Centuries Points: 1378 More actions · Answer 1

Tara (4/29/2009)
How would i setup READ UNCOMMITTED at server level or any other ways so that evry user log into the server and tried to read the data it shud be under this isolation level instead of explicilty setting this option for each session.

Why not just set the database(s) to read-only, in that case.

ALTER DATBASE your_database_name_here SET READ_ONLY

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

If you don't do any changes at all, mark the databases as readonly and SQL will take no locks when querying it.

There's no other way to make read uncommitted the default, for good reason. It can result in incorrect results if the tables are actually been modified.

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

Donn Polix Default port Points: 1453 More actions · Answer 3

I'm not sure if you can achieve it on a server or db level setting, I'd be interested to know as well,. but one thing you can do to help speed up reads a bit is to create a readonly filegroup and move your data there.

_____________
Donn Policarpio

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

Does it mean unless i make the database READ ONLY, i can set the isolation at server level or database level??

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

The only isolation level that you can set at a database level is Read Committed Snapshot (the optimistic concurrency version of read committed). Other than that, isolation levels are set on a connection level and have to be done for each connection.

If a DB is read only, there's no need for SQL to take locks as nothing can change. Hence any queries against that DB essentially run as if they were in 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

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

when you say connection level how do i do that? do u mean session level. can i set someone to this isolation when he logs in with rdp session.

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

That's on a SQL connetion, not a rdp connection.

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

noeld SSC Guru Points: 96590 More actions · Answer 8

I don't understand why you want to do that but here is a shot.

If that person using RDP is going to use SSMS you can set the default Isolation level for the connections to Read uncommitted under:

Tools->Options->Query Execution->SQL Server->Advanced -> SET TRANSACTION ISOLATION LEVEL

* Noel

Tara-1044200 SSCoach Points: 15951 More actions · Answer 9

noeld

i think that is waht i was looking but when i set there does it apply to whole sql server on that box?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 10

Nope, it justs applies to connections opened thru tht SSMS.

MJ