A few days ago there was a question in Twitter about options to KILL (SPID) with a long running transaction that was causing a lot of blocking in a mission-critical system. The person asking the question got some helpful tips on how to fix the problem, such as looking at the tables and the indexes and some tools were pointed out to him, like Adam Machanics Sp_WhoIsActive.
While these all were sound advice they didn’t really offer much of an opinion to using KILL, not that there are that many. In fact your only option to KILL is to wait it out.
If that doesn’t sound like a good option, consider this. If you have a transaction that’s been running for a fifteen or so minutes and you KILL it, it’s going to perform a ROLLBACK which will take equally long time. In some cases it might even take longer because the ROLLBACK process is single threaded where your aopplication might have used multiple threads for the original transaction.
If you’ve killed a long running process and it seems to take forever you can try and use KILL WITH STATUSONLY to get an estimate on how long it’ll take. If you have, for example, killed a process 71 with command:
KILL 71;
You can see and estimate on how long the rollback will take with command:
KILL 71 WITH STATUSONLY;
In short: I would advice caution before using KILL, but there are times when it simply cannot be avoided. On the other hand if you need to do it often, then I’d suggest what the other folks suggested in Twitter. Take a look at the query, the table and the indexes to reconcile the issue. If it’s something that cannot be fixed by optimizing, such as reporting workloads on huge tables it might be something that should be offloaded to reporting instance.
Leave a Reply