June 5, 2014 at 8:21 am
SQLRNNR (6/5/2014)
Great. I love that excuse "It's the way we/I have always done it".
That kind of excuse right there shows a base incompetence. Everything he needed to learn was learned in kindergarten.
He is using nolock therefore it would be apparent that he is creating a lot of crap code. Yup, that is highly educated.:-D
Yes. 🙁 I don't know everything there is to know about SQL...my mind is blown here on a regular basis, but I know enough to know that I DON'T know everything...and I'm resourceful enough to look for answers or ask for help.
I just got overruled by one of the solutions architects who agrees that it's an "industry standard" so it's kind of a moot point. I could possibly argue with a consultant, but as a relative newbie here it wouldn't be wise, politically, to go up against someone who is well-respected here and who has been here for many years. Even if I think he's wrong.
Can't win 'em all I guess!
June 5, 2014 at 8:26 am
SQLRNNR (6/5/2014)
SQL is delicious (6/5/2014)
Koen Verbeeck (6/5/2014)
SQL is delicious (6/5/2014)
On the subject of WITH (NOLOCK) in code being a pet peeve....I was going over an SSIS template with a consultant the other day, and there was WITH (NOLOCK) all over the place in his Execute SQL tasks. I asked him why and he said "It is an industry standard."
I told him it wasn't. He said "The first place I worked, it was required to use it on every query and I have used it everywhere ever since and nobody has said not to."
I responded that that doesn't make it an industry standard, as the first guy who told him to do that was wrong.
He shrugged and said he wasn't going to change it. His defense is "we must prevent deadlocks."
Your response is:
"and you want dirty data"...
Correct, and that is exactly what I said to him. He shrugged and said it didn't matter.
I should add that this (foreign) consultant, while reasonably friendly, also labors under the delusion that the American education system is worthless and that our master's level work is the equivalent of junior high/early high school material in India. Given his assumption, he is dismissive of Americans' concerns. I don't think he does it on purpose, or to be mean...I just think there is an innate cultural bias at work that he may not even be aware of.
What this means is I not only have to prove that he's wrong, but provide an alternative strategy. I'm working on that now, but if anyone has any ideas I'd love to hear them. 😉
Great. I love that excuse "It's the way we/I have always done it".
That kind of excuse right there shows a base incompetence. Everything he needed to learn was learned in kindergarten.
He is using nolock therefore it would be apparent that he is creating a lot of crap code. Yup, that is highly educated.:-D
Jason, that's a good point on the consultant being highly educated when he carries around that level of incompetence. It also sounds like he suffers from a level of pride that wouldn't allow him to even consider facts (much less opinions) presented by others if they were contrary to his own. That level of close-mindedness means he isn't going to learn anything except through extreme pain. Yep, clearly his educational system did an awesome job teaching him the real lesson - how to learn.
June 5, 2014 at 8:30 am
Ed Wagner (6/5/2014)
Jason, that's a good point on the consultant being highly educated when he carries around that level of incompetence. It also sounds like he suffers from a level of pride that wouldn't allow him to even consider facts (much less opinions) presented by others if they were contrary to his own. That level of close-mindedness means he isn't going to learn anything except through extreme pain. Yep, clearly his educational system did an awesome job teaching him the real lesson - how to learn.
I agree with this.
When people have told me before that my code is "crap," I've asked them all the same question: "Why?" Not because I'm defensive because someone called my baby ugly, but because I really want to know why it's crap so I can un-crappify it and learn how to write better code.
The way I see it, I'll never be finished learning about SQL Server. Unless I win the lottery or get to retire someday.
June 5, 2014 at 8:39 am
SQL is delicious (6/5/2014)
SQLRNNR (6/5/2014)
Great. I love that excuse "It's the way we/I have always done it".
That kind of excuse right there shows a base incompetence. Everything he needed to learn was learned in kindergarten.
He is using nolock therefore it would be apparent that he is creating a lot of crap code. Yup, that is highly educated.:-D
Yes. 🙁 I don't know everything there is to know about SQL...my mind is blown here on a regular basis, but I know enough to know that I DON'T know everything...and I'm resourceful enough to look for answers or ask for help.
I just got overruled by one of the solutions architects who agrees that it's an "industry standard" so it's kind of a moot point. I could possibly argue with a consultant, but as a relative newbie here it wouldn't be wise, politically, to go up against someone who is well-respected here and who has been here for many years. Even if I think he's wrong.
Can't win 'em all I guess!
erm, yeah. Far from industry standard. It is an industry standard insomuch that if a CFO signs off on an earnings report that used nolock, he could go to prison. Yes that is an edge case, but it underscores the problems here in the States. Nolock can cause bad data. If they are ok with that, then it is their responsibility.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 9:26 am
SQL is delicious (6/5/2014)
SQLRNNR (6/5/2014)
Great. I love that excuse "It's the way we/I have always done it".
That kind of excuse right there shows a base incompetence. Everything he needed to learn was learned in kindergarten.
He is using nolock therefore it would be apparent that he is creating a lot of crap code. Yup, that is highly educated.:-D
Yes. 🙁 I don't know everything there is to know about SQL...my mind is blown here on a regular basis, but I know enough to know that I DON'T know everything...and I'm resourceful enough to look for answers or ask for help.
I just got overruled by one of the solutions architects who agrees that it's an "industry standard" so it's kind of a moot point. I could possibly argue with a consultant, but as a relative newbie here it wouldn't be wise, politically, to go up against someone who is well-respected here and who has been here for many years. Even if I think he's wrong.
Can't win 'em all I guess!
There's a lot of documentation on NOLOCK problems.
You could argue that if it was an "industry standard" it would be the default option or, even better, the default isolation level would be read uncommited. If they're so sure that NOLOCK hint is a best practice, they wouldn't have a problem on changing the isolation level to ensure the "industry standard". Do it politely and you might get good recognition.
June 5, 2014 at 9:28 am
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.
The vendor software we use at my current company has NOLOCK all over the place, however it can manage it. INSERTs, UPDATEs, DELETEs... Who cares, throw NOLOCK on all the joins!
There's also a bunch of "upgrade" scripts we're supposed to be running on this thing's databases at some point. I love how the "database optimization scripts" they've included for the system do two things:
1. Drop all of the indexes on all of their tables (and they were mostly good indexes!)
2. Put NOLOCK everywhere it wasn't already present
At least we can talk with the vendor about it, though! ... Or rather, I tried talking to them about it, and got a canned response of "NOLOCK is a vital part of our SQL coding", with the addendum that, if I wanted to speak about it more in-depth, the company would have to pay an additional $1,000 for "heightened support".
One of the other programmers here tried that route once. We got the exact same response of "It's vital to our operations!", and that was that. Fun :w00t:
- 😀
June 5, 2014 at 9:39 am
hisakimatama (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software
...
"It's vital to our operations!", and that was that. Fun :w00t:
Sounds about like Standard Operating Procedure with vendors. Take shortcuts where possible. It's a pain because their "vital operations" cause numerous production outages but you can't actually fix the problem without paying them extra to implement the simple changes necessary to prevent the outages. They don't even care if you did all the research, have the better working code or not. They still want to bill for that change.
I've started telling clients they need to rework their contracts with the vendors to have an outage clause. If the software causes an outage and the vendor doesn't fix it, then the vendor either needs to pay for the outage or allow the DBA to fix the root of the problem without penalty.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 9:54 am
SQLRNNR (6/5/2014)
hisakimatama (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software
...
"It's vital to our operations!", and that was that. Fun :w00t:
Sounds about like Standard Operating Procedure with vendors. Take shortcuts where possible. It's a pain because their "vital operations" cause numerous production outages but you can't actually fix the problem without paying them extra to implement the simple changes necessary to prevent the outages. They don't even care if you did all the research, have the better working code or not. They still want to bill for that change.
I've started telling clients they need to rework their contracts with the vendors to have an outage clause. If the software causes an outage and the vendor doesn't fix it, then the vendor either needs to pay for the outage or allow the DBA to fix the root of the problem without penalty.
Funny thing about that, actually... 🙂
We had about three days of downtime over the last week because something went funky in the vendor software. After some dedicated digging with Apex SQL's log reader, I found out that their software, for some reason, decided to delete core data necessary for system functionality.
Oops.
Until now, the vendor's been insisting that they don't know what happened, but "it MUST have been something your business did!". Now, I have thoroughly detailed evidence showing the command came from their system's username.
Now to see how they react to this :w00t:
- 😀
June 5, 2014 at 10:01 am
LOL...because I questioned some of their coding practices and brought to light the fact that they're not following our standards when they promised they were, I have been kicked out of their development environment. Their rationale is that I'm going to screw it up and cost their developers time.
Can't make it up. 😀
June 5, 2014 at 10:13 am
SQL is delicious (6/5/2014)
LOL...because I questioned some of their coding practices and brought to light the fact that they're not following our standards when they promised they were, I have been kicked out of their development environment. Their rationale is that I'm going to screw it up and cost their developers time.Can't make it up. 😀
Oh no, I have seen it 2 or 3 times as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 10:27 am
george sibbald (6/5/2014)
TheSQLGuru (6/5/2014)
george sibbald (6/5/2014)
I have an app where the vendor recommends (nay insists) setting maxdop to 1 and cost threshold to 15, go figure.........Is Dynamics AX a microsoft product, they have a habit of recommending maxdop = 1 (sharepoint for instance)
1) MAXDOP 1 is actually a very good recommendation FOR WELL TUNED OLTP APPLICATIONS WHERE NO REPORTING IS BEING DONE AND ONLY THAT APP LIVES ON THE SQL SERVER. Note that is a VERY specific set of circumstances for which in 15 years of consulting I don't recall seeing many (any??) apps/servers that meet the bar.
2) Your specific scenario (MAXDOP 1, CTFP increased to 15) actually addresses an exception to 1) above. That is where reports or larger-costing statements ARE being run and they explicitly hint OPTION (MAXDOP 'somethingOtherThanOne'). Now those 'big hitters' can parallelize but only when they are actually bigger than the horribly-inadequate CTFP default of 5.
good point but the question as to why is met with silence............
There are other recommendatons which reek of throwing the results of white papers at it.
Oh and they want sysadmin for the app account 🙂
So typical vendor behavior (in short they don't know why and 15 is still horribly inadequate).
As for sysadmin - that one gets my blood boiling.
I have a client with a vendor that remotes into the server using the application service account. For some reason everybody is afraid of changing those passwords and revoking rdp access. GRRRRR
Just the other day I found 4 sessions to the prod box using the service account coming from the vendor IP but the Vendor is in denial mode.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 10:39 am
OK all this nonsense about how painful NOLOCK can be has prompted me to write this again. I know I have posted this on this site a few other times. I guess I need to start a blog on here and post it there. In the meantime let me share a real world story of NOLOCK and an arrogant DBA who refused to listen to pleas of a consultant (me). This was a company that processes credit card transactions.
As you would imagine the database received a very high volume of traffic. The amount of traffic was increasing quickly and starting causing some blocking. This is obviously not a good thing when you have customers trying to swipe a plastic card and the system deadlocks or causes delays. Their senior DBA suddenly mandated that all queries must be implemented with NOLOCK, no exceptions. They went so far as to rebuild every single stored procedure in the system and include that hint on every single table. I caused a lot of commotion and complained that this was not a good decision and the company would in fact regret it at some point. Since I was just a consultant they listened to their DBA instead. Now you have to realize that I was also a consultant for the company that originally wrote the software that this new company purchased so I knew the system far better than any of them did.
They spearheaded a project to add NOLOCK to every single table inside of every stored procedure (probably 2000+). This took considerable effort to code and test the entire system. It took somewhere around 3-4 months total time. The help desk stopped receiving calls about customers not being able to swipe their cards and everyone was happy. However, during the first month end auditing they started noticing that some accounts were being denied when they had funds and other accounts were approved when they didn't have funds. This generated lots of overdraft fees, which on a debit card can be around $25-50 per occurrence. This continued for next few months and the accountants were growing increasingly annoyed.
They finally asked me to help them figure out why this kept happening. It took a couple weeks of data analysis and collection but I was finally able to prove that it was all because of dirty reads. We then started another project to undo all the NOLOCK hints in the same procedures we had just added them to. I can't even hazard a guess at how many thousands and thousands of dollars and man hours were wasted on this project. Once all that nonsense was corrected we discovered the source of the blocking and fixed the one procedure that was the issue.
_______________________________________________________________
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/
June 5, 2014 at 10:45 am
Sean Lange (6/5/2014)
It took a couple weeks of data analysis and collection but I was finally able to prove that it was all because of dirty reads. We then started another project to undo all the NOLOCK hints in the same procedures we had just added them to. I can't even hazard a guess at how many thousands and thousands of dollars and man hours were wasted on this project. Once all that nonsense was corrected we discovered the source of the blocking and fixed the one procedure that was the issue.
Awesome story and painful at the same time. They think the magic bullet is that hint, when the magic bullet really is tuning the proper code.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 11:02 am
GilaMonster (6/5/2014)
thomashohner (6/5/2014)
What is the preference on column aliases ?AS. Equals sign is for comparing values or assigning values. 😀
Tends to be needed for computed columns as well, but in the contect of column aliases I wouldn't touch "=".
Tom
June 5, 2014 at 11:19 am
Sean Lange (6/5/2014)
OK all this nonsense about how painful NOLOCK can be has prompted me to write this again. I know I have posted this on this site a few other times. I guess I need to start a blog on here and post it there. In the meantime let me share a real world story of NOLOCK and an arrogant DBA who refused to listen to pleas of a consultant (me). This was a company that processes credit card transactions.As you would imagine the database received a very high volume of traffic. The amount of traffic was increasing quickly and starting causing some blocking. This is obviously not a good thing when you have customers trying to swipe a plastic card and the system deadlocks or causes delays. Their senior DBA suddenly mandated that all queries must be implemented with NOLOCK, no exceptions. They went so far as to rebuild every single stored procedure in the system and include that hint on every single table. I caused a lot of commotion and complained that this was not a good decision and the company would in fact regret it at some point. Since I was just a consultant they listened to their DBA instead. Now you have to realize that I was also a consultant for the company that originally wrote the software that this new company purchased so I knew the system far better than any of them did.
They spearheaded a project to add NOLOCK to every single table inside of every stored procedure (probably 2000+). This took considerable effort to code and test the entire system. It took somewhere around 3-4 months total time. The help desk stopped receiving calls about customers not being able to swipe their cards and everyone was happy. However, during the first month end auditing they started noticing that some accounts were being denied when they had funds and other accounts were approved when they didn't have funds. This generated lots of overdraft fees, which on a debit card can be around $25-50 per occurrence. This continued for next few months and the accountants were growing increasingly annoyed.
They finally asked me to help them figure out why this kept happening. It took a couple weeks of data analysis and collection but I was finally able to prove that it was all because of dirty reads. We then started another project to undo all the NOLOCK hints in the same procedures we had just added them to. I can't even hazard a guess at how many thousands and thousands of dollars and man hours were wasted on this project. Once all that nonsense was corrected we discovered the source of the blocking and fixed the one procedure that was the issue.
Sean, thank you for sharing that story. I have some people complaining about locking on some tables and they blame everything under the sun (except design) for the problem. Someone brought up NOLOCK and I shot it down as hard as I could. They're looking for other ways and I know it's going to be a lot of work, but the data has to be right. I'm going to keep this story in my back pocket for when it comes up again.
Viewing 15 posts - 91 through 105 (of 271 total)
You must be logged in to reply to this topic. Login to reply