<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss Content Posted by Robert Marda
»
Performance Adding Hints
Performance Adding Hints
Rate Topic
Display Mode
Topic Options
Author
Message
Robert W Marda
Robert W Marda
Posted Wednesday, September 04, 2002 12:00 AM
SSC Eights!
Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976,
Visits: 48
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
darius.england
darius.england
Posted Friday, May 30, 2008 2:55 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 3:44 AM
Points: 7,
Visits: 66
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
Kevin-214685
Kevin-214685
Posted Wednesday, June 04, 2008 2:17 PM
SSC 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
Jeff Moden
Jeff Moden
Posted Wednesday, June 04, 2008 5:33 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910,
Visits: 26,800
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #511861
Kevin-214685
Kevin-214685
Posted Wednesday, June 04, 2008 10:40 PM
SSC 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
Jeff Moden
Jeff Moden
Posted Thursday, June 05, 2008 11:28 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910,
Visits: 26,800
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #512352
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
