I have tables that during production hours are not wrote to, and yet during the night they can have records written. The few nights data is being wrote to the tables, users are not running queries against them.

We think we have some read blocking on longer running queries on these tables and some users are paying the price. We're considering adding nolock to these queries.

My general rule of thumb is no nolock. But I'm starting to think this might be an okay use case.

I realize I could also look at READ COMMITTED snapshot isolation but I feel like it might accomplish the same thing, but would this route use the tempdb more?

  • 5
    Have you actually validated that queries are taking a long time specifically because of blocking? I suspect they are not - just that it's a large table and reading is not free, especially if the data isn't all in memory. Perhaps these are all scans because of poor indexing and/or poor queries. If that is the case, NOLOCK isn't going to be a magic turbo button. – Aaron Bertrand May 28 '14 at 18:40
  • 3
    If the table isn't being written to why would anything be holding a blocking lock? The analysis doesn't seem to make sense. How have you come to this conclusion? – Martin Smith May 28 '14 at 19:03
  • Also, what is your question? If we think it's okay for you to use NOLOCK? That would be quite opinion-based. Some people swear by it, others swear against it. Most who use it don't truly comprehend the accuracy ramifications (or don't care). Anyway, did you try NOLOCK, and did it make anything better? My guess is the answer is either {no|N/A} or {yes|no}. I'm leaning on the former. You should try it. I bet your queries don't speed up. Try to solve the problem you know exists, instead of just knee-jerking something you think may be relevant. – Aaron Bertrand May 28 '14 at 19:19
  • READ COMMITTED SNAPSHOT ISOLATION will make heavier use of tempdb. Do you have reason to believe that would be a problem? – RLF May 28 '14 at 20:46
  • @AaronBertrand I do not have the queries, they are all at the app and dll level. I don't want to mine with profiler, and XEL is on my to do list to learn. I can bother devs for them but there tons of them. Also, my question is I have tables in a DB – pmpjr May 29 '14 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.