Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kiwidude
Starting Member
2 Posts |
 Posted - 2007-08-25 : 06:23:12
|
| We are trying to find a way that we can get our DBAs to agree to that will allow us to KILL long running queries from our application code.In the dev environment life is relatively easy - you can execute a KILL against the spid of your session as we have dbo rights.However in UAT/production of course our application servers run under a database role which has far more limited permissions, amongst which is the lack of permission to execute a KILL.I cannot believe we are the only people to hit this problem and desire a solution. Our N-tier application is written in .NET. We cannot use the "Cancel" feature of ADO.Net, because we have multiple application servers servicing the clients using a (highly customised) version of the Microsoft Data Access App Block. Ado.Net Cancel requires "holding on" to your SqlCommand object reference - something which we don't do as it is abstracted. Even if we were to put some nasty in like a static cache of active SqlCommand objects of course in a load balanced server scenario the request to cancel from the client may not even come back to the same server.So that left us with an idea to store the spid information in the database with a unique query id which is returned to the client - the idea being that if the client issues a cancel request it passes that query id, we open a new session, look up the spid from our running query table for that query id and if the session still is running then "KILL" it. This "wrapper" logic would only be used with a few selected browser screens running SELECT queries, not "every" query against our database.As stated above that KILL is the sticking point. In an ideal world we would have some extended stored proc which the DBAs own that we are able to call passing the spid, that is a wrapper around KILL. However is it even possible to write and permission a sproc to do what we want (i.e. be called by an account with a different role that does not have that KILL permission)?The only other alternative that has been mooted is to have a separate "service" application, owned by the DBAs which runs with sufficient dbo priveleges and exposes via a web service for instance the ability to kill a spid on a database. However that is obviously dramatically increasing the complexity of the overall solution and there are other issues it opens up internally over security, ownership, resourcing, hosting etc etc.Desperately hoping someone out there has some suggestions... I have searched the forums and found nothing mentioned I could see. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-25 : 11:57:02
|
| As a DBA, I wouldn't allow this for security reasons. According to SQL Server 2000 Books Online, "KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable."I would figure out why you need the ability to kill queries and fix that situation by either rewriting the queries, adding statistics, defragmenting indexes, denormalizing the schema, or perhaps replicating the data to another database. There's just so much that can be done to help long-running queries that all of those avenues should be looked at first.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
kiwidude
Starting Member
2 Posts |
Posted - 2007-08-25 : 12:40:58
|
| Hi Tara,The problem is that the queries are built dynamically - these are query screens where the GUI allows the user to choose all sorts of combinations of filter criteria be it date ranges, attributes etc. Sometimes the users have genuine reasons for doing some specific long running queries, so we cannot just simply look for certain combinations "missing" and block the user from doing those actions. Repetitively throwing modal dialogs in users faces warning them their query may take a while to run will do nothing but irritate them and achieve nothing.Building indexes on every single field obviously isn't an option - and while we certainly do our best to optimise what we can there are always limits to what is possible. These are mostly real-time financial/trading applications. The replicated database scenario is also not an option (this particular organisation does not use/trust SqlServer replication, which means a mammoth development effort to attempt this ourselves programmatically, plus it opens all sorts of other cans of worms).Your points are all totally valid and obviously we do our best to optimise what we can. However sometimes due to the tremendous amount of data involved there isn't any choice but to "suck it up" as an expensive query. Also as developers mistakes can always be made - a missing index for instance could bring a production system to its knees. Until the change request processes grind their way through allowing the fix to be redeployed to production the system's uptime is fragile, resulting in the potential for phone calls to the DBAs to cancel processes etc.Also what is termed as "long running" is questionable. Sure we could have some queries which bring the system to it's knees, but even something which takes 15 seconds can be way too long for a trader wanting to change his criteria and resubmit. In these circumstances we want to have the fallback option available of cancelling the query, rather than just letting things "run to completion" in the background soaking up valuable server resources.Our DBAs have agreed to the idea "in principle" as it could reduce support effort for them. We are only suggesting using this for particular browser screens with read-only SELECT queries, certainly never for anything involving transactions or updates. The user will only be able to cancel their own query. Your comment about the sysadmin / processadmin roles is exactly the issue we need to find a way "around" as obviously it is not an option for our application account to belong to that role. I'm obviously not a DBA myself, so putting my numpty hat on I'm curious as to what the options to achieve this we have are - for instance is the only option going to have to be a standalone "process killer" app/service running under an account that does belong to that role, or is there any other options at all?Many thanks for your time and thoughts. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-25 : 14:40:54
|
| Since it is read-only SELECT queries, you really should look into replication. It is quite easy to setup. The DBAs who do not trust replication probably haven't ever used it or haven't used it correctly. We use it for multiple mission critical applications and it works. Perhaps the DBAs just need to get some training on replication in order to trust it, although training is going quite overboard for such an easy thing to setup. There is no way around the permissions thing as the permissions are not transferrable, per BOL. You'll have to see what the DBAs agree to as far as standalone applications or services that can do the work.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Cancelling queries programmatically