August 23, 2010 at 8:41 am
Hiya,
Our company has a VB6 application which, amongst other things, runs some fairly heavy queries against a SQL database for reporting purposes. They have expressed a desire to use the Kill command to cancel any queries that are running where the user has chaned their minds. I'm more than a little nervous about this, not least because of potential rollbacks. Although they are only select queries, there's the possibility of them using tempdb and so the cancellations could have to rollback transactions there. And in any case, I would have expected the connection to SQL to be dropped when the "con.close" and "set con = nothing" are called (the app uses ADODB).
Has anybody come across a request like this before?
What are your thoughts?
Thanks
Martin
August 23, 2010 at 9:54 am
Yes I have and my answer at the time was no...
You could look at offering it if they are using a read only connection or something, but yeah the rollback could be a killer.
August 23, 2010 at 10:00 am
It's not something I would be happy with.
Have you had a look at the queries and tried the standard tuning steps on them?
they could possible be missing a couple of important indexes or a query re-wite. I would look at optimising the queries first before trying anything more radical.
August 23, 2010 at 10:17 am
KILL should only be done manually by a trained DBA on a production environment. Period. A user app should NOT be able to kill processes. Too unpredictable as to the results.
August 24, 2010 at 7:31 am
Thanks for your feedback, guys - that confirms my opinion. Is anybody able to give me definitive reasons that I can go back to the dev team with to say "This is why you shouldn't do it...." (I'm aware of the rollbacks in tempdb, of course).
Thanks again
August 24, 2010 at 8:08 am
A kill can have block implications outside of the realm they are aware of.
Depending on the nature of how connections are made there is a risk you may kill something that you do not intend to kill. (killing the wrong spid because you stuff is in fact done and something else is now using that spid).
Since the KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable.
It would require those user connections to have rights sigificantly beyond the scope needed for other activities on those accounts, connections.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply