Bu makalede mssql server üzerinde herhangi bir session yapısının kill edilmek istendiğinde başarılı bir şekilde kill edildiği mesajını aldıktan sonra tekrardan ilgili sessionları kontrol ettikten sonra kill edilmeyen session yapısının kill edilmediğini görmekteyiz.
KILL edilmesine rağmen session gitmiyorsa genelde arka planda rollback yapıyordur ya da kill edilemeyen bir durumda takılı kalmıştır.
İlgili session kill edilir.
sp_whoIsactive’den kontrol edildiğinde KILL edilen session’ın KILL edilmediğini görmekteyiz.
Aşağıdaki komut ile bakıldığında ilgili ifadenin kendini KILLED\ROLLBACK yaptığı görülmektedir.
SELECT*FROM SYS.dm_exec_requests where session_id=2460
Aşağıdaki komut yardımı ile ilgili session yapısının ne durumda olduğunu kontrol edebiliriz.
select CMD,status,*from sys.sysprocesses where spid=2460
Aşağıdaki komut ile ilgili session yapısının tahmini ne kadar sürede bitireceğini söylemektedir. Kısacası rollback süresi tahmin edilebilir.
KILL 2460 WITH STATUSONLY
Aşağıdaki komut ile session id üzerinde herhangi bir aktif işlem olup olmadığını görebiliriz. is_user_process
SELECT open_transaction_count,is_user_process,*FROM sys.dm_exec_sessions where session_id=2460
Bu yapıya sebep olan işlem eğer session büyük bir işlem (DELETE, UPDATE, index rebuild vb.) yaptıysa, SQL Server geri alma (rollback) yapar.
Transaction çok büyük olduğu için milyonlarca satırlık işlem, büyük index rebuild işlemleri, uzun süredir açık transaction’lar session yapısının bu duruma düşmesine sebep olabilir. Özellikle BEGIN TRAN ile açılıp commit edilmeyen işler bu duruma yol açar. Sessionlar system işlemleri ise kill edilemez. System işlemleri CHECKPOINT-BACKUP-REPLICATION-AlwaysOn- LOG WRITER ve system sessionları kill edilemez.
Rollback süresi, işlemin kendisinden bile uzun sürebilir. Çünkü rollback işlemi tek bir tread üzerinden gerçekleşmektedir.
Son olarak Rollback işlemi uzun sürerse ve sql server çalışmayacak durumda ise sql server servisi restart edilmemesi gerekmektedir. Bu yapı ile sql server servisiniz açılamayabilir. Restart edip sql server servisi açılmazsa bu durumda sunucu seviyesinde restart işleminin yapılması gerekmektedir. cmd komut satırında kill etmeye çalışsanız bile sql server servisini kill edilemez. Aşağıdaki sorgudan ilgili PID değerinin bulunup kill edilmesi gerekmektedir.
sc queryex MSSQL$InstanceName
--PID değerini kill etmek için
taskkill /PID /FNeden backup rollback’te uzun sürer. SQL Server backup işlemini yarıda kesince açılmış I/O işlemlerini kapatır. Backup metadata’sını geri alır. Disk üzerindeki tutarsız parçaları temizler.
Arka planda yaşanan saatler süren gecikmenin temel nedenleri:
1. Sıralı (Sequential) Yazma vs. Rastgele (Random) Silme
Restore ederken: SQL Server .bak dosyasından veriyi sıralı bloklar halinde okur ve diske Sequential I/O (ardışık okuma/yazma) ile basar. Disklerin (NVMe/SSD dâhil) en hızlı çalıştığı mod budur. 5 dakikada onlarca hatta yüzlerce gigabayt veriyi diske bodoslama yazabilir.
Rollback yaparken (Saatler): SQL Server veritabanını iptal ettiğinizde diske yazılan veri bloğunu öylece bırakıp dosyayı silemez (çünkü arka planda dosya pointer’ları ve sistem katalogları kilitlenmiştir). Yazılan her bir sayfanın (8 KB Page), allocated edilen extents yapılarını ve sistem tablolarındaki izlerini Random I/O ile tek tek bulup geri almak (Undo) zorundadır. Rastgele okuma/yazma, sıralı yazmaya kıyasla disk I/O performansını %90-95 oranında düşürür.
2. Instant File Initialization (IFI) Tuzağı
Eğer sunucunuzda Instant File Initialization aktifse, veritabanı restore başlar başlamaz SQL Server diske sıfır (0) yazmadan saniyeler içinde diske GB’larca boş dosya alanı (.mdf) ayırır (allocate eder).
Siz 5 dakika içinde sadece %5 veri yazmış olsanız bile, SQL Server işletim sisteminden 500 GB’lık disk alanını çoktan zimmetine geçirmiştir. İptal ettiğinizde SQL Server o devasa allocation haritasını (GAM, SGAM, PFS sayfaları) işletim sistemine güvenli şekilde iade etmek için disk üzerinde milyonlarca sayfalık pointer temizliği yapar.
3. Çoklu İz (Multi-Threading) vs. Tek İz (Single-Threading)
Restore: SQL Server, yedek dosyanızı açarken MAXTRANSFERSIZE ve BUFFERCOUNT parametrelerine bağlı olarak diske yazmak için sunucudaki tüm CPU çekirdeklerini ve çoklu thread (iş parçacığı) yapısını sonuna kadar kullanır.
Rollback: Veritabanı motorunda bir transaction’ı rollback etmek Strictly Single-Threaded (Tek İş Parçacıklı) bir mimaridir. 16-32 çekirdekle 5 dakikada diske boca edilen veriyi, iptal ettiğiniz an tek bir CPU çekirdeği iğneyle kuyu kazar gibi temizlemeye çalışır. Saatler süren darboğazın en büyük sorumlusu budur.
4. Sistem Kataloğu ve Latch/Lock Kilitlemeleri
Restore esnasında sadece veri yazılmaz; master ve msdb veritabanlarında restore adımları, dosya haritaları ve LSN (Log Sequence Number) bilgileri güncellenir. İptal butonuna bastığınızda SQL Server bu dahili latch ve lock’ları çözebilmek için Transaction Log’u baştan sona geriye doğru tarar (Analysis & Undo Phase).
Veritabanını offline moduna aldıktan sonra data file’ların silinmesi saatlerce süren işlemin kısa süre içirisinde bitmesine sebep olacaktır.
Kill edilen sessionda büyük işlem iptali database recovery’yi çok daha uzun yapar. Bu da saatlerce beklememize sebep olmaktadır.
Aşağıdaki komutla block olan sorguları kill edebiliriz.
SELECT 'KILL ' + CONVERT(varchar(10), l.request_session_id)
FROM sys.databases d, sys.dm_tran_locks l
WHERE d.database_id = l.resource_database_idAşağıdaki sorgularla veritabanı bazlı ve genel olarak sorgularımızı kill işlemini gerçekleştirebiliriz.
select 'kill '+CAST(spid AS varchar(50)) from sys.sysprocesses where dbid=DB_ID('A') select 'kill '+CAST(session_id AS varchar(50)) from sys.dm_exec_connectionsHerhangi bir session’ı bloklayanları kill eden script:
Declare @x Int =2804, @y Int=0--Ilgılı kullanıcı session id değeri
While 1=1
Begin
Set @y = (select blocking_session_id from sys.dm_exec_requests Where session_id=@x and blocking_session_id<>@x)
DEclare @xx nVarchar(10)
Set @xx = 'Kill ' +Convert(Varchar(5),@y)
Exec sp_executesql @xx
Print @xx
waitfor delay '00:00:01'
EndBaşka makalede görüşmek dileğiyle..
Yoksullara ve kimsesizlere destek olmak, büyük ibadetler kadar değerlidir.(Hadis)
