June 20, 2010 at 8:19 am
GilaMonster (6/19/2010)
Eugene Elutin (6/19/2010)
Contrary, I think most of the time, developers using hints, do know enough about them. So, in my experience, I found that "many people" use itto help performcance when dirty reads are acceptable.I asked in a TechEd presentation last year how many people used nolock often (session on query hints and plan guides). About 1/4 of the room put up their hands (20 or so people). I asked how many of them knew that it allowed dirty reads and possibly duplicated or missed data. All but one person put their hands down.
My experience has been the opposite. Over the years I've known quite a few people who have used NOLOCK occassionally (and equivalents in other databases, not all SQL-based) and have only ever come across one who used it without understanding all the implications (he has been educated - I think it sunk in the second time, when the education was rather louder than the first time). Most of them tended to use TABLOCKX (or equivalent) more often than they used NOLOCK, and they knew what that meant too. I've known far fewer people who used join hints or index hints, but a much higher proportion (nearly all) used them wrongly.
Tom
June 20, 2010 at 2:02 pm
GilaMonster (6/19/2010)
...I asked in a TechEd presentation last year how many people used nolock often (session on query hints and plan guides). About 1/4 of the room put up their hands (20 or so people). I asked how many of them knew that it allowed dirty reads and possibly duplicated or missed data. All but one person put their hands down.
...
It means that I'm a lucky guy, as I'm working with people how know what they are doing or, at least, with people who don't use things they don't know. I have googled "WITH (NOLOCK)" and first five sites listed (including Microsoft and, of cause, SQLServerCentral) straight away flash the issue of dirty reads, before even talking of any performance benefits.
Therefore, I may conclude that the 1/4 of the room (minus 1 person) from the mentioned TechEd presentation, were:
a) Very old mens who still using punch cards and are not aware of internet existence; or
b) Very very young children, who can't read at all or can't read more than 5 lines of text at once.
😀
There is an option c): they've been told, by someone at some conference, to use NOLOCK for performance, without explaining the other sides of it. - SORRY, But it was not me!!!
"Shit happens" as used to say a running man (Forest Gum).:-D
GilaMonster (6/19/2010)
... Nolock is particularly bad because people use it when giving answers on forums without bothering to explain what it does (assuming that they even know themselves) so people get the impression that it's a good thing to use because the 'experts' are using it....
I'm protesting here:
1. "Nolock is particularly bad" - it is not particularly bad and it is not bad at all, it does exist on a purpose and have valid cases for use, otherwise it would not be there at all. It is similar to say that "coding in C++ are bad, cause it's possible to write very dangerous code, and so on"
2. I have not advised to use it here. It was already in the given query.
Here, I should admit, that by the look of the given query, it was not done by someone who knows SQL well enough to use hints. But, at the end, it may be was done by the contractor who didn't care much (as myself) and was late for an important meeting (in the pub) :hehe:
June 20, 2010 at 3:23 pm
Eugene Elutin (6/20/2010)
Therefore, I may conclude that the 1/4 of the room (minus 1 person) from the mentioned TechEd presentation, were:a) Very old mens who still using punch cards and are not aware of internet existence; or
b) Very very young children, who can't read at all or can't read more than 5 lines of text at once.
😀
No and no.
There is an option c): they've been told, by someone at some conference, to use NOLOCK for performance, without explaining the other sides of it. -
I spoke to a couple of the people after the session. They'd either been told by colleagues or had encountered blocking in their queries, went looking for a way to avoid blocking (BoL), saw Nolock and used it without further investigation. Not everyone reads blogs/forums.
I keep running across posts here where people recommend nolock (usually in performance tuning questions) without explaining what it means or what it does.
http://www.sqlservercentral.com/articles/T-SQL/66512/ - Nolock on just about every query with no explanations why (and in the discussion the writer says "I just used it for performance reasons."
http://www.sqlservercentral.com/Forums/FindPost470128.aspx
1. "Nolock is particularly bad" - it is not particularly bad and it is not bad at all, it does exist on a purpose and have valid cases for use, otherwise it would not be there at all.
It is similar to say that "coding in C++ are bad, cause it's possible to write very dangerous code, and so on"
Odd analogy. I didn't slam the entire T-SQL language. Perhaps a slightly closer analogy would be VB's 'ON ERROR RESUME NEXT'. There's reasons for its existence, good places for it to be used, but used too much/too often it just becomes a case of 'Errors? What errors? Lalalalala' Quick fix of symptoms instead of looking for root causes
Yes, there are valid uses for nolock, there are also places where it is used and shouldn't be. I say that it's particularly bad because it can cause intermittently incorrect results, and intermittent bugs are hellishly hard to fix, if the developer even realises that they're happening, and way too many people are not aware of that. I would prefer to point out the side effects and possibly have the person say 'Yes, I know about that' than assume that they know.
I recall a thread here where someone had a massive query with nolock on every single table. I mentioned to him the side effects of nolock (dirty reads, dup reads, missed rows) and his reply was along the lines of 'Oh, that explains the problems the users have been having. We never could it figure out."
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2010 at 3:05 am
...
I spoke to a couple of the people after the session. They'd either been told by colleagues or had encountered blocking in their queries, went looking for a way to avoid blocking (BoL), saw Nolock and used it without further investigation. Not everyone reads blogs/forums.
...
1. blaim colleagues for a wrong advise? good idea.
- :w00t: who coded this sh*t?
- :doze: sorry, it was me, but I was advised to do it by John...
- :crazy: where is John?
- oops, he left the company two days ago...
:laugh:
2. How explaining all effects of NOLOCK or other hints here will help to ones who doesn't reads blogs/forum? BTW in msdn BOL (which is not a blog or forum, but a primary reference source), it explained enough and even suggested not to use it for a new develeopment. So, it must be just a bad colleagues, who left few days ago...
...
Odd analogy. I didn't slam the entire T-SQL language. Perhaps a slightly closer analogy would be VB's 'ON ERROR RESUME NEXT'. There's reasons for its existence, good places for it to be used, but used too much/too often it just becomes a case of 'Errors? What errors? Lalalalala' Quick fix of symptoms instead of looking for root causes
...
1. I loved this feature of VB, just put it as a first line of code and it guarantees that "Errors? What errors? Lalalala". Would be cool to have it by default in .NET 😀
2. By the way, before introducing TRY-CATCH in SQL2005, T-SQL had the above feature as only one possible for tracking errors. You would need to check error after each and every statement, wasn't it the same as after effect of ERROR RESUME NEXT in VB?:-P
I do unreservly agree with you about advising on use of hints. If hint is advised, all aspects of its effects should be explained.
Please note: I did not advise use of NOLOCK here. It was already part of the given query.
P.S I love using smiles: :-):-D:-P;-)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply