Performance Adding Hints

Question

Post reply

Performance Adding Hints

Robert W Marda

SSChampion

Points: 13413
More actions
September 4, 2002 at 12:00 am

#105163

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/performanceaddinghints.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Viewing 6 posts - 1 through 5 (of 5 total)

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

darius.england Mr or Mrs. 500 Points: 557 More actions · Answer 1

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...

Kevin-214685 SSC Veteran Points: 230 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kevin-214685 SSC Veteran Points: 230 More actions · Answer 4

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?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)