Üzerinde index bulunan bir tabloya kayıt eklendiğinde Page ve Row konumları nasıl etkilenir? Page Split ne zaman oluşur?

Birçok yerde indexlenmiş tabloya yeni kayıt eklendiğinde veya mevcut kayıt silindiğinde kayıtların adreslerinin fiziksel olarak değişip değişmediği sorusu gelmektedir. Bunu basit bir örnekle açıklayalım. UYE isminde bir tablo oluşturalım. ID alanı üzerinde Clustered Index oluşturalım. İlk kayıt olarak ID=4 olarak “D” ifadesini ekleyelim.

CREATE TABLE UYE(UyeId INT PRIMARY KEY, AdSoyad VARCHAR(2000))

INSERT UYE VALUES(6,REPLICATE('G',1000))

DBCC IND(‘BLOG’, ‘UYE’, -1) komutuyle sayfalarını listelediğimizde 144 nolu bir data page’in oluştuğunu görüyoruz. Bu sayfanın içeriğine DBCC PAGE(BLOG,1,144,1) komutuyla bakalım. Eklenen satırın konumuna bakmak için Page içerisinde “OFFSET TABLE:” bölümüne baktığımızda bu kaydın adresinin 96 olarak görüyoruz.

Şimdi bizim kafamızdaki sıralamaya göre ID=6’ten önce olan ID=4 kaydını INSERT edip 144 nolu sayfayı yine dump edelim.

INSERT UYE VALUES(4,REPLICATE('D',2000))

Görüldüğü gibi ilk kayıt olan “G” kaydının fiziksel adresi değişmedi. G=96 D=2111 adreslerinde görünmektedir. ID=2 olarak “B” değerini INSERT edelim.

INSERT UYE VALUES(2,REPLICATE('B',2000))

Offset tablosunda görüleceği gibi G=96 D=2111 ve yeni eklediğimiz B=4126 adreslerinde durmaktadır. ID=5 AdSoyad=E satırını ekleyelim.

INSERT UYE VALUES(5,REPLICATE('E',2000))

Offset dizisi aşağıdaki gibi olacaktır.

Bu arada sp_spaceused UYE komutuyla tablonun büyüklüğüne baktığımızda büyüklüğün 8KB olduğunu görüyoruz. Yani bir kayıt daha eklersek yeni bir Page oluşturulacak. Tablo üzerinde Clustered Index olduğu için B-Tree ağac yapısında göre balancing yapması gerekiyor. Şu ana kadar 1 page olduğu için herhangi bir ağaç oluşturmasına gerek yoktu. Şimdi Root Page de oluşmuş olacak. Çünkü mevcut düğümdeki (Page) anahtar sayısı maksimum seviyeye ulaştı.

INSERT UYE VALUES(5,REPLICATE('H',2000))

Şu ana kadar eklenen kayıtlar ID=2,4,5,6,7 değerleridir. B-Tree için ortadaki değer olan 5 alınarak (ortanca eleman (median)) ağaç oluşturulur. Yani sayfa kırılımı “5” değerine göre yapılır.

SELECT allocated_page_page_id AS PagePID
,allocated_page_iam_page_id AS IAMPID
,object_id AS ObjectID
,index_id AS IndexID
,page_type AS PageType
,page_type_desc AS PageTypeDesc
,page_level AS IndexLevel
,next_page_file_id AS NextPageFID
,next_page_page_id AS NextPagePID
,previous_page_file_id AS PrevPageFID
,previous_page_page_id AS PrevPagePID
FROM sys.dm_db_database_page_allocations(DB_ID('BLOG'), OBJECT_ID('UYE'), NULL, NULL, 'DETAILED')

145: IAM Page, 146: INDEX ROOT Page (Index Level=1), 144 ve 147: Data Page (Leaf Level) oluştu. Bu sayfaları aşağıdaki gibi temsil edebiliriz.

146 nolu sayfanın içeriğine bakalım.

Değerlerin kırılma noktası UyeId(key)=5 olarak görülmektedir. Yani <5 kayıtları 144 nolu sayfada >=5 olanlar da 147 nolu sayfada duruyor.

Tablodaki kayıtları Page bilgileriyle birlikte listeleyelim.

SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM UYE

2 ve 4 kayıtları 144 nolu sayfaya, 5, 6 ve 7 kayıtları da 147 nolu sayfaya taşındı. Bu işlem sonucunda 5 ve 6 kayıtları başka sayfaya alınmış oldu. Bu işlemi Page Split denilir. Page Split ile ilgili detayı “SQL Server Page Split Nedir ?” makalemde bulabilirsiniz.

Sırasıyla 1,3,9,10,8 kayıtlarını da ekleyelim. 1,3<5 olduğu için 144 nolu sayfaya, 9>5 olduğu için 147 nolu sayfaya yerleşir. 144 nolu sayfada:1,2,3,4 kayıtları 147 nolu sayfada:5,6,7,9 kayıtları bulunuyor yani 2 sayfa da dolmuş oldu. Dolayısıyla 10 kaydı INSERT edileceği zaman yeni bir Page oluşturulacak fakat taşınma işlemi olmadığı için Page Split işlemi olmamış olur. Son olarak “8” kaydını ekleyelim. Bu durumda 8 kaydını “5-6-7-9” bulunduğu 147 nolu sayfaya yazmaya çalışacak. Fakat sayfa dolu oluğu için yani B-Tree’da node dolu olduğu için “5-6-7-8-9” listesinden ortanca değer olan “7” değeri bir üste (Root Page’e) çıkar yani yeni kırılım oluşturulup sağındaki ve solundaki sayfalar yeniden düzenlenir sonuçta 8-9 değerleri başka sayfaya taşınmış olur. Daha önce 5 ve 10 kırılma noktası iken şimdi 7 de gelmiş oldu. Son durum aşağıdaki gibi olacaktır.

Bu esnada Performance Monitor aracında Page Splits/sec sayacını aktifleştirirsek aşağıdaki gibi o anda durumu izleme şansı edebiliriz. “Page Splits/sec” sayacı “SQLServer:Access Methods” objesinin altında bulunmaktadır.

Son olarak Index’e ait ROOT PAGE’deki UNIQUIFIER(key) alanında bahsedelim. Bu alan Clustered Index’te mükerrer kayıt olması durumunda satırları tekilleştirmek için kullanılan 4 byte’lık bir değerdir. UYE isimli tablonun üzerindeki Primary Key alanını DROP edip Clustered Index oluşturalım.

CREATE TABLE UYE(UyeId INT, AdSoyad VARCHAR(2000))
GO
CREATE CLUSTERED INDEX IX_UyeId ON UYE(UyeId)
GO
INSERT UYE VALUES(1,REPLICATE('A',2000))
GO 10
INSERT UYE VALUES(2,REPLICATE('B',2000))
GO 10