Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How can I kill ad-hoc or long time running queries, safely? Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 2:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
GilaMonster (5/9/2013)
Correct, it's CPU and RAM, but how does killing a query that's done a lot of physical IO help? The IO's already done by the time you kill the query, the stuff that it read already in memory.


Those ad-hoc queries are hammering the SAN really bad (tempdb and the actual Data LUN) and they keep running for ever, affecting other clients as well.

Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.
Post #1451323
Posted Thursday, May 9, 2013 4:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
sql-lover (5/9/2013)
Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.


then the user will re-run the query and it'll hammer the SAN for another minute, be killed, be re-run, be killed, repeat until the user gets fed up and complains to management that they can't do their work.

You're addressing the symptoms, not the problem.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451374
Posted Thursday, May 9, 2013 7:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
GilaMonster (5/9/2013)
sql-lover (5/9/2013)
Killing those ad-hoc queries after X minutes will release the IO pressure. So instead of hammering the SAN for 5+ minutes, if they are not done my 1 min or less , they will be killed.


then the user will re-run the query and it'll hammer the SAN for another minute, be killed, be re-run, be killed, repeat until the user gets fed up and complains to management that they can't do their work.

You're addressing the symptoms, not the problem.


Hmmm... now I see your point.

Let me play a bit with resource governor. But something it is clear to me, I do not want to limit or affect other users or clients, which shares the same instance, so need to test this carefully.
Post #1451397
Posted Thursday, May 9, 2013 9:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.

The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1451403
Posted Friday, May 10, 2013 7:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
Jeff Moden (5/9/2013)
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.

The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea.


Ideal solution, but not possible.

This is a special type of ad-hoc feature of report that allows clients to run their own reports (customize them) Is an added feature and it's not free. It won't be discontinued anytime soon. Moreover, it may be expanded or sold to other big clients as well. A Developers idea, you know .... ... not a DBA idea ...

The good thing is that I do not see many clients using it, due cost vs benefit. Only 5% or less of our clients.
Post #1451599
Posted Friday, May 10, 2013 7:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
sql-lover (5/10/2013)
Jeff Moden (5/9/2013)
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.

The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea.


Ideal solution, but not possible.

This is a special type of ad-hoc feature of report that allows clients to run their own reports (customize them) Is an added feature and it's not free. It won't be discontinued anytime soon. Moreover, it may be expanded or sold to other big clients as well. A Developers idea, you know .... ... not a DBA idea ...

The good thing is that I do not see many clients using it, due cost vs benefit. Only 5% or less of our clients.


So how do you think the clients that are paying for the ability to run these reports are going to feel about being cut off after waiting for several hours? You'll need to find out what the long running reports actually are and do some serious tuning or, perhaps, build a set of regularly updated tables that are designed to handle the reporting better.

The problem with just cutting them off is the customers will be disappointed and stop paying, spread the word that your company doesn't meet what they said they were going to do (which is much more damaging than most will ever imagine), a the cutoff may left a "0% to go" rollback that will never end but continues to consume CPU (a fairly well known and documented fault with SQL Server).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1451601
Posted Friday, May 10, 2013 8:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 2,832, Visits: 8,509
Bigger picture: Propose to management that a reporting database be created and updated (preferably on a different server) and let users query against that database instead. Still not ideal to let users write queries, but it would take the load of the existing database.


Post #1451611
Posted Friday, May 10, 2013 9:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
Jeff Moden (5/10/2013)
sql-lover (5/10/2013)
Jeff Moden (5/9/2013)
I wouldn't do any of that because it doesn't address the basic problem of users writing bad code for their reports. Instead, find the users that are writing the code and show them what's going on and how to kill their own runs. If you help them with tuning their code a bit, the problem will go away.

The real problem, of course, is the original sin of letting users write their own code. For a week, let the runs finish, collect the data, and submit a proposal to disallow it in the future. If management refuses, then just let them run until they get the idea.


Ideal solution, but not possible.

This is a special type of ad-hoc feature of report that allows clients to run their own reports (customize them) Is an added feature and it's not free. It won't be discontinued anytime soon. Moreover, it may be expanded or sold to other big clients as well. A Developers idea, you know .... ... not a DBA idea ...

The good thing is that I do not see many clients using it, due cost vs benefit. Only 5% or less of our clients.


So how do you think the clients that are paying for the ability to run these reports are going to feel about being cut off after waiting for several hours? You'll need to find out what the long running reports actually are and do some serious tuning or, perhaps, build a set of regularly updated tables that are designed to handle the reporting better.

The problem with just cutting them off is the customers will be disappointed and stop paying, spread the word that your company doesn't meet what they said they were going to do (which is much more damaging than most will ever imagine), a the cutoff may left a "0% to go" rollback that will never end but continues to consume CPU (a fairly well known and documented fault with SQL Server).


Jeff,

I understand your point, you are right.

The thing is, just few users from those clients are abusing of this feature, so we just want to be proactive. And, management has decided to move forward with this. Actually, it has been in place for a while.

This is more a code issue than anything else. I can give advice, but I have no control over it.
Post #1451638
Posted Friday, May 10, 2013 9:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
homebrew01 (5/10/2013)
Bigger picture: Propose to management that a reporting database be created and updated (preferably on a different server) and let users query against that database instead. Still not ideal to let users write queries, but it would take the load of the existing database.


Did that already.

At this point, we have no additional hardware, space and resource for that.

Good advice though.
Post #1451639
Posted Friday, May 10, 2013 1:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 411, Visits: 1,310
Is Resource Governor supported on SQL 2012 Standard edition?

I spent a lot of time testing today, Dev environment, and it looks this feature is only available on SQL 2012 Enterprise, is that correct?

It worked as expected though! ...
Post #1451726
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse