Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

READ UNCOMMITTED vs. NOLOCK Expand / Collapse
Author
Message
Posted Tuesday, April 14, 2009 9:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 8:24 AM
Points: 69, 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?
Post #696705
Posted Tuesday, April 14, 2009 11:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 9, 2014 4:10 PM
Points: 1,388, Visits: 239
My understading is that these are the same.
Post #696793
Posted Tuesday, April 14, 2009 11:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 15,517, Visits: 27,898
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #696846
Posted Tuesday, April 14, 2009 11:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
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

Post #696847
Posted Tuesday, April 14, 2009 12:29 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 4,388, Visits: 9,506
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #696885
Posted Wednesday, April 15, 2009 9:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 8:24 AM
Points: 69, Visits: 56
Thanks for the input everyone.
Post #697655
Posted Wednesday, April 29, 2009 2:25 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:44 AM
Points: 988, Visits: 2,945
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.
Post #707215
Posted Wednesday, April 29, 2009 2:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
Asked and (partially) answered here
http://www.sqlservercentral.com/Forums/Topic707203-146-1.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #707223
Posted Wednesday, May 27, 2009 7:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 9, 2014 7:44 AM
Points: 988, Visits: 2,945
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.


Post #724089
Posted Wednesday, May 27, 2009 7:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
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 2008, MVP
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

Post #724099
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse