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
Change is inevitable... Change for the better is not.