SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


READ UNCOMMITTED vs. NOLOCK


READ UNCOMMITTED vs. NOLOCK

Author
Message
CountCet
CountCet
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 56
Our application has almost no updates to most tables. Only Inserts and Reads. I changed each query to use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" instead of using WITH (NOLOCK) on each table. Is there any real difference in doing this?

I currently see many spids blocking their own commands and going into a suspended state. I am currently trying to find a solution. Is there a better solution than the one we currently use?
rgillings
rgillings
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 239
My understading is that these are the same.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99601 Visits: 33014
Processes normally block and lock resources as they work. If you're hitting excessive blocking & locking than you should address the code and the structure, not using query & table hints. Reading uncommitted data will lead to bad data being returned. Not simply data is in the process of being updated, but you'll get extra rows or miss rows as the data changes under your query. Extra values or missing values are going to seriously negatively impact most businesses.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228513 Visits: 46342
ReadUncommitted and NOLOCK have the same effect. Only difference is that specifying SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED affects every single table within a select statement until the connection closes or a different isolation level is specified. NOLOCK applies just to the table the hint follows.

Maybe I'm missing something, but if you have virtually no updates, you shouldn't have any blocking. Select statements don't block other select statements, unless strange locking hints are applied. What's the cause of the blocking?

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


Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19541 Visits: 10042
If you are seeing processes block themselves, you need to review what type of waits you are encountering. My guess would be that they are CXPACKET waits - which means the queries are using parallel plans.

To reduce the parallel plans and blocking you need to focus on fixing the queries.

You can also reduce the impact by setting the max degree of parallelism setting for the server to 1/2 the number of cores. Reduce even further if you are still seeing a lot of parallel plans and they are causing issues.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

CountCet
CountCet
Old Hand
Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)Old Hand (341 reputation)

Group: General Forum Members
Points: 341 Visits: 56
Thanks for the input everyone.
Tara-1044200
Tara-1044200
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3543 Visits: 3077
can we set read uncommitted isolation level by default on server so that every query will use that isolation level instead of explicitly giving in the query.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228513 Visits: 46342
Asked and (partially) answered here
http://www.sqlservercentral.com/Forums/Topic707203-146-1.aspx

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
Tara-1044200
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3543 Visits: 3077
I was just wondering how do i use READ UNCOMMITTED isolation level when i do bulk/fast load using ssis packages, as we have to import our production data (no updates, only inserts happen) into a different server. I want to use this isolation because i see a lot of improvement in perofrmance too, may be bacause they dont need to wait for loack for reading data from production.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228513 Visits: 46342
NOLOCK/READ UNCOMMITTED only apply to selects. All data modifications will ignore that hint, they have to lock exclusive.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search