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

Performance Adding Hints Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2002 12:00 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: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/performanceaddinghints.asp



Robert W. Marda
SQL Programmer
Ipreo
Post #6603
Posted Friday, May 30, 2008 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:21 AM
Points: 7, Visits: 100
Hi. I don't know if I get it right but I have read here

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
and here
http://blogs.msdn.com/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx

that the NOLOCK hint can wreak havoc on the integrity of the data up to the point where some rows will not be read at all and some of them will be read several times in the same query, so you get more or fewer rows than there are in the underlying table. The guy who wrote about this even gave an example of this actually happening. So, I am not sure if it's safe to use this hint at all...
Post #508897
Posted Wednesday, June 4, 2008 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 4:18 PM
Points: 32, Visits: 12
The question I have is WHat if you have some tables that have a With(NOLOCK) hint on them that are joined to tables that do not have the NOLOCK hint on them

i.e. Select *
from title WITH(NOLOCK)
inner join authors on authors.aid = t.aid

This is starting to happen now when some developers are editing existing code - and leaving out the NOLOCK -
We are starting to see some blocking going on and have not yet determined if this is the cause or not.

Thanks.
Post #511766
Posted Wednesday, June 4, 2008 5:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
Kevin (6/4/2008)
The question I have is WHat if you have some tables that have a With(NOLOCK) hint on them that are joined to tables that do not have the NOLOCK hint on them

i.e. Select *
from title WITH(NOLOCK)
inner join authors on authors.aid = t.aid

This is starting to happen now when some developers are editing existing code - and leaving out the NOLOCK -
We are starting to see some blocking going on and have not yet determined if this is the cause or not.

Thanks.


Teach the new developers your standards... if you can't enforce the standards with code reviews before the code makes it to production, then you have no standards... just new blocking sources ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #511861
Posted Wednesday, June 4, 2008 10:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 4:18 PM
Points: 32, Visits: 12
Thanks - yeah this actually was caught by me in Beta, and is why it's not in production yet.

So are you agreeing that this 'may' be a source or cause for blocking?
Post #511919
Posted Thursday, June 5, 2008 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 35,370, Visits: 31,911
Sure, it could be... the real key is to write code that doesn't need it to prevent blocking, though. Generally speaking, code that uses WITH (NOLOCK) everywhere is to cover the blocking problems associated with slow code or long winded transactions. It would be much better if they fixed those problems.

Even with the use of WITH (NOLOCK) everywhere, you will not prevent deadlocks caused by bad code and it will not cause Inserts, Updates, or Deletes to run any faster or with fewer blocks... only Selects. Inserts, Updates, and Deletes will always lock rows (or more) to do their job correctly.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #512352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse