Post reply

Usefulness of NOLOCK or READUNCOMMITED

  • October 12, 2011 at 10:07 am

    #1393571

    jared-709193 (10/12/2011)

    MysteryJimbo (10/12/2011)

    Ninja's_RGR'us (10/12/2011)

    I've yet to see a single client trade data quality for a little extra performance (never had a lot with correct tuning on the system).

    Once you tell them it's certain that at some point they'll get incorrect results (even if by a little). They always say no to nolock.

    Of course I'm dealing with ERPs, and not google like search results.

    You dont work here 😀

    It's acceptable in the web based company where I work down to the number of concurrent connections and the reliability using nolock gives that we dont incur online outages due to speedily tested code. We work in an extremely dynamic environment which requires it. It's not implemented for performance sake.

    Dont get me wrong, when reading uncommitted data is not acceptable we dont use NOLOCK or READ UNCOMMITTED but as a procedure, this is used for virtually all queries and stored procedures.

    The same is true of my company. They have several applications caching data for telephony and reports that look at millions of rows where a couple hundred dirty reads is not a problem. However, locks are. Again, if the business case cannot tolerate dirty reads it will be changed. However, 99% of the queries that the applications use actually prefer dirty reads over the possibility of missing a transaction that has not yet been committed.

    Take away as usual... It Depends 🙂

    Thanks,

    Jared

    Agreed, but recenty I'm been working with the financial dep. So bad data is just out of the question.

    Of course if you only need a ballpark figure of the sales so far in the day or # of phone calls, an approximation is just fine.

    In that case I wouldn't be speacking of bad data tho.

  • October 12, 2011 at 10:21 am

    #1393585

    MysteryJimbo (10/12/2011)

    Ninja's_RGR'us (10/12/2011)

    I've yet to see a single client trade data quality for a little extra performance (never had a lot with correct tuning on the system).

    Once you tell them it's certain that at some point they'll get incorrect results (even if by a little). They always say no to nolock.

    Of course I'm dealing with ERPs, and not google like search results.

    You dont work here 😀

    It's acceptable in the web based company where I work down to the number of concurrent connections and the reliability using nolock gives that we dont incur online outages due to speedily tested code. We work in an extremely dynamic environment which requires it. It's not implemented for performance sake.

    Dont get me wrong, when reading uncommitted data is not acceptable we dont use NOLOCK or READ UNCOMMITTED but as a procedure, this is used for virtually all queries and stored procedures.

    I wouldn't argue the rationale for using NOLOCK as a routine hint for queries but I would say that performance IS the reason your shop uses it. In the situation you described it is entirely the reason it is in use.

    This discussion always seems to go on and on and is one of those items in sql that "it depends" just doesn't even begin to cover. There are so many reasons that it is a horrible idea to use, and there are so many reasons (like your situation) that is can make the system more reliable and faster. There just simply is no cookie cutter response as to when it is appropriate.

    I tend to shudder at using NOLOCK but that is because I work in an environment where dirty reads just simply are unacceptable for almost everything. You shudder at not using that hint because dirty reads are not an issue in your environment.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 12, 2011 at 12:47 pm

    #1393656

    Sean Lange (10/12/2011)

    I wouldn't argue the rationale for using NOLOCK as a routine hint for queries but I would say that performance IS the reason your shop uses it. In the situation you described it is entirely the reason it is in use.

    Arguably you could say that, but since the queries will run with near identical durations and io I would have to disagree that it is to do with the performance of the sql. More likely to improve the performance of the applications.

  • October 12, 2011 at 1:05 pm

    #1393665

    MysteryJimbo (10/12/2011)

    Sean Lange (10/12/2011)

    I wouldn't argue the rationale for using NOLOCK as a routine hint for queries but I would say that performance IS the reason your shop uses it. In the situation you described it is entirely the reason it is in use.

    Arguably you could say that, but since the queries will run with near identical durations and io I would have to disagree that it is to do with the performance of the sql. More likely to improve the performance of the applications.

    So how does adding a NOLOCK hint improve the performance of an application? The only possible performance would be from sql. The application itself doesn't care if the query used the NOLOCK hint. It waits for the output.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 12, 2011 at 1:11 pm

    #1393667

    Sean Lange (10/12/2011)

    MysteryJimbo (10/12/2011)

    Sean Lange (10/12/2011)

    I wouldn't argue the rationale for using NOLOCK as a routine hint for queries but I would say that performance IS the reason your shop uses it. In the situation you described it is entirely the reason it is in use.

    Arguably you could say that, but since the queries will run with near identical durations and io I would have to disagree that it is to do with the performance of the sql. More likely to improve the performance of the applications.

    So how does adding a NOLOCK hint improve the performance of an application? The only possible performance would be from sql. The application itself doesn't care if the query used the NOLOCK hint. It waits for the output.

    In my case it is important for our application to read data that may never be committed. The application depends on this, not just for performance, but to function according to business rules.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 12, 2011 at 1:20 pm

    #1393674

    jared-709193 (10/12/2011)

    Sean Lange (10/12/2011)

    MysteryJimbo (10/12/2011)

    Sean Lange (10/12/2011)

    I wouldn't argue the rationale for using NOLOCK as a routine hint for queries but I would say that performance IS the reason your shop uses it. In the situation you described it is entirely the reason it is in use.

    Arguably you could say that, but since the queries will run with near identical durations and io I would have to disagree that it is to do with the performance of the sql. More likely to improve the performance of the applications.

    So how does adding a NOLOCK hint improve the performance of an application? The only possible performance would be from sql. The application itself doesn't care if the query used the NOLOCK hint. It waits for the output.

    In my case it is important for our application to read data that may never be committed. The application depends on this, not just for performance, but to function according to business rules.

    Thanks,

    Jared

    Not trying to be a pain but you say it isn't for performance but you keep saying that it is for performance. Business rules for that are a bit odd but that is the way business rules can be. 😉 Again the only way your application could depend on NOLOCK for performance is because using that hint makes the sql slightly faster. It has been proven time and time again that it will perform at least slightly faster in almost all cases. In your case (especially since you actually desire dirty reads) that split second difference can be a big boost. Just splitting hairs I guess but is seems like the slight performance increase is a side affect of the desire to read uncommitted data.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • October 12, 2011 at 1:21 pm

    #1393675

    Lexa (10/11/2011)

    Hi,

    Can someone give examples where using NOLOCK or READUNCOMMITED hints helped with performance when using with SELECTs?

    Thanks

    Here is an example of a legitimate case: A table with rows containing temporary session data identified by a unique guid. The guid is assigned to a session (client) at the time the session is established and is always unique to one session (no two clients will ever use the same guid). The session is scope for as finite amount of time then ages off.

    In this case there is no need for locking (except resource locks for page allocations) because the application does not employ concurrency. You can inform SQL Server of that fact using NOLOCK.

    The probability of survival is inversely proportional to the angle of arrival.

Viewing 7 posts - 16 through 21 (of 21 total)

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