Dangers of running this stored procedure that kill blocking processes

Question

use master
GO
CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL)
AS

DECLARE @count int, @spid int, @sql nvarchar(max)
SET @count = ISNULL(@RecursiveCount, 3)

while @count > 0
begin

 begin try
  set @spid = (
   select top 1 spid from sysprocesses (nolock)
   where blocked = 0 and spid in (
    select blocked from sysprocesses (nolock) where blocked <> 0
   )
  )
  if @spid > 50
  begin
   set @sql = N'kill ' + cast(@spid as nvarchar(100))
   exec sp_executesql @sql
   --print @sql
  end
 end try
 begin catch
  --print 'error'
 end catch

 set @count = @count - 1
end
GO

I saw this stored procedure, and I am wondering if there's any danger of running this stored process whenever the db is frozen. My thought process is that you still need to kill the blocking process even if the process you kill is important, so is there really any consequence of doing this? And what's the point of knowing which process is freezing up the db?

Answer 1

Blocking doesn't "freeze up the db". It causes those who happen to be blocked to be ...blocked until the one holding the lock that causes the blocking situation to release that lock. That is not the same thing as "freezing up the db".

The danger of that procedure it that kills any blocker. The one that it kills might be a very transient blocker, where the blocking situation would only happen for 100 ms, but you happened to run that proc just those 100 ms. So, you killed somebody without gaining anything, since the real problem wasn't taken care of. And you now run it again, with the risk of doing this yet again.

Don't just kill anybody who happen to block somebody else. Kill with care!

Answer 2

To add on to Tibor's answer.

Blocking is normal in a database and occurs all the time. Where blocking becomes a problem is when it happens for long periods of times, or they end up in a deadlock. Though we'd like to avoid deadlocks, SQL will choose a victim, and they'll solve on their own. So, that's a different topic.

Now, let's say I have transaction that is commonly blocking for 5-10 minutes, and many transaction wait during that time. With this procedure, you'd be blinding killing the offending session, and never know you had done this, or that you had blocking. As a DBA, I want to know when I have to kill a session. I want to access the risk of killing the session and the pending rollback. I also want to be to look at what may need to be changed to prevent the same session from blocking for a long periods of time again in the future.

Additionally, anything the transaction was doing now needs to be rolled back. Rollbacks are single threaded. This means, in theory, that if you have 8 CPUs and your transaction was going parallel and had been running for 1 hour, it might take 8 hours to rollback.

If you're concerned with blocking going undetected and wrecking havoc on other process, invest in a good monitoring tool and have it send you an alert when blocking has occurred longer than a defined period of time.