SQL Server üzerinde row, page veya extend bazında DELETE işlemi yapıldığı zaman, SQL Server silinmek istenen nesneleri o anda hemen silmez onları Ghost Object(Hayalet Nesne) olarak işaretleyip askıya alır. Yani silme işlemi fiziksel değil mantıksal olarak gerçekleşir. Sistemin durumuna göre bir süre sonra arka tarafta çalışan bir processle fiziksel silme işlemini gerçekleştirir. Ghost Record Cleanup olan isimlendirilen bu process, DELETE işlemi esnasında SQL Server yapacağı fiziksel silmeden yaşayacağı performans sorununu aşmak için oluşturulmuş bir yöntemdir. Ayrıca silinmiş nesneleri daha hızlı bir şekilde rollback etmek amacıyla da bu sistem kullanılır.
SQL Server üzerindeki bir satırı silmek istediğimizde o satır o anda hemen fiziksel olarak silinmek yerine ghost record olarak tanımlanır. Yani aslında silme işleminden sonra sözkonusu satır hala data page veyaindex page‘in içerisinde bulunuyor fakat diğer satırlardan farklı olarak bu satırların header‘inde bu satırın hayalet bir satır olduğu bilgisi konulmuştur. Ayrıca bir page‘de ne kadar hayalet satırın olduğu bilgisi de page’in header’ine yazılır. Bazı kaynaklarda Ghost Record Cleanup servisinin 15 sn’de bir devreye girdiği yazılmaktadır.
SQL Server üzerinde bir kaydın silinmesi süreci sonra yapısını görmek için küçük bir örnek yapalım. Deneme isimli veri tabanı üzerinde Musteri isimli bir tablo oluşturalım.
CREATE TABLE [dbo].[Musteri]( [MusteriId] [int] IDENTITY(1,1) NOT NULL, [AdSoyad] [nchar](10) NULL ) ON [PRIMARY]
Ardından tablo üzerinde index page oluşması için bir clustered index oluşturalım.
CREATE CLUSTERED INDEX IX_Musteri1 on Musteri (AdSoyad)
Şimdi bu tablonun sahip olduğu page’leri listeyelim. Bunun için SQL Server’in undocumented DBCC komutlarını kullanacağız. Deneme veri tabanı içindeki Musteri tablosunun page’lerini listelemek için DBCC IND komutu kullanılabilir.
dbcc ind( dbid|dbname, objid|objname, printopt = {-2|-1|0|1|2|3} )
DBCC IND ('Deneme', 'Musteri', 1);
Benim makinemdeki yapıya göre aşağıdaki page’ler geldi.
Bu tabloda sözkonusu tablonun oluştuğu tüm page’ler hakkında geniş bilgiyi bulabiliriz. Tablodaki PageType kolonun sayfanın türünü belirtir.
1 – data page
2 – index page
3 veya 4 – text pages
8 – GAM page
9 – SGAM page
10 – IAM page
11 – PFS page
Bizim tabloda kayıtların bulunduğu page numarası 1736 olarak geçiyor. Bu page’in içeriğini görmek için DBCC PAGE komutu kullanılır.
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
Deneme veri tabanının 173. sayfasını incelemek için aşağıdaki satır yazılı.
DBCC TRACEON (3604);
GO
DBCC PAGE ('Deneme', 1, 173, 3);
Şimdi bu verileri kullanarak bir DELETE işlemi yapalım. DELETE işlemini bir transaction içerisinde gerçekleştirelim ki transaction esnasında arkada yapılan işlemleri görelim.
BEGIN TRAN "SilmeDeneme"
INSERT INTO Musteri VALUES ('Ahmet')
DELETE FROM Musteri WHERE AdSoyad='Ahmet';
GO
DBCC IND ('Deneme', 'Musteri', 1);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE ('Deneme', 1, 173, 3);
GO
COMMIT TRAN "SilmeDeneme"
Bu komuttan sonra sayfanın içindeki kayıtlar gösterilir.
Görüldüğü gibi “Ahmet” kaydı “GHOST_DATA_RECORD” olarak işaretlenmiştir. Bu işlemi COMMIT ettikten sonra kayıt fiziksel olarak silinmiş olur.
Log dosyası tarafında nelerin olup bittiğini anlamak için log dosyasını incelememiz lazım. SQL Server’da bir kolon için log bilgisini almak amacıyla ::fn_dblog() sistem fonksiyonu kullanılabilir. Diğer yöntem de DBCC LOG komutunu kullanmaktır. ::fn_dblog() fonksiyonu daha esnek yapıya sahip olup iki parametre alır:
START_LSN : Starting Log Sequence Number
END_LSN: Ending Log Sequence Number
SELECT * FROM ::fn_dblog(
LSN(Log Sequence Number) kavramı günlük sıra numarası olarak tanımlanabilir. Transaction log, her transaction’ı tekil bir LSN ile numaralandırır. fn_dblog() metoduna başlangıç ve bitiş LSN değerlerini verip log dosyasında belli aralıktaki transaction satırlarını okunabilir. Buradaki START_LSN ve END_LSN parametreleri, HEX:HEX:HEX formatındadır yani 3 tane HEX formatında değer oluşmuş bir değerdir. fn_dblog metodu, birçok kolon içermektedir.
Burada ilgineceğimiz kolonlar şunlardır:
Operation : Yapılan işlemi belirtir. < ı>LOP_BEGIN_XACT seçeneği, transaction’ın başlatıldığını, < ı>LOP_COMMIT_XACT, transaction’ın bittiğini, < ı>LOP_MODIFY_COLUMNS, update işleminin çağrıldığını, < ı>LOP_INSERT_ROWS, insert işleminin, < ı>LOP_DELETE_ROWS, delete işleminin çağrıldığını bildirir.
Alloc Unit Name : O transaction’da kullanılan nesnenin adını belirtir.
Öncelikle örneğimiz için oluşan Transaction ID’ye erişmemiz lazım. Bunun için transaction adı olan “SilmeDeneme” ifadesinden sorgulama yapacağız. Aynı transaction içerisinde insert ve delete işlemini yapalım aynı zamanda bu işleminde log tarafında yansımasını da fn_dblog() fonksiyonu aracılığıyla listeleyelim.
BEGIN TRAN "SilmeDeneme"
INSERT INTO Musteri VALUES ('Ahmet')
DELETE FROM Musteri WHERE AdSoyad='Ahmet';
GO
SELECT * FROM fn_dblog (null, null)
WHERE [Transaction ID] = (
SELECT MAX([Transaction ID]) FROM fn_dblog(null,null)
WHERE [Transaction Name]='SilmeDeneme'
)
GO
COMMIT TRAN "SilmeDeneme"
Buradan şunu anlıyoruz, bir INSERT ve bir DELETE işlemi gerçekleşmiş ve o silinen kayıt ghost record olarak işaretlenmiştir.
Ekranda da gösterildiği gibi transaction bitmeden hemen önce oluşan LSN’nin numarası “00000025:00000098:0004¨ dır. Eğer bu LSN’den sonraki LSN’leri o anda sorgularsak COMMIT işlemi ve sonrasında yine LOG tarafında nelerin yapıldığını öğrenmiş oluruz.
SELECT * FROM fn_dblog (null, null) WHERE [Current LSN] > '00000025:00000098:0004'
Burada da COMMIT işleminin yapıldığını ve yapılan işlemlerin PFS Page’e(Page Free Space) yansıtıldığını görmekteyiz. PFS içerisinde kaydın tipi değiştirilmiş Bu işlemler, silme işlemini yaptığımız transaction içerisinde gerçekleşmez o transaction’dan bağımsız bir transaction’da çalışır. (SQL Server dosya sisteminde kullanılan PFS, GAM, SGAM, IAM sayfa türlerinin açıklamaları MSDN’de bulunmaktadır)
Bu transaction içerisinde kullanıcı işlemlerin hepsini aynı anda görmek için COMMIT işleminden sonra kullandığımız Transaction ID’sini girebiliriz. Sonuç ekranlarından da görüleceği gibi [Transaction ID] değerimiz “0000:00000416¨ görünüyor.
SELECT * FROM fn_dblog (null, null) WHERE [Transaction ID] = '0000:00000416'
Veya DBCC LOG(‘Deneme’,-1) ifadesi kullanılabilir. Bu durumda Deneme veritabanındaki tüm log satırlarını görebiliriz.
Fonksiyonun döndürdüğü sonuçlarda [Page ID] kolonuna baktığımızda “0001:000000ad” ifadesini görürüz. fn_dblog() fonksiyonunun gösterdiği ID’ler, Hexadecimal(Onaltılı) formatındadır. Ve değerlerin arasında “:” bulunur. Örneğin buradaki Page Id bilgisinin ikinci bölümünü (000000ad) Calculator programını açıp Hex modundayken yazalım ve ardından Dec’i seçip değeri Decimal’a(Ondalık) çevirelim. Bu durumda Page Id değerimiz olan 173’ü gösterecektir.
Bu işlemlerden sonra sayfanın durumuna yeniden bakacak olursak m_ghostRecCnt değerinin sıfırlandığını görürüz.
DBCC TRACEON (3604);
GO
DBCC PAGE ('Deneme', 1, 173, 3);
Bu işlem sonucu kayıt silinmiş olur ancak ona ait içerik tam olarak yok olmamış olur. Nitekim DBCC PAGE için 2 parametresine girersek kayıtları daha net görmüş oluruz.
DBCC TRACEON (3604);
GO
DBCC PAGE ('Deneme', 1, 173, 2);
Fakat bu durum bizi tedirgin etmemeli. Boş olanlar doldukça bu kayıtların üzerine yazılır.