Performance Adding Hints

  • Robert W Marda

    SSChampion

    Points: 13413

    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

  • darius.england

    Mr or Mrs. 500

    Points: 548

    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

    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: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Kevin-214685

    SSC Veteran

    Points: 230

    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: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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