| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2009-07-15 : 09:21:17
|
| How can I prevent killer queries from slowing our server down?We have a couple of peeps in other departments that access our server and their queries hog all the resources, is ther a way of limiting how much cpu useage they have or can I do it some other way? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 09:44:48
|
| The easiest way is not to let them have access to a production server. Give them a test server, especially if they can't write effecient queries. The other way is to teach them how to write decent SQL. |
 |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-07-15 : 09:52:38
|
| Yes the trouble is they want access all the time and we only have the one server.Is there no way of limiting their access or time them out? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-07-15 : 10:03:42
|
| Yes, thanks, I looked at that and I was hoping it could be done per user or group rather than across the board. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-07-15 : 10:16:51
|
| Hmmm....you could maybe write a script that would check active process for their login and then kill any of their sessions that have been open beyond a set limit.Intriguing....________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-07-15 : 10:21:16
|
| Intriguing, bloomin annoying I find them - lol, these people (finance) are really geting to me, 'those that hold the purse strings' and all that.Hope someone can come up with something! |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 10:35:15
|
You don't work for a public sector client by any chance? Just i've got the same kind of thing at the moment, but have put my foot down and now they've got their own instance to screw up..Oh bTW, its the job of a DBA to put your foot down and tell them what will be what. When they say, but we hold the purse strings, or the classic 'but its our data', the easiest response is that its not their data, its the companys data and you have been employed to look after it and make sure it is secure.. |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-07-15 : 10:39:16
|
| Oh, if onlt it was that easy, when my boss is on their side too. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-15 : 10:45:17
|
| I would ask him what he employed me for then? But then again, being a contractor, you can get away with that more.The other thing to do is suggest that they work off a mirror if they are just doing selects, then put that on a heavily limited instance on the same box. |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-07-15 : 15:14:46
|
| Simply assign the cost of a slow server to the individuals that cause the problem.If 3 people running queries slow the productivity of everyone else by half you should be able to make a case for keeping them out of the server. If the purpose is unavoidable, then compare the cost of the lost productivity against another SQL server or a much beefier one designed to handle fail queries. If you double the payroll cost of even a small department, the hardware cost will be insignificant.Then of coarse they may just replace the bum employees with someone who knows what they are doing.That's how I justify my hardware. |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-07-16 : 01:26:40
|
| I'm running a couple of traces so I can point the finger at the worst performing queries, maybe this''l open my bosses eyes to whats happening. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-07-16 : 02:58:42
|
| The best solution would be to put up an additional server and replicate all the required data there and they could go nuts with their queries. You could even by a desktop pc for probably less than 500USD and put you data there...financing departments usually don't mind all that much if their queries take 5 seconds or 5 minutes. Another option could be to grant their database users execute permissions on certain stored procedures only, that you have optimized for them. But that all depends on how ad-hoc their queries are...- Lumbago |
|
|
|
|
How do I prevent killer queries ?