May 23, 2012 at 6:19 am
Lynn Pettis (5/10/2012)
Oliiii (10/15/2010)
Anyone tuning the query by not looking at IO and Time statistics and query plan would immediately fail.Guess I'd fail here. First things I look at are the code, the indexes, and the data. Amazing what you can learn from just looking at those before you even look at IO and Time statistics or the query plan.
I'm not giving a super easy query where you can spot right on what's wrong and I'm giving some big hint that i would like to know by how much the query has been improved (without specifically asking for the statistics).
If the guy can tell right away what's missing that's all good, but I'll still ask him what he would do to give me the situation before and after or I'll ask him to optimize another query a bit harder.
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.
If the guy I'm interviewing has not clue what is the query plan, the statistics io, time or how to use it's info quickly and efficiently, i don't really have any use for him here (that doesn't mean he's bad, just that he doesn't fit).
I guess some people here see that i would make them fail just because they can't answer a question the way i want and freak out but that's not at all how it work 🙂
When I'm doing this I'm sitting right next to the guy in front of the computer, it's all part of a conversation where i ask a bunch of small question and steer the interview the way i want it to go if the guy seems a bit lost.
The interview looks more like a regular day of work summarized in a few minutes, that way the guy can see how we work, what kind of skill he's expected to have and i can see how he react and how he would fit.
I found out doing interview this way was much better than a regular conversation since I've met some folks that could BS their way into any job, and only found out about it after asking some simple technical questions and looking how they would resolve it.
May 23, 2012 at 6:25 am
Oliiii (5/23/2012)
Lynn Pettis (5/10/2012)
Oliiii (10/15/2010)
Anyone tuning the query by not looking at IO and Time statistics and query plan would immediately fail.Guess I'd fail here. First things I look at are the code, the indexes, and the data. Amazing what you can learn from just looking at those before you even look at IO and Time statistics or the query plan.
I'm not giving a super easy query where you can spot right on what's wrong and I'm giving some big hint that i would like to know by how much the query has been improved (without specifically asking for the statistics).
If the guy can tell right away what's missing that's all good, but I'll still ask him what he would do to give me the situation before and after or I'll ask him to optimize another query a bit harder.
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.
If the guy I'm interviewing has not clue what is the query plan, the statistics io, time or how to use it's info quickly and efficiently, i don't really have any use for him here (that doesn't mean he's bad, just that he doesn't fit).
I guess some people here see that i would make them fail just because they can't answer a question the way i want and freak out but that's not at all how it work 🙂
When I'm doing this I'm sitting right next to the guy in front of the computer, it's all part of a conversation where i ask a bunch of small question and steer the interview the way i want it to go if the guy seems a bit lost.
The interview looks more like a regular day of work summarized in a few minutes, that way the guy can see how we work, what kind of skill he's expected to have and i can see how he react and how he would fit.
I found out doing interview this way was much better than a regular conversation since I've met some folks that could BS their way into any job, and only found out about it after asking some simple technical questions and looking how they would resolve it.
I would say that some leading questions that point at stats for time and io are a valid part of a DBA interview. If the job will include any sort of tuning/optimization/code-review duties, of course. Those stats are important tools and I've been using them religiously in my query tuning ever since I found out about them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 9:27 am
Oliiii (5/23/2012)
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.
I have to question this methodology. Or at least this part "made the mistake". This assumes that a mistake was made instead of a change in process that perhaps queried on a column that would not typcially be indexed. Is that a mistake?
This requirement seems overly complex, YOU need to understand why it is acting the way it is acting otherwise you probably can't enhance performance.
Perhaps I am over thinking this, but this doesn't "feel" right to me and I can't quite quantify why..
CEWII
May 23, 2012 at 9:52 am
Elliott Whitlow (5/23/2012)
Oliiii (5/23/2012)
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.I have to question this methodology. Or at least this part "made the mistake". This assumes that a mistake was made instead of a change in process that perhaps queried on a column that would not typcially be indexed. Is that a mistake?
This requirement seems overly complex, YOU need to understand why it is acting the way it is acting otherwise you probably can't enhance performance.
Perhaps I am over thinking this, but this doesn't "feel" right to me and I can't quite quantify why..
CEWII
My problem with the "made the mistake" really comes down to this, who made the mistake. The person who wrote the original code or the tester that allowed it to make it into production? Or perhaps the people that reviewed the code?
Maybe it wasn't a mistake but the best effort of the individual developing the code based on their knowledge (SQL< application, data) at the time it was written. Sorry, I just think that the wording is wrong. I'm not going to critisize the work of others because I know that work I did years ago could now be done better. I will look at code and tell you if it can be improved, but I won't say that the original developer made a mistake.
May 23, 2012 at 9:59 am
The person who wrote the original code or the tester that allowed it to make it into production?
ahem.... the tester didn't put the defect there (the developer did) 😛
MVDBA
May 23, 2012 at 10:03 am
Lynn Pettis (5/23/2012)
Elliott Whitlow (5/23/2012)
Oliiii (5/23/2012)
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.I have to question this methodology. Or at least this part "made the mistake". This assumes that a mistake was made instead of a change in process that perhaps queried on a column that would not typcially be indexed. Is that a mistake?
This requirement seems overly complex, YOU need to understand why it is acting the way it is acting otherwise you probably can't enhance performance.
Perhaps I am over thinking this, but this doesn't "feel" right to me and I can't quite quantify why..
CEWII
My problem with the "made the mistake" really comes down to this, who made the mistake. The person who wrote the original code or the tester that allowed it to make it into production? Or perhaps the people that reviewed the code?
Maybe it wasn't a mistake but the best effort of the individual developing the code based on their knowledge (SQL< application, data) at the time it was written. Sorry, I just think that the wording is wrong. I'm not going to critisize the work of others because I know that work I did years ago could now be done better. I will look at code and tell you if it can be improved, but I won't say that the original developer made a mistake.
Or maybe it wasn't a mistake but was the best query for the requirement as it was at the time the query was written. So the guy who wrote it did what was best for teh company and got it absolutely right. Then, to Oliii's way of thinking, when later on the requirement changed so that that query was no longer optimal the guy who wrote it ceased to have got it right and started to have made a mistake.
Anyone who assumes that whenever something nneds fixing that means that a deveoper made a mistake gets a big down-tick from me.
Tom
May 23, 2012 at 10:07 am
michael vessey (5/23/2012)
The person who wrote the original code or the tester that allowed it to make it into production?
ahem.... the tester didn't put the defect there (the developer did) 😛
Bad assumption: maybe the analyst who wrote the requirement spec made the mistake, so that neither development (who wrote code that met the stated requirement and unit tested it thoroughly) nor QA (who did system tests that demonstrated that the code did indeed meet the stated requirement and behave ensibly in edge/stree conditions of the data, and performed even better than the stated performance requirement) made any mistake (except perhaps trusting management not to saddle them with responsability for someone else's mistakes).
Tom
May 23, 2012 at 10:08 am
michael vessey (5/23/2012)
The person who wrote the original code or the tester that allowed it to make it into production?
ahem.... the tester didn't put the defect there (the developer did) 😛
The tester may not have put it there, but they let it get by. 😉
Plus, is it really a defect? Isn't it possible that it met the requirements at the time?
May 23, 2012 at 11:34 am
Oliiii (5/23/2012)
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.
To add to the discussion on this point:
What do you do if the "dude" who made the original mistake is not available?
Q. Why it was like that?
A. Who knows?
Q. Why the dude developing it made the mistake?
A. Humanity is prone towards imperfection?
Honestly, you often won't know why something was coded a particular way, and stats and query plans won't discover it for you. You may not get as flippant as I do about answering silly questions like that, but you're going to be confronted by questions you actually can't honestly answer on those points.
What do you do if the code, as originally written, was error-free at that time and for forseeable events, but the query became slow because of unforseeable changes in data distributions? Is that an error? If so, the answer to the question about why the mistake was made in the first place has to be, "Nostradamus wasn't involved in the design/testing of the code."
On the point about the QA personnel not being the ones who made the original mistake, all that says is you don't use test-driven development, or, if you do, you're doing it wrong.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 11:37 am
Lynn Pettis (5/23/2012)
michael vessey (5/23/2012)
The person who wrote the original code or the tester that allowed it to make it into production?
ahem.... the tester didn't put the defect there (the developer did) 😛
The tester may not have put it there, but they let it get by. 😉
Plus, is it really a defect? Isn't it possible that it met the requirements at the time?
Random feature?
Seriously, the problem in my mind seems to be trying to place blame and that is not something that I really care about. If we need better test cases say that, if the requirements were weak say that. As Lynn said, code I wrote years ago is not up to MY standards today, but does that make it wrong? If it all of a sudden breaks was it wrong or did something else change?
I take the hard line that if you are more worried about who screwed up and not more worried about getting it fixed you have your priorities wrong. I worked for a fella years ago that was always concerned about who was to blame and it really irked me because I didn't care. If there was clear blame I would often just talk to the "offender" privately about it so they knew and we could work for better outcomes. Because in the end thats all I really wanted anyway..
CEWII
May 23, 2012 at 11:48 am
Elliott Whitlow (5/23/2012)
Seriously, the problem in my mind seems to be trying to place blame and that is not something that I really care about.
+100
Time spend trying to assign blame will be far better spent fixing the problem and learning from it to ensure it doesn't happen again.
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
May 24, 2012 at 1:26 am
GSquared (5/23/2012)
Oliiii (5/23/2012)
Where i work, when you fix something you have to usually show why it was like that, what you did to fix it and why the dude developing it (or the company that sold it) made the mistake, and to do that there is nothing more efficient than having statistics time & io and sometime the query plan.To add to the discussion on this point:
What do you do if the "dude" who made the original mistake is not available?
Q. Why it was like that?
A. Who knows?
Q. Why the dude developing it made the mistake?
A. Humanity is prone towards imperfection?
Honestly, you often won't know why something was coded a particular way, and stats and query plans won't discover it for you. You may not get as flippant as I do about answering silly questions like that, but you're going to be confronted by questions you actually can't honestly answer on those points.
What do you do if the code, as originally written, was error-free at that time and for forseeable events, but the query became slow because of unforseeable changes in data distributions? Is that an error? If so, the answer to the question about why the mistake was made in the first place has to be, "Nostradamus wasn't involved in the design/testing of the code."
On the point about the QA personnel not being the ones who made the original mistake, all that says is you don't use test-driven development, or, if you do, you're doing it wrong.
Most of the DB here comes from application bought or licenced, we don't have that much internal development for SQL and they very rarely have issues that requires our input.
We only work on issues that are threatening the servers stability/availability/performance or if someone specifically asks us to, so by definition when we are involved it is because a mistake has been made somewhere.
The mistake can come from poor development or wrong way to use the application.
By experience the mistakes comes from the way the application has been developed, they simply didn't plan or didn't have the skill to make an application that can handle the kind of load it's been sold to do (it doesn't matter if the problem comes from the dev or the commercial selling the application).
In some very rare case the business is pushing the application beyond the limit specified in the sla/contract.
I don't know anything about the way you need to work, but here if you fix an issue and simply say "it's fixed" is not good enough. The question that will come back 2 secs later is "what happened?".
And by that they mean what happened exactly, how did you fix it and what has to be done so we never have the problem anymore.
It's pretty easy to explain what you did to fix it or what need to be done to fix it, it's usually not hard at all to find out why it happened and it requires some experience to explain what need to be done to avoid the issue in the future.
Most of the time fixing the issue or avoiding it in the future requires the involvement of the company developing it and that's when you need rock solid proofs (yes, you always have to prove it's their fault).
There is no better proof than statistics before/after, sometime with query plan and observations with a couple of server performance stats/history.
With these proofs we can show the business the issue is not with the server or the way we manage it but with way the application is used or has been designed.
Once the business understand that, they can contact the application provider and see either if it can be fixed or if they have to either accept the issue and keep working, pressure the provider more, send their army of lawyer or simply start planing a migration to another application if there is one.
If the issue came from one of our mistake then we explain what happened, what we did to fix it and what we've put in place to avoid it in the future.
To answer the questions: What happen if the dude who made the mistake is not available?
That's actually way easier than you think, the business nearly always have the answer, if they have not then we make an educated guess.
While i agree the human is prone to imperfection that's not an excuse for not fixing mistakes and that's exactly why you need to have all the infos on the issue.
While we are at it i totally disagree with the fact that time spent assigning blame would be better used fixing the issue.
First thing is you can do both at the same time so time is not really lost and i would go as far as saying pointing fingers in a constructive way is more important than the fix itself.
Any DBA can fix an issue or resolve an incident, but the only way permanently fix an issue is to know where it comes from and why it happened and that's not something any DBA can do.
So assigning blame and making sure it's handled correctly is paramount in a big company.
May 24, 2012 at 2:36 am
Lynn Pettis (5/23/2012)
michael vessey (5/23/2012)
The person who wrote the original code or the tester that allowed it to make it into production?
ahem.... the tester didn't put the defect there (the developer did) 😛
The tester may not have put it there, but they let it get by. 😉
Plus, is it really a defect? Isn't it possible that it met the requirements at the time?
it wasn't just the tester that let it go by, it was the developer, the tester and the SME that did UAT.
testers are like goal keepers, a defect has to get past midfield, the defence, the offside trap and cross the line inside the posts before it's an issue.....
in this analagy the deveopers are the opposition strikers and the extremely wooly business requirements are the opposition midfield , making the pass to the forwards 🙂
MVDBA
May 24, 2012 at 12:47 pm
So assigning blame and making sure it's handled correctly is paramount in a big company.
This may be one of the scariest statements I have seen in a while. I would hate to work for a company that fosters this kind of attitude. I work for a big company and we focus on fixing the issue and if we can educate someone in the process then it was a success. I feel that companies that play the blame game promote a culture where individuals feel that they cannot make mistakes causing the individual to try and hide mistakes. By focusing on fixing the issue and education, we promote an open culture where people freely admit mistakes because they are not worried about getting blamed.
May 24, 2012 at 2:02 pm
The mistake can come from poor development or wrong way to use the application.
By experience the mistakes comes from the way the application has been developed, they simply didn't plan or didn't have the skill to make an application that can handle the kind of load it's been sold to do (it doesn't matter if the problem comes from the dev or the commercial selling the application).
In some very rare case the business is pushing the application beyond the limit specified in the sla/contract.
This part scares me too. You pay for a 3rd party application to accomplish a task, and it accomplishes this task. Then you use it in a way that was not anticipated by the vendor and now you are telling the vendor they didn't know what they were doing? That is arrogance. Most times, what I have found is that the users push the system to the limits, and when it doesn't do exactly what they want, they find ways around the system. This usually works for a while but it usually catches up with them and the system starts to fail. Are you still going to blame the vendor and their developers for not anticipating what the users might do?
Viewing 15 posts - 151 through 165 (of 187 total)
You must be logged in to reply to this topic. Login to reply