November 16, 2011 at 12:30 pm
GSquared (11/16/2011)
Brandie Tarvin (11/16/2011)
Dev (11/16/2011)
Now I'm curious. Since when is the use of NOLOCK a best practice?
This is cross-question. This is what I suggested to Ashu. 😀
But what I am saying is a fact (Live Example). My customer is forcing us to write NOLOCK hint in all queries. It's primary QA check (per their Best Practice Document). SPs will not move to higher environments if they don't have such hints. What to do in such case except accepting the foolish practices. That’s why I didn’t criticize Ashu.
Oh, I've seen it. In fact, there are people who swear by it in my environment, even though I've tried to get them not to use it. It was just the fact that you mentioned Best Practices and I've never seen this listed in a Best Practice list before.
As far as how to deal with it, find an example where that dirty read will cost the company money. Document thoroughly and show the boss. Then you've done your CYA and maybe convinced the boss that NOLOCK is a bad idea.
The place I worked four years ago, it was listed as a "Required Practice", which took precedence over "Best Practices" there. Fortunately, I was able to get them to change their minds on it, but they'd been using it for years. So, there are definitely people who think it's a best practice. And some of them are in positions of authority and can enforce it.
I worked as a consultant several years ago for a major health care provider and their rule was to add the NOLOCK hint to every single query in the entire system. This is the system that handled disbursements of health care claims. Literally millions of financial transactions daily, all based on dirty reads. When I discovered the prevalence of this I ran it up the flag pole all the way to the CIO. It was his mandate that it be used, he was determined that it was the magical "go fast" easy button. I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
_______________________________________________________________
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/
November 16, 2011 at 1:01 pm
GilaMonster (11/16/2011)
Michael Valentine Jones (11/16/2011)
My preference is to set the database to read_committed_snapshot and never have to worry about any of this again.
use master;
alter database [MyDB] set allow_snapshot_isolation on;
alter database [MyDB] set read_committed_snapshot on;
Why both? Or do you have some processes that you want using snapshot isolation and the others using read committed snapshot?
The main thing I am doing is just getting read committed snapshot set on.
I enable snapshot isolation to make it available if the app developers want to use it for a particular process. There doesn't seem to be any problem with having it on and unused that I am aware of.
November 17, 2011 at 1:04 am
GilaMonster (11/16/2011)
Dev (11/16/2011)
I had one of those. I sat down with the architect and explained to him what NoLock actually did and what effects it could have. The standards document was updated the next day. Most places that 'require' this simply don't know what it actually does and will be horrified when they realise what it actually does.
@Gail: We tried it but didn't work out. Their statement is firm "We are fine with Dirty Reads". The worst part is it’s a Financial Domain project. :unsure:
People say that a lot. Thing is, do they know what 'dirty reads' are, do they know what effects they can have? I certainly didn't go to the architect and say 'Stop using NoLock! It can cause Dirty Reads!!!'. His reply would have been something along the lines of 'And so?'
I explained to him the effects that it could have on the data. Not in deep technical terms. In examples. Somehow seeing a $10 million transfer from a rather large client listed twice in the daily summary caught his attention.
It’s just not the Technology we are talking about. Managerial Practices also involved here. If you have a customer who hold your 25% (or more) of Customer Base you (& your manager) wouldn’t like to make him / her unhappy (by any means, even by crossing his statements).
As GSquared pointed out “some of them are in positions of authority and can enforce it”
November 17, 2011 at 1:09 am
I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
New Job???
I am sorry, I didn't get it. You left the job because these practices were unacceptable to you. OR...
November 17, 2011 at 4:32 am
Dev (11/17/2011)
I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
New Job???
I am sorry, I didn't get it. You left the job because these practices were unacceptable to you. OR...
Yes he left, and I would have done the same in the past (different reason, but in the same ballpark).
November 17, 2011 at 4:42 am
Ninja's_RGR'us (11/17/2011)
Dev (11/17/2011)
I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
New Job???
I am sorry, I didn't get it. You left the job because these practices were unacceptable to you. OR...
Yes he left, and I would have done the same in the past (different reason, but in the same ballpark).
Dev, Look at this from his point of view. He's the one will would have gotten in trouble for the financial numbers being wrong. Nevermind that using NOLOCK was not his call, if the financial reporting got the company sued, if it looked like data disappeared (because dirty data was being read for the report and then never got committed), then it was HIS butt on the line, not his boss's butt.
November 17, 2011 at 4:47 am
Brandie Tarvin (11/17/2011)
Ninja's_RGR'us (11/17/2011)
Dev (11/17/2011)
I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
New Job???
I am sorry, I didn't get it. You left the job because these practices were unacceptable to you. OR...
Yes he left, and I would have done the same in the past (different reason, but in the same ballpark).
Dev, Look at this from his point of view. He's the one will would have gotten in trouble for the financial numbers being wrong. Nevermind that using NOLOCK was not his call, if the financial reporting got the company sued, if it looked like data disappeared (because dirty data was being read for the report and then never got committed), then it was HIS butt on the line, not his boss's butt.
I'd have gone just on the principal of giving crappy service to the end user (the real client in any company, not your boss, not his boss, not the CTO, not the owner(s) or shareholders).
I don't want to work in a company I'd never do business with because their service is crappy at best.
November 17, 2011 at 4:54 am
P.S. That goes on how they treat their employees as well.
If you see something you don't like being done to them (or even despise), you can be sure that at some point, when the time is right and all the stars are <mis>aligned that they'll do it to you to.
I've had a situation where an employee had done, re-done and re-re-done the same project (200+ free hours). All for home, in UNPAID overtime, missing the very important family time for 3 months straight.
Each time he asked for more, and PRECISE requirements which were never given. When he said no for the fourth round he was fired on the spot because he wasn't a team player and didn't want to roll up his sleeve and get to work.
So the company hires a competent employee with a few lacks in his knowledge.
They don't train him.
They don't give him the correct info to do the project.
They overload him with too many projects and THEN complain he's getting behind on his "normal" tasks.
Then they dare to fire him on the bases of incompetence when he has absolutely no way to correct the situation.
That's after they stole easily over 10 000$ worth of his time over those last few months (ok he could have objected, which he did and got him fired).
"oddly" enough they did the same thing to me 5-6 months later.
November 17, 2011 at 7:20 am
Dev (11/17/2011)
I did my best to explain why it should not be used and was met with the same response. I got on the phone about 10 minutes after that discussion and found a new job.
New Job???
I am sorry, I didn't get it. You left the job because these practices were unacceptable to you. OR...
Yes I did not want the responsibility of making bad financial decisions based on poor decisions. I knew the time would come and I did not want the bearer of that responsibility to the me. I was a consultant and left for the next gig (which btw I have now been at for 3 1/2 years).
_______________________________________________________________
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/
November 17, 2011 at 7:35 am
Thanks to Sean, Ninja & Brandie for sharing your views here. We always discuss about technology but I believe such discussions are equally important.
I will see what best I can do. I thing for sure, I will not quit for this reason (because I have taken sufficient precautionary step to save my B**T)... I will do it someday for some other reason perhaps 😉
November 17, 2011 at 7:50 am
Dev (11/17/2011)
Thanks to Sean, Ninja & Brandie for sharing your views here. We always discuss about technology but I believe such discussions are equally important.I will see what best I can do. I thing for sure, I will not quit for this reason (because I have taken sufficient precautionary step to save my B**T)... I will do it someday for some other reason perhaps 😉
Hey Dev, being that my company does not use their data for financial or anything like that... It is an unwritten rule to use WITH(NOLOCK). We have weighed the advantages and disadvantages and the potential for locks that may cause one of our applications to timeout or make a user wait outweighs the possible of dirty reads in every business case presented. I still believe that it should be used on a case by case basis and not all of the time, but so far have not come across a case strong enough to bust the idea that if it is not implemented from the get go it may cause issues as traffic or data increases. An unexpected lock or interference at that point would be detrimental to a clients' application in their eyes. In this case, I have just accepted it.
Jared
Jared
CE - Microsoft
November 17, 2011 at 7:54 am
p-nut (11/17/2011)
Dev (11/17/2011)
Thanks to Sean, Ninja & Brandie for sharing your views here. We always discuss about technology but I believe such discussions are equally important.I will see what best I can do. I thing for sure, I will not quit for this reason (because I have taken sufficient precautionary step to save my B**T)... I will do it someday for some other reason perhaps 😉
Hey Dev, being that my company does not use their data for financial or anything like that... It is an unwritten rule to use WITH(NOLOCK). We have weighed the advantages and disadvantages and the potential for locks that may cause one of our applications to timeout or make a user wait outweighs the possible of dirty reads in every business case presented. I still believe that it should be used on a case by case basis and not all of the time, but so far have not come across a case strong enough to bust the idea that if it is not implemented from the get go it may cause issues as traffic or data increases. An unexpected lock or interference at that point would be detrimental to a clients' application in their eyes. In this case, I have just accepted it.
Jared
Why did you choose that over snapshop isolation?
November 17, 2011 at 7:58 am
p-nut (11/17/2011)
Dev (11/17/2011)
Thanks to Sean, Ninja & Brandie for sharing your views here. We always discuss about technology but I believe such discussions are equally important.I will see what best I can do. I thing for sure, I will not quit for this reason (because I have taken sufficient precautionary step to save my B**T)... I will do it someday for some other reason perhaps 😉
Hey Dev, being that my company does not use their data for financial or anything like that... It is an unwritten rule to use WITH(NOLOCK). We have weighed the advantages and disadvantages and the potential for locks that may cause one of our applications to timeout or make a user wait outweighs the possible of dirty reads in every business case presented. I still believe that it should be used on a case by case basis and not all of the time, but so far have not come across a case strong enough to bust the idea that if it is not implemented from the get go it may cause issues as traffic or data increases. An unexpected lock or interference at that point would be detrimental to a clients' application in their eyes. In this case, I have just accepted it.
Jared
There are times when a dirty read is perfectly acceptable (google, facebook that kind of thing) but it has not place in a business application. It doesn't matter if the data is not financial. Intentionally introducing possible incorrect data is along the line of saying "well I think the code is correct most of the time but I know there are some places where it will be incorrect, however those times are unknown, unpredictable and you will never really know if your data is correct or not". So if your business decision is that close is good enough and occasionally being way off is acceptable then I guess dirty reads are ok. I would still suggest snapshot isolation over writing the same hint over and over.
_______________________________________________________________
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/
November 17, 2011 at 8:15 am
Ninja and Sean,
I will ask my boss why we don't use snapshot isolation instead. I am still learning about all of these different hints and isolation levels. Understanding them is one thing, then understanding what exactly causes locks and such is another to add to that to gain a big picture view. I will discuss with my boss today and report her thoughts. She is an outstanding DBA, so either I will get a great explanation, make her question the use (she has not been with this company much longer than I either; 1 yr for her), or get a promotion :w00t:
Thanks,
Jared
Jared
CE - Microsoft
November 17, 2011 at 8:18 am
Getting a promotion from this single idea is a tough sell. You better have very strong proof on how it helps the business ($, problems avoided, etc).
Still a good idea to talk to her about it. 😉
Viewing 15 posts - 46 through 60 (of 76 total)
You must be logged in to reply to this topic. Login to reply