Sleeping Session Kill Script – II

Bir önceki yazımızda az yoğun olup sorun yaratabilecek sistemlerde (örn.log shipping) sleeping sesion’ların killl edilmesine ilişkin yazıya (Sleeping Session Kill Script -I) ek olarak, bu yazımızda yoğun transaction olan yerlerde sleeping sessionların killl edilmesine ilişkin yazımızı paylaşacağız.
Yoğun transaction olan yerlerde Sleeping session’lar lock oluşturarak kaynak tüketimini artırmakta ve sistemde dar boğaza sebebiyet verebilmektedir.
Bu tür yoğun olan sistemlerde running, runnable veya suspend modda bekleyen session_id ‘leri kill etmek he uygulamada hemde veri tabanlarında büyük sorunlara sebebiyet verebilmektedir. bİZ DBA’ların en dikkat ettiği durumlardan biride bir DML işleminin rollback olması durumunda yaşanabilecek senaryolardır.
Böyle bir durumla karşlılaşmamak adına Kimberly L. Tripp, Adam Machanic’in yayınlamış olduğu sp_whoisactive çıktısının sonucunda sleeping session’ları kill etmenin iyi bir yöntem olduğunu savunmaktadır.
Aşağıda scriptlere ulaşabilirsiniz eğer sorguları bir job içerisine koyup belirli bir zaman dilimine bağlarsanız sürekli olarak sleeping session’ları kill edebilirsiniz.

Ancak çok yoğun olarak kullanılan sistemlerde herhangi bir tempdb contentaion’ı yaşadığınızda, sp_whoisactive çıktısı tempdb’den okuduğu için size cevap vermeyecektir. Buda sorun anında sleeping sessionları kill edememenize neden olacaktır.
Böyle bir durum için dmv’ler ile okunan Sleeping Session Kill Script – III adlı yazıda konuya ilişkin script ve bilgilere erişebilirsiniz.

USE [tempdb]
GO
 
/****** Object:  Table [dbo].[TempTableDBA]    Script Date: 09.12.2022 9:37:16 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[TempTableDBA](
	[dd hh:mm:ss.mss] [varchar](8000) NULL,
	[session_id] [smallint] NOT NULL,
	[sql_text] [xml] NULL,
	[sql_command] [xml] NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[wait_info] [nvarchar](4000) NULL,
	[tran_log_writes] [nvarchar](4000) NULL,
	[CPU] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[blocking_session_id] [smallint] NULL,
	[reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[query_plan] [xml] NULL,
	[used_memory] [varchar](30) NULL,
	[status] [varchar](30) NOT NULL,
	[tran_start_time] [datetime] NULL,
	[implicit_tran] [nvarchar](3) NULL,
	[open_tran_count] [varchar](30) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
 
DECLARE @KillSession VARCHAR(50)
	,@sqlKill Varchar(50)
SET @KillSession = N''
 
DECLARE @retention int = 7, @destination_table varchar(500) = 'TempTableDBA', @destination_database sysname = 'TempDB', 
      @schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit;
 
SET @destination_table = @destination_database + '.dbo.' + @destination_table;
 
--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
BEGIN;
  EXEC sp_WhoIsActive  @get_transaction_info = 1,  @get_outer_command = 1,  @get_plans = 1, @show_sleeping_spids = 2,  @return_schema = 1,  @schema = @schema OUTPUT;
  SET @schema = REPLACE(@schema, '<table_name>', @destination_table);  
  EXEC(@schema);
END
ELSE
BEGIN
	Exec('TRUNCATE TABLE ' + @destination_table)
	--Print 'silmedim'
END
 
--create index on collection_time
SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
 
IF @exists = 0
BEGIN;
  SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
  EXEC (@SQL);
END;
 
--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,  @get_outer_command = 1,  @get_plans = 1, @show_sleeping_spids = 2,  @destination_table = @destination_table;  
 
 /*
--purge older data
SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time &lt; DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());';
EXEC (@SQL);
*/
 
 
 
DECLARE Kill_Cursor CURSOR FOR
 
 
SELECT 'Kill '+ convert(varchar,[session_id] ) KillSession
  FROM [tempdb].[dbo].[TempTableDBA] with (nolock)
  WHERE status  = 'Sleeping'
  AND Login_name not IN ('YourUsers')
  AND DATEDIFF (MINUTE,start_time,collection_time) > 2
  AND open_tran_count=0     --rollback olmması icim acik transaction'da yapmiyoruz
 
OPEN Kill_Cursor
FETCH NEXT FROM Kill_Cursor INTO @KillSession
 
WHILE @@FETCH_STATUS =0
BEGIN
	set @sqlKill = @KillSession
 
	EXec(@sqlKill)
--	print @sqlKill
 
	FETCH NEXT FROM Kill_Cursor INTO @KillSession
END
 
CLOSE Kill_Cursor
 
DEALLOCATE Kill_Cursor