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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
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 < 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 |
“Sleeping Session Kill Script – II” üzerine 2 yorum