How can I kill ad-hoc or long time running queries, safely?
-
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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.
-
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.
-
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.
-
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! ...
-
sql-lover (5/10/2013)
Is Resource Governor supported on SQL 2012 Standard edition? 🙁Unfortunately not. From Features Supported by the Editions of SQL Server 2012:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Read up on the SET QUERY_GOVERNOR_COST_LIMIT setting, which can be configured at either the session or server level. The threshold value specifies the maximum number of seconds for which a query is allowed to run. Also, if SQL Server's estimated query cost (based on statistics and exection plan) exceeds the specified threshold, then the query will be cancelled without running.
http://msdn.microsoft.com/en-us/library/ms190419(v=sql.105).aspx
You stated earlier that you don't want to setup resource governing at server level, so perhaps setting query cost limit at session level is what you need. This can be implemented in a number of ways. Within SSMS, there is an option called "execution timeout" under Tools.. Options.. Query Execution, which I think is the same as QUERY_GOVERNOR_COST_LIMIT. You *may* also be able to set this within an application's connection string or via login trigger, but I've never used this thing, so I'm not sure.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
I hope these ad-hoc queries are not running against your primary operational database. If so, then as suggested earlier, speak with your manager about implementing a replicated instance. Also consider implementing some parameter driven stored procedures and summary tables to facilitate the more common reporting needs of the clients, and let them know that ad-hoc queries will be capped based on their cost estimation.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
sql-lover (5/10/2013)
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.
Find out whether the income from the "extra cost" ad-hoc capability exceeds the cost of setting up the separate hardware. If so, you've got a good business case. If not, you've got the usual business issues of selling losses.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply