August 30, 2013 at 2:20 pm
"I don't know why so many want to deny that: THAT'S the main reason NOLOCK is available, to allow one to reduce locking overhead" - I do agree with this statement
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 30, 2013 at 2:23 pm
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.
To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding any locks at all from being taken while reading the table.
Good grief Scott. How many times have you seen or heard people say they add NOLOCK because "it makes the queries faster"? Using NOLOCK as a performance enhancement tool for queries is like using a jet engine for cooking fish. Sure it will get you to the end line slightly faster but it has a really good chance of destroying the food.
Are you honestly suggesting that we should use this to boost performance or do you just like splitting hairs? I provided some links with in depth details about the usage of that hint. I would assume that somewhat alleviates the need for me to reproduce all the details in my thread.
Yes, I am honestly suggesting it can be worthwhile to add NOLOCK to boost performance. Are you honestly suggesting it should be removed from the product just because you don't understand when it is safe or not safe to use it??
Yes I am. I would use isolation which does not return duplicate/missing data unlike NOLOCK.
Huh? The only isolation level lower than the default is READ UNCOMMITTED, which is just shorthand for NOLOCK on every table.
This discussion has gotten far beyond silly. You are now accusing me of not understanding what the NOLOCK hint does or when it is safe. You win Scott.
What "isolation" were you talking about then? What "isolation" does not return "bad" data like NOLOCK but doesn't require locks??
["Snapshot isolation" has its own (rather large) overhead.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2013 at 2:24 pm
And now a word from our sponsor (Molap). It seems your thread has been hijacked by a discussion well outside the bounds of your original question. As has been said by at least three others in here, if you can post the actual execution plan at a minimum we can likely assist you with your problem.
_______________________________________________________________
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/
August 30, 2013 at 2:49 pm
MyDoggieJessie (8/30/2013)
ScottPletcher (8/30/2013)
MyDoggieJessie (8/30/2013)
It may "bypass" the locking of the records it's ready but it does not improve performance or reduce any overhead one bit.LOL. That's self-contradictory -- if NOLOCK avoids locking that the statement would otherwise have to do, it de facto improves performance.
I'm not saying NOLOCK should be used indiscriminately. I'm saying it does have proper uses because it DOES reduce overhead.
Just because it's often overused and/or misused is no reason to falsify what it does or does not do.
NOLOCK can be useful, for example, for code lookup tables, such as state code lookups. A state hasn't been added since 1959, I think I'll risk it :-).
Also, for example, if/when I'm forced to do read(s) from very busy production table(s) and I want to insure that I don't interfere with production processing.
So you['re saying that if you compare the execution plan of two queries, one with tables having WITH(NOLOCK) specified and one that does not, you would see a more optimal plan with the latter?
I agree "time" is saved by bypassing potentially locked rows, but there's no difference to the performance of the query any more than adding a WAITFOR DELAY in the middle of your stored-procedure/TSQL
No, I'd expect the query plans to be identical, since locking isn't shown in the query plans. But the query with NOLOCK WILL HAVE LESS OVERHEAD if any rows are read. It may be very little less overhead, or a lot less overhead, but it will have less overhead in SQL than a query w/o NOLOCK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply