SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I kill ad-hoc or long time running queries, safely?


How can I kill ad-hoc or long time running queries, safely?

Author
Message
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86639 Visits: 45253
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, MVP, M.Sc (Comp Sci)
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


sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85172 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
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 .... Ermm ... 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85172 Visits: 41077
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 .... Ermm ... 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4793 Visits: 9108
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.



sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
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 .... Ermm ... 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.
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
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.
sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 1930
Is Resource Governor supported on SQL 2012 Standard edition? Sad

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! ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search