July 4, 2015 at 12:43 pm
The other day I was asked to build a SQL Server process to terminate blocking sessions that could be safely destroyed in order not to drain necessary instance resources. The solution is made of below stored procedure and by a SQL Server Agent jobs that runs every 3 minutes just to invoke the sproc.
The T-SQL code should be easy to read and has plenty of remarks. Any doubts please post here for extra info.
USE [<<yourDBAgoodStuffDatabase...>>]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[font="Courier New"]--
-- ===============================================
-- Author: Paulo A. Nascimento ©
-- Create date: 8th May 2015
-- Description: A blocking sessions terminator usp
-- ===============================================
--[/font]
CREATE PROCEDURE [dbo].[usp_Unblocker]
AS
BEGIN
DECLARE @kMinWaitDuration smallint = 30000; -- 30 seconds, change as necessary
SET NOCOUNT ON;
IF ( (
SELECT COUNT(*) FROM sys.dm_os_waiting_tasks as A
JOIN master.dbo.sysprocesses as B
ON A.blocking_session_id = B.spid
WHERE ( blocking_session_id IS NOT NULL )
AND (wait_duration_ms > @kMinWaitDuration) -- only blockings whose duration is bigger than N seconds...
AND hostname like 'AnnoyingServer101'
OR ( blocking_session_id IS NOT NULL AND (wait_duration_ms > @kMinWaitDuration)
AND LEFT(hostname, 3) LIKE 'LAP' ) -- no laptops allowed to block our beloved SQL Server... business internal nomenclature for notebooks, adapt as needed
OR ( blocking_session_id IS NOT NULL AND (wait_duration_ms > @kMinWaitDuration)
AND LEFT(hostname, 3) LIKE 'WKS' ) -- no workstations allowed to block our beloved SQL Server... business internal nomenclature for client PCs, adapt as needed
--
-- ...
-- ADD/REMOVE business rules according the context of your organization!
--
) > 0 )-- Is there blocking happening right now ? Yes, so let's kick ***...
BEGIN
SELECT TOP(1) @blocking_session_id_ = blocking_session_id,
@wait_duration_ms_ = wait_duration_ms,
@session_id_ = session_id,
@wait_type_ = wait_type,
@lastwaittype_= B.lastwaittype,
@DBname_ = DB_NAME(B.dbid), -- as DBname
@login_time_ = B.login_time,
@last_batch_ = B.last_batch,
@open_tran_ = B.open_tran,
@status_ = B.status,
@hostname_ = B.hostname,
@program_name_ = B.program_name,
@cmd_ = B.cmd,
@loginName_ = B.loginame,
@sqlHandle_ = B.sql_handle,
@dateTS_ = getdate()
FROM sys.dm_os_waiting_tasks as A
JOIN master.dbo.sysprocesses as B
ON A.blocking_session_id = B.spid
-- We want to keep an historic of offending sessions and originators
INSERT INTO [dbo].[blockingTable] VALUES
(
@blocking_session_id_,
@wait_duration_ms_,
@session_id_,
@wait_type_,
@lastwaittype_,
@DBname_,
@login_time_,
@last_batch_,
@open_tran_,
@status_,
@hostname_,
@program_name_,
@cmd_,
@loginName_,
@sqlHandle_,
@dateTS_
)
TRUNCATE TABLE #blockingTableAux] -- We clear temporary/auxiliary table (clone of blockingTable table so it keeps track of only current SPIDs...
INSERT INTO [dbo].[blockingTableAux] VALUES -- Holding the session details for post-processing...
(
@blocking_session_id_,
@wait_duration_ms_,
@session_id_,
@wait_type_,
@lastwaittype_,
@DBname_,
@login_time_,
@last_batch_,
@open_tran_,
@status_,
@hostname_,
@program_name_,
@cmd_,
@loginName_,
@sqlHandle_,
@dateTS_
)
-- Start of demolition loop
DECLARE @umSPID int = 0;
DECLARE @SQLstatement nvarchar(10); -- should be enough for a string like 'kill 65535'...
DECLARE @contador smallint = ( SELECT COUNT(*) FROM #blockingTableAux );
WHILE ( @contador > 0 ) -- while temporary table blockingTableAux is not empty...
BEGIN
SET @umSPID = (SELECT TOP (1) dbo.blockingTableAux.blockingSession_id FROM dbo.blockingTableAux);
SET @SQLstatement = 'KILL ' + CAST( @umSPID as nvarchar(5) );
EXECUTE(@SQLstatement) -- executes the dynamic SQL generated in the line above...
DELETE
FROM #blockingTableAux
WHERE #blockingTableAux.blockingSession_id = @umSPID
SET @contador = @contador - 1;
END
END
END
July 4, 2015 at 1:59 pm
That's not, in general, a very safe thing to do. You have no idea how long the session that you're killing has been running, you have no idea how important what it's doing is and you have no idea whether whatever it's doing is correctly using transactions or whether killing it part way through will leave the data in an incorrect state.
Basically, you could end up with really long rollbacks, you could end up killing very important processes, you could end up messing up the data if the people who wrote the code didn't use transactions properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2015 at 8:27 am
Yesterday I felt inclined, in general, to agree with you but in the particular that cannot happen because you have no idea of the context and therefore you cannot generate for free such secure and bold opinions, and this why:
1. The session (except for the 1st one, in theory), runs at the most for 180 seconds (tops) with current parametrization;
2. The session is an IBM Cognos BI read-only query or Office query (basically a DML select so no harm done).
3. I have mechanisms in place to detect early corruption (SQL Server alerts) if it was applicable, which is not the case.
4. Doubt explicit transactions are used at all;
5. Last but not least, this was requested by and for the business, so pertinent audiance has knowledge that this solution is a fair trade-off;
By the way, what your alternative solution would be ?
Thanks for your feedback.
July 5, 2015 at 8:51 am
Paulo A. Nascimento (7/5/2015)
you cannot generate for free such secure and bold opinions
Actually, I can.
Because I know how SQL works and because I've seen people try this before. Because I've seen the screw ups that can be caused caused by automatically killing sessions and because I've cleaned them up before.
1. The session (except for the 1st one, in theory), runs at the most for 180 seconds (tops) with current parametrization;
"Except for the first one", the first one being the blocker, it's probably a data modification (based on what you said below about the queries being selects), which means the rollback will take longer than the query ran for. And while the current queries may run no more than 3 minutes, that's no guarantee that they will continue to do so.
Also, you presented this as a general solution, hence I commended on it as a general solution.
2. The session is an IBM Cognos BI read-only query or Office query (basically a DML select so no harm done).
If you only have read only selects, then you cannot have blocking. Selects take shared locks and shared locks are shared, meaning any number of sessions can take shared locks on the same resource. To have blocking, you have data modifications going on. It'll be likely that the data modification is the head blocker (because shared locks are shared) and so you're more likely to kill a data modification than anything else
3. I have mechanisms in place to detect early corruption (SQL Server alerts) if it was applicable, which is not the case.
Killing sessions can't cause corruption, so irrelevant.
4. Doubt explicit transactions are used at all;
Yup, that's what I expected and meant by "you could end up messing up the data if the people who wrote the code didn't use transactions properly."
Without transactions, all data modifications are auto-committed, so if you have a set of data modifications that all must succeed and they are not wrapped in a transaction and you kill the session, you can end up with only some of them running. And that's no fun at all to clean up after the fact
5. Last but not least, this was requested by and for the business, so pertinent audiance has knowledge that this solution is a fair trade-off;
Business, in my experience, seldom has the technical knowledge to understand the impact of their requests, which is why we have to explain to them the pros and cons, the risks and the alternatives. It's not their job to understand the technical details and impacts, that's our job.
By the way, what your alternative solution would be ?
If there's frequent blocking, then I would investigate the cause of the blocking and fix the problem, rather than addressing the symptoms. If a session needs to be killed, I want to check what it's doing and what the consequences of rolling it back will be first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply