| Author |
Topic |
|
vk59
Starting Member
38 Posts |
 Posted - 2006-02-03 : 05:32:46
|
| Below is an interview. Can you pls tell me the answerQ:What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “A query that ran yesterday took 30 seconds, but today it takes 6 minutes".` |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-03 : 06:44:16
|
| Ask what's changed on the system. 'Coz its gonna be something to reduce yesterday's query by a factor of 12!!Kristen |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-03 : 08:08:45
|
| Probably find out if it is a problem or not. If not then spend your time on things more worthwhile.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-03 : 08:29:05
|
| Hehehe ... nothing like customer service, eh Nigel?!What did you say vk59?Kristen |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-03 : 08:34:28
|
| Also refer here for further inspiration.Saw this over SQL-Server-Performance.com----------------------------------'KH' |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-02-03 : 09:20:57
|
I liked this quote:quote:
If your row count is best represented in scientific notation, you should probably recheck your design.
Corey Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."  |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-02-03 : 13:32:27
|
| The first thing I would do is put the database into single user mode and then run the query. That would certainly speed things up a bit for that query. The second thing I would do is ask the boss a question that would establish for me whether he's a liar/clueless or not to establish whether or not he's got the integrity or brains to make the determination of what 30 seconds yesterday, or 6 minutes today means.Now after having fun, I'd probably ask some serious questions. But the problem is that the question already pre-assumes a response since they wanted just the "first thing" and not "what my approach would be" so I'm sure that I'd ask the wrong "first" question. I would start with "Was it exactly the same query, that returned exactly the same number of rows?" "What time was it run?" (to establish if it ran in 30 seconds 5 minutes after lunch time, and it took longer during the peak time.) Then I'd probably ask questions about the network and not the server at all, assuming that if nobody was screwing up the server, it was probably a slow network issue transferring large amounts of results back to the client or something. Once I narrowed down that it had to be the server, and that I'd have to accuse the "SQL Administrator" ... I'd ask "Did some moron delete an INDEX by chance?"That's my story and I'm sticking to it,Dalton |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-03 : 13:38:59
|
| Could say.Update statistics and recompile sp then see what happens the next time it runs.That's if you can do such things on that server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 06:12:52
|
| "Update statistics and recompile sp then see what happens the next time it runs"That implies that a shed-load of data would have been added [to drive a query from 30 seconds to 6 minutes], wouldn't it?Hence my point "Ask what's changed on the system". I'd be horrified if enough data could be added to wreck a query in that way that wasn't either a) planned for in normal operations or b) sorted out (or alerted to) by routine overnight maintenance.So that would come back to not wanting to take the job because their existing setup is shoddy.So my answer should have been: "Could that really happen here?" and if the answer is "yes" then either be very concerned at the lack of systems they have in place, or want a large budget and salary to sort it out!Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-04 : 10:11:32
|
| >> That implies that a shed-load of data would have been added [to drive a query from 30 seconds to 6 minutes], wouldn't it?Not relly. It's the data distribution that's important compared with the values that the query is using.Doesn't take as much as you might think - often less than the default to re-calculate statistics.What could have happend is that the data changed a lot over a long period but the query plan has never been recalculated, the server gets a reboot and the plan recreated - but now on statistics that are months old so gets a fifferent query plan.If you haven't much time to spend it's always worth doing a reindex and recompile or clearing cache.But of course if the problem is at all critical you should do something a bit deeper.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 11:50:19
|
| Very useful insight, thanks Nigel.Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-02-04 : 11:53:44
|
| I still vote for some one having removed an index. I can't think of any other normal operating conditions that would so drastically change the time. I could imagine a lot of data changes occuring, but I couldn't imagine that happening one day out of the blue, and not being a common occurance such that they would have rebuilds for that index on a regular basis then. I suppose that Tara's statement "Ask what has changed since yesterday" would cover the index situation and others as well. But I'm sure if that was the response their response would then be "if we knew what changed since yesterday we'd be able to fix it ourselves." Such a tough question for an interview because it can be so "over thought" and no matter what the "first" guess is it could be wrong in their mind. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 12:06:27
|
I vote for poor infrastructure"I still vote for some one having removed an index"Change control procedures should pick that one up. (Well, our's wouldn't have, but I'm going to build it in now!)"Such a tough question for an interview because it can be so "over thought""Great opportunity for a smart reply though. "OK, no sweat. What have you guys considered so far and what infrastructure do you have in place to catch/alert the common things that might cause this type of f***-up?"Actually, I'll split the consultancy with you:"My God, this is a major F-up. Lets get my mate Kristen in, he only charges $2,000 * X [multiply by your "cut" ] and he'll sort it out for once and for all" Perhaps I should attend some interviews, they sound jolly entertaining!Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-04 : 12:10:18
|
| A lot depends on the environment.Where I am now dopping an index would be very unlikely - adding useless indexes not so uncommon though.But queries do suddenly take a lot longer occasionally.The response to that depends on the query. Usually it's an overnight batch thing and you can't run it during the day so apart from comparing test and live estimated query plans you can't do a lot unless it's important enough to watch it running overnight.It's quite a good interview question as it doesn't have a correct answer but should give an idea about the interviewees approach and experience.Would be a very poor written question though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 12:22:28
|
| "It's quite a good interview question as it doesn't have a correct answer but should give an idea about the interviewees approach and experience."Yes, all joking apart I thought that too.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-04 : 12:41:57
|
| Came across another one in a forum recently which I might use in an interview.How do you create an identity value in a result set from an SP.It's good in that it should evoke a discussion about what you are trying to do and why rather than attempt a solution.FirstlyDo you mean a sequence number?Why do you want one?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-04 : 13:00:58
|
| It might be good as an interview question, but you'd get a gruff answer if you posted it on SQL Team!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-05 : 00:06:17
|
| It is a good question, because it reveals a lot about how valuable you would be to them. If your approach shows a tendency to dismiss it as unimportant, blame factors not under your control, blame others, say it's impossible to find out, or anything else that shows you wouldn't be able to do the job, well, that's what interviews are for.The first thing I would do is gather as much information as possible about the situation, because that will give you the most clues about the reason it is going wrong and the possible solutions. Ask questions like: When did the problem start? Does it happen all the time to the same query? Are other queries having a problem also? Does it only happen at certain times of day? Only happen from certain clients? Was there any unusual activity on the system or network? Where there any system software or hardware changes? Was the query or stored procedure changed? Were the tables changed? Were indexes added or dropped? Were large amounts of data added or removed?CODO ERGO SUM |
|
|
|
vk59
Starting Member
38 Posts |
Posted - 2006-02-06 : 06:17:56
|
| Thank you all for valuable comments.` |
|
|
|
|
Interview question