Requesting
I am sometimes asked to write special bits of code by people to solve a specific problem they’re having.
A recent one was for quite an unreasonable situation brought on by a shamelessly written vendor application, where:
- Implicit transactions were in use
- Bizarre locking hints abounds
- Absolutely zero attention paid to transaction handling
Which lead to scenarios where select queries would run, finish, and never close out their connection. Of course, this was bad, because loads of other queries would get blocked by these things that should have just ended their sessions and released their locks and been on their way.
And so I wrote this thing. A thing that I’d always sort of made fun of the concept of, because I’d seen so many bad implementations of it throughout the years.
Most of them would just look for lead blockers and kill them without any consideration as to how much work they’d done, which would lead to even more blocking during rollback.
This one specifically looks for things that have used zero transaction log space.
Here it is. I don’t love it, but I wanted to share it, because it might make you feel better about some code that you weren’t proud to write, either.
Thanks for reading!
/*
EXEC dbo.sleeper_killer
@debug = 'true';
SELECT
sk.*
FROM dbo.killed_sleepers AS sk;
*/
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET STATISTICS TIME, IO OFF;
GO
CREATE OR ALTER PROCEDURE
dbo.sleeper_killer
(
@debug bit = 'false'
)
AS
BEGIN
SET NOCOUNT ON;
/*Make sure the logging table exists*/
IF OBJECT_ID('dbo.killed_sleepers') IS NULL
BEGIN
CREATE TABLE
dbo.killed_sleepers
(
run_id bigint IDENTITY PRIMARY KEY,
run_date datetime NOT NULL DEFAULT SYSDATETIME(),
session_id integer NULL,
host_name sysname NULL,
login_name sysname NULL,
program_name sysname NULL,
last_request_end_time datetime NULL,
duration_seconds integer NULL,
last_executed_query nvarchar(4000) NULL,
error_number integer NULL,
severity tinyint NULL,
state tinyint NULL,
error_message nvarchar(2048),
procedure_name sysname NULL,
error_line integer NULL
);
END;
/*Check for any work to do*/
IF EXISTS
(
SELECT
1/0
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS tst
ON tst.session_id = s.session_id
JOIN sys.dm_tran_database_transactions AS tdt
ON tdt.transaction_id = tst.transaction_id
WHERE s.status = N'sleeping'
AND s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
AND tdt.database_transaction_log_bytes_used < 1
)
BEGIN
IF @debug = 'true' BEGIN RAISERROR('Declaring variables', 0, 1) WITH NOWAIT; END;
/*Declare variables for the cursor loop*/
DECLARE
@session_id integer,
@host_name sysname,
@login_name sysname,
@program_name sysname,
@last_request_end_time datetime,
@duration_seconds integer,
@last_executed_query nvarchar(4000),
@kill nvarchar(11);
IF @debug = 'true' BEGIN RAISERROR('Declaring cursor', 0, 1) WITH NOWAIT; END;
/*Declare a cursor that will work off live data*/
DECLARE
killer
CURSOR
LOCAL
SCROLL
READ_ONLY
FOR
SELECT
s.session_id,
s.host_name,
s.login_name,
s.program_name,
s.last_request_end_time,
duration_seconds =
DATEDIFF(SECOND, s.last_request_end_time, GETDATE()),
last_executed_query =
SUBSTRING(ib.event_info, 1, 4000),
kill_cmd =
N'KILL ' + RTRIM(s.session_id) + N';'
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS tst
ON tst.session_id = s.session_id
JOIN sys.dm_tran_database_transactions AS tdt
ON tdt.transaction_id = tst.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.status = N'sleeping'
AND s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
AND tdt.database_transaction_log_bytes_used < 1
ORDER BY
duration_seconds DESC;
IF @debug = 'true' BEGIN RAISERROR('Opening cursor', 0, 1) WITH NOWAIT; END;
/*Open the cursor*/
OPEN killer;
IF @debug = 'true' BEGIN RAISERROR('Fetch first from cursor', 0, 1) WITH NOWAIT; END;
/*Fetch the initial row*/
FETCH FIRST
FROM killer
INTO
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
@kill;
/*Enter the cursor loop*/
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF @debug = 'true' BEGIN RAISERROR('Insert', 0, 1) WITH NOWAIT; END;
/*Insert session details to the logging table*/
INSERT
dbo.killed_sleepers
(
session_id,
host_name,
login_name,
program_name,
last_request_end_time,
duration_seconds,
last_executed_query
)
VALUES
(
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query
);
IF @debug = 'true' BEGIN RAISERROR('Killing...', 0, 1) WITH NOWAIT; END;
IF @debug = 'true' BEGIN RAISERROR(@kill, 0, 1) WITH NOWAIT; END;
/*Kill the session*/
EXEC sys.sp_executesql
@kill;
END TRY
BEGIN CATCH
IF @debug = 'true' BEGIN RAISERROR('Catch block', 0, 1) WITH NOWAIT; END;
/*Insert this in the event of an error*/
INSERT
dbo.killed_sleepers
(
session_id,
host_name,
login_name,
program_name,
last_request_end_time,
duration_seconds,
last_executed_query,
error_number,
severity,
state,
error_message,
procedure_name,
error_line
)
SELECT
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_MESSAGE(),
ERROR_PROCEDURE(),
ERROR_LINE();
END CATCH;
IF @debug = 'true' BEGIN RAISERROR('Fetching next', 0, 1) WITH NOWAIT; END;
/*Grab the next session to kill*/
FETCH NEXT
FROM killer
INTO
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
@kill;
END;
IF @debug = 'true' BEGIN RAISERROR('Closedown time again', 0, 1) WITH NOWAIT; END;
/*Shut things down*/
CLOSE killer;
DEALLOCATE killer;
END;
END; --Final END
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
