Post reply

NOLOCK hint.

  • May 30, 2013 at 10:15 am

    #1619781

    Michael Valentine Jones (5/30/2013)

    Luis Cazares (5/30/2013)

    GilaMonster (5/28/2013)

    WangcChiKaBastar (5/28/2013)

    our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).

    Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.

    If you're getting timeouts, tune the queries, don't address a symptom.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail,

    I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.

    Using one of the snapshop isolation levels is a far better solution for elimination of blocks and contention.

    The "DBAs" saying the most important thing is not having blocks or contentions, may mean it's the most important thing to them, not the business users. The "DBAs" may be more concerned about lack of complaints from users than the accuracy of the data that the users are getting.

    Yeah! But again, it's really depends who your users are. If you have one happy broker (who happens to be the best in the firm) missing commission on just one of his aggregated monthly sales figure, I can easily see how all developers involved (eg. their managers and DBA's teams) will start looking for a new job in no time:hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 30, 2013 at 10:31 am

    #1619786

    Michael Valentine Jones (5/30/2013)

    Luis Cazares (5/30/2013)

    GilaMonster (5/28/2013)

    WangcChiKaBastar (5/28/2013)

    our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).

    Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.

    If you're getting timeouts, tune the queries, don't address a symptom.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail,

    I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.

    Using one of the snapshop isolation levels is a far better solution for elimination of blocks and contention.

    The "DBAs" saying the most important thing is not having blocks or contentions, may mean it's the most important thing to them, not the business users. The "DBAs" may be more concerned about lack of complaints from users than the accuracy of the data that the users are getting.

    That's exactly what I believe. Users will complain and it will be our fault as developers and the "DBAs" will remain calm.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 30, 2013 at 10:51 am

    #1619792

    Luis Cazares (5/30/2013)

    Michael Valentine Jones (5/30/2013)

    Luis Cazares (5/30/2013)

    GilaMonster (5/28/2013)

    WangcChiKaBastar (5/28/2013)

    our database is not financial, our use of NOLOCK hint was to increase concurrency and limit contention (deadlock).

    Use one of the snapshot isolation levels. Financial or non-financial, I've never met a business user that likes the idea that their reports could be wrong.

    If you're getting timeouts, tune the queries, don't address a symptom.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail,

    I invite you to know the company I'm working for. According to the "DBAs" the most important thing is not having blocks or contentions even if the information is incorrect. It's important to notice that it's a financial company operating in 8 countries.

    Using one of the snapshop isolation levels is a far better solution for elimination of blocks and contention.

    The "DBAs" saying the most important thing is not having blocks or contentions, may mean it's the most important thing to them, not the business users. The "DBAs" may be more concerned about lack of complaints from users than the accuracy of the data that the users are getting.

    That's exactly what I believe. Users will complain and it will be our fault as developers and the "DBAs" will remain calm.

    There is a great tool around. It's called "Email".

    Email them (CC to their and your manager), about the real business risk of having specific use of technology (NOLOCK). Be very polite, don't try to abuse them. Add URL's to expert's articles explaining the issue and examples. Suggest better technology (in this particular cae, it could be using snapshot isolation level http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx).

    IN NO WAY TRYING TO BE PERSISTENT! Just a friendly observation...

    So, in case of a big f**k up, the first question will be: "Who is guilty?" the second one could be "We pay lots to IT, didn't anyone from there knew about this issue and not reported it?". I guess you will get a pretty good chance to be promoted when you produce your email "from while ago" :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 30, 2013 at 10:56 am

    #1619794

    I've sent some emails and the answer was that they are aware of the implications of using it but it's a policy and must be followed.

    I'll save that email to defend myself if there's a problem later on.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 30, 2013 at 12:47 pm

    #1619824

    Luis Cazares (5/30/2013)

    I've sent some emails and the answer was that they are aware of the implications of using it but it's a policy and must be followed.

    I'll save that email to defend myself if there's a problem later on.

    And I'm sure if you tried to find out who established the procedure and why it was established you would get the usual "it has always been that way."

    Reminds me of the story of the five gorillas and the bananas.

Viewing 5 posts - 16 through 19 (of 19 total)

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