Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Adding Hints


Performance Adding Hints

Author
Message
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 82
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

darius.england
darius.england
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 146
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
Kevin-214685
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65615 Visits: 40682
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 Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kevin-214685
Kevin-214685
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65615 Visits: 40682
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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