WITH (NOLOCK) IN SQL Server to improve performance

February 27, 2008 at 8:57 am (SQL, Technical, Tips and Tricks)

I used to see my senior developers use WITH (NOLOCK) when querying in SQL Server and wonder why they use. Now i explored it and found that its useful to improve the performance in executing the query. However there is a disadvantage in using it. The disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table ie Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running. I reffered this link and found it prettu useful.

Advertisements

12 Comments

  1. Windows Vista Tools said,

    Adware Alert – No Spyware

    I suggest you to take a free scan and remove dangerous spyware and adware from your computer. Even if you think your computer is clean, you should scan and you will be surprised.

  2. Praveenkumar said,

    Quick information..Thank you

  3. nk said,

    nice

  4. Abigail Myers said,

    hi
    f2mf6ddfbm85wbpp
    good luck

  5. Boorangoma said,

    Nice, BUT, it’s not necessarily a disadvantage, it’s an application dependent thing. Some applications have relatively static data or consequence free data e.g COUNT(*) of your emails on an email application. If COUNT(*) is off by 1, 2 or even 4, it’s really not the end of the world (or employment) for anyone.

  6. Michael said,

    Or, in my case, a stats table that is generated once daily from a job. I am confident this data is not changing as I am running my query. Thanks for the handy information!

  7. C Chamberland said,

    “Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.”

    But if you are using a high volume OLTP system, and you want to run a query without deadlocking your system, you HAVE to use with(nolock), or else your going to have problems.

  8. iberserk said,

    “But if you are using a high volume OLTP system, and you want to run a query without deadlocking your system, you HAVE to use with(nolock), or else your going to have problems.”

    NOLOCK= READUNCOMMITTED transaction level, nothing else

  9. Alekhya said,

    i want detailed explanation. Please provide the detail explanation

    • keka said,

      it is simply to use to improve the performance.

  10. duoct said,

    My policy is to use hints only when I have no other ways. I am 100% with this post: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx.

  11. shashank said,

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: