Post reply

Nolock hint

  • October 6, 2020 at 2:36 pm

    #3793242

    In our stored procedures we have (nolock) hints everywhere in a code. Can I replace them all with a single SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED on the top of S.P.? Is it safe? These stored procedures are for reporting.

    Thanks

     

     

  • October 6, 2020 at 2:51 pm

    #3793249

    Can I replace them all with a single SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

    Yes.

    Should I replace them all with a single SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?

    No.

    At least not if any of the underlying tables is likely to be undergoing INSERTs, UPDATEs or DELETEs while reports are running and you are depending on the integrity of the data presented in your reports.

    • This reply was modified 4 years, 1 month ago by  Phil Parkin.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 6, 2020 at 4:16 pm

    #3793283

    The reason for these NOLOCK's is mostly performance, they don't want to be blocked.

  • October 6, 2020 at 4:45 pm

    #3793288

    Oops.  I think I hit REPORT instead of QUOTE.

    Using NOLOCK everywhere for performance means that your reports are most likely incorrect.  How can business be expected to make informed decisions with bad data.

    If your reports are taking too long, get somebody to do performance tuning on the SQL statements, and potentially on the underlying structures.  Using NOLOCK is the SQL version of Russian Roulette.

    How to post data/code on a forum to get the best help.

  • October 6, 2020 at 5:11 pm

    #3793295

    DesNorton wrote:

    Using NOLOCK is the SQL version of Russian Roulette.

    Heh... with 5 of 6 chambers loaded and the really bad part is that you won't know that your report has been shot in the head. 😀

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

  • October 6, 2020 at 5:21 pm

    #3793301

    Please read this for details of how NOLOCK can make things go wrong.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 7, 2020 at 9:28 am

    This was removed by the editor as SPAM

  • October 7, 2020 at 1:26 pm

    #3793626

    Piling on because this is a serious topic.

    First, yes, using READ UNCOMMITTED is actually a much better choice than painting your code in NOLOCK hints.

    Next, the fact is, reading uncommitted data will speed up your queries. Reducing the amount of locking within the system does make the queries run faster. This is true. However, what's also true is, if your queries are running really slowly, chances are high you're probably seeing lots of scans. This is especially true if reducing locking increases your query performance. So, with lots of scans, the chances of page splits occurring are high. A page split occurring, before, or after, you scan a particular page means that you could either miss some rows, or read them more than once (twice, yes, but even more depending on the number and severity of page splits occurring in your system).

    So, you're facing a trade-off that must be made clear to the business. We can make the queries run faster without doing all the hard work of adjusting our structure, picking the right indexes, and writing our code correctly. However, when we do this, we sacrifice accuracy of data, in a very large way. Is it OK if someone's bank withdrawal gets counted twice? Is it OK if someone's order gets fulfilled twice or billed twice? If so, great, you can use READ UNCOMMITTED and achieve an increase in performance without doing the hard work. If not, if data accuracy matters, then do not use this.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 7, 2020 at 2:06 pm

    #3793648

    And of course keep in mind you also have the transaction option "Snapshot"

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 9 posts - 1 through 8 (of 8 total)

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