SQL Server veri tabanı mimarisi serisinin üçüncü bölümünde de SQL Server underground dünyasını detaylandırmaya devam ediyoruz. Bu yazıda fiziksel ve mantıksal mimarinin temel bileşenleri olan aşağıdaki kavramları detaylandırıyor olacağız.

Allocation Unit (IAM)
IN_ROW_DATA Allocation Unit
LOB_DATA Allocation Unit
ROW_OVERFLOW_DATA Allocation Unit

Önceki makalelere aşağıdaki linklerden ulaşabilirsiniz;

SQL Server mimarisi – II (Data Page, Extent, GAM, SGAM, IAM)
SQL Server Mimarisi – I (Database Engine, Storage Engine, SQLOS)

SQL Server verileri fiziksel olarak her biri 8KB olan data page’lerde depolanır. Page’ler veri olarak ya doğrudan tablo datasını veya ona referans veren index datasını saklar. SQL Server üzerinde bir table veya index oluşturduğumuzda otomatik olarak bu nesnelerin altında bir partition oluşturulur. Sonraki yazılarda detaylandıracağımız Partition kavramı temel olarak yönetim kolaylığı ve performans açısından verilerin belli alan(-lar) bazında küçük kümelere bölümlendirilmesidir. Bir tablo bir veyda fazla partition’a içerir. Partition lar da Heap veya Clustered Index yapısında Data Row’ları içerir. Heap veya Clustered Index’a ait Page’ler kolonların verip tipine göre bir veya daha fazla Allocation Unit içerisinde yönetilir. Partition’lar kendisine ait page’leri kolonların tipine göre gruplandırır ve bunların her birine Allocation Unit denilir. “Allocation Unit” kavramını disk mimarisinden hatırlayabiliriz. Allocation Unit, disk üzerindeki sektör kümesini temsil eder yani yönetilebilir en küçük disk alanıdır. SQL Server’de de aynı amaç için kullanılmaktadır.

3 türde (storage types) Allocation Unit bulunmaktadır.

• IN_ROW_DATA
• ROW_OVERFLOW_DATA
• LOB_DATA

Tablo veya index sayfaları, bir veya daha fazla partition içerir. Partition’ları kısaca kullanıcı tarafından veri organizasyonu amaçlı oluşturulmuş birim olarak tanımlayabiliriz. Aşağıdaki örnekte IAMOrnek isimli bir tablo oluşturulmuştur. Tablonun partition bilgisini sorguladığımızda default bir partition oluşturulduğunu görmüş olur.

CREATE TABLE IAMOrnek(
   Id INT
)

Bu basit tablo oluşumundan sonra sys.tables, sys.partitions ve sys.allocation_units DMV’leri sorgulayarak oluşan birimleri görebiliriz.

--Tablo bilgisi
SELECT * FROM sys.tables WHERE name='IAMOrnek'
--Partition bilgisi
SELECT * FROM sys.partitions WHERE object_id=OBJECT_ID('IAMOrnek')
--Oluşturulan Allocation Unit bilgisi
SELECT * FROM sys.allocation_units WHERE container_id=72057594041860096 -- sys.partitions'daki partition_id

SQL Server, bir partition’daki data page’leri yönetmek için Clustered Index’li olup olmamak üzere iki yöntem kullanır; Clustered Index’i tablolara Clustered Table, Clustered Index’i olmayan tablolara Heap denilir. Ekrandaki index_id=0 olması bu tablo üzerinde clustered index olmadığı yani işlenmemiş anlamında Heap olduğunu belirtir. index_id=1 olması tablodaki Clustered Index’i temsil eder. index_id>1 olması ise tablo üzerindeki Non-Clustered Index’leri belirtir.

Tablo üzerinde her partition için bir partition_id ve buna bağlı olarak bir HOBT oluşur. HOBT kelimesi Heap Or B-Tree (Balanced Tree) ifadesinin kısaltması olarak kullanılır. Yukarıdaki sonuç görüntüsünde hobt_id alanı bunu temsil etmektedir. Şu an için hobt_id’nin özel bir anlamı olup partition_id ile aynı değere sahiptir. Belki ileriki sürümlerde farklı bir amaç için kullanılabilir. Konumuza geri dönüp Allocation Unit’leri tanımlayalım.

IN_ROW_DATA: Büyük veriler (Large object -LOB) hariç tüm table ve index datasını yönetmek için kullanılır. Page’lerin türü Data veya Index olabilir. Bir satırın büyüklüğü 8060 byte’ı aşmadığı sürece SQL Server tüm datayı IN_ROW_DATA Allocation Unit içine kaydeder.
ROW_OVERFLOW_DATA: Değişken uzunluklu (variable length data) olan varchar, nvarchar, varbinary, sql_variant türlerin 8060 byte’ı aşması durumunda SQL Server bu datayı ROW_OVERFLOW_DATA birimine alır. Page’lerin türü Text/Image dir.
LOB_DATA: Büyük nesneleri saklamak için kullanılan text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), CLR user-defined types (CLR UDT) türler için tercih edilir. Page’lerin türü Text/Image dir.

Görüldüğü gibi tabloda sadece 4 byte’lık uzunluğu sabit olan INT türü kullanıldığı için IN_ROW_DATA türünde Allocation Unit oluşturuldu. Tabloya VARCHAR(4000) ve VARCHAR(5000) tipinde iki kolon ekleyelim.

ALTER TABLE IAMOrnek ADD C1 VARCHAR(4000)
ALTER TABLE IAMOrnek ADD C2 VARCHAR(5000)

Böylece satırın uzunluğu maximum 4+4000+5000=9004 olacaktır. 9004, 8060 sınırından büyük olduğu için ROW_OVERFLOW_DATA türünde Allocation Unit oluşacaktır.

SELECT AU.* FROM sys.partitions P
	INNER JOIN sys.allocation_units AU ON AU.container_id = P.partition_id
WHERE P.object_id = OBJECT_ID('IAMOrnek');

Şu ana kadar 2 tane Allocation Unit oluşturuldu. Biz henüz kayıt INSERT etmediğimiz için page sayısı=0 görünmektedir. Son olarak TEXT türünde bir CLOB kolon ekleyelim. Veya yeni sürümler için VARCHAR(MAX) da olabilir.

ALTER TABLE IAMOrnek ADD LobData TEXT

Şimdi tabloya bir kayıt ekleyelim ki Page oluşumunu görmüş olalım.

INSERT IAMOrnek VALUES(1,'A','B','Uzun Metin')

Tabloda tek kayıt bulunmaktadır. DBCC IND(‘BLOG’,’IAMOrnek’,1) komutunu veya sys.dm_db_database_page_allocations DMV’yi kullanarak bu tablonun Page’lerini listeleyelim.

SELECT
allocated_page_file_id AS PageFID
,allocated_page_page_id AS PagePID
,allocated_page_iam_file_id AS IAMFID
,allocated_page_iam_page_id AS IAMPID
,object_id AS ObjectID
,index_id AS IndexID
,partition_id AS PartitionNumber
,rowset_id AS PartitionID
,allocation_unit_type_desc AS iam_chain_type
,page_type AS PageType
,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('IAMOrnek'), NULL, NULL, 'DETAILED')

Görüldüğü gibi SQL Server 2 tane IAM Page (121, 166) allocate etmiş. IAM Page’leri (Index Allocation Map) tablodaki diğer tabloları takip etmek onları izini kaybetmemek için kullanılır. Her Allocation Unit için bir IAM Page oluşturulur. Aynı tablo altındaki IAM Page’ler birbirleriyle linklenmiş durumdadır. Bu link listesine IAM Chain denilir.

Şu anda bu tablo için sadece IN_ROW_DATA ve LOB_DATA allocation’ları oluşturuldu. 8060 değerini aşan bir satır olmadığı için ROW_OVERFLOW_DATA tahsis edilmedi. Şimdi 8060 byte sınırını aşacak bir kayıt insert edelim.

INSERT IAMOrnek VALUES(2,REPLICATE('A',4000),REPLICATE('B',5000),'Uzun Metin')

Aynı şekilde tablonun page’lerini listelediğimizde 121 ve 166 nolu sayfalara ek olarak 127 nolu yeni bir IAM Page’in oluşturulduğunu ve ROW_OVERFLOW_DATA türünde Page’lerin geldiğiniz görürüz.

PageType alanındaki numaların açıklamalarını sys.dm_db_database_page_allocations’daki page_type_desc alanından öğrenebiliriz.

Eğer bu tabloda daha fazla partition olsaydı her partition IAM Page’ler olacaktı. Son durumda sys.allocation_units DMV’den Allocation Unit Page sayısını görelim.

SELECT AU.* FROM sys.partitions P
	INNER JOIN sys.allocation_units AU ON AU.container_id = P.partition_id
WHERE P.object_id = OBJECT_ID('IAMOrnek')

Şimdi bir tane IAM Page’in içeriğini inceleyelim. Bu makale serisinin önceki yazısında gördüğümüz gibi DBCC PAGE komutu kullanılabilir.

DBCC TRACEON (3604);
DBCC PAGE ('BLOG', 1, 121, 3);

Görüntüdeki SequenceNumber alanı bu IAM Page’in IAM Chain’deki sırasını, konumunu belirtir. Single Page Allocations bölümünde mixed extent’ten allocate edilmiş ilk 8 sayfa numaraları görülmektedir. Hatırlacağı üzere bu 8 sayfa dolarsa SQL Server uniform extent’i allocate ediyor olacak.

120 nolu page’in içeriğine bakalım.

İlk kaydımız 120 nolu page içerisinde duruyor. Ve uzunluk itibariyle tek page’e sığmış durumda. Fakat üçüncü kolon LOB türünde olduğu için farklı bir Page’e (RowId = (1:162:0)) referans verilmiştir. 162 nolu Page’e baktığımızda “Uzun Metin” ifadesini görmüş oluruz. Aynı şekilde 142. sayfaya bakalım.

Burada da görüldüğü gibi AAAA… değeri bu sayfaya sığmış ancak ikinci kolon doğrudan 126 sayfaya kayıt edilmiş. Eğer BBBBBB…. verisini daha kısa bir ifadeyle güncellersek 126 nolu sayfa atıl duruma düşmüş olacak.

UPDATE IAMOrnek SET C2='B1' WHERE Id=2

Konunun başında kullandığımız TEXT veri tipinin neden artık tercih edilmemesi gerektiğini burada net görebiliriz. Eğer tabloyu oluştururken TEXT veri tipini kullanırsak aşağıdaki gibi 4 sayfa oluşacak. Ve 147 sayfasından “AAAA” ifadesinin yazılı olduğu 144 sayfasına referans verilecekti.
150 – IAM_PAGE
147 – DATA_PAGE
146 – IAM_PAGE
144 – TEXT_MIX_PAGE

Oysa VARCHAR(MAX) kullanılması durumunda
146 – IAM_PAGE
144 – DATA_PAGE
gibi iki sayfa oluşturularak “AAAA” ifadesi 144 nolu sayfada olmuş olacaktı. Eğer ileride bu ifade >8060 olarak güncellenirse o zaman LOB_DATA Allocation Unit ve TEXT_MIX_PAGE türünde sayfa oluşturulur.

Eğer 2’den daha fazla page sığacak kadar uzun bir data girecek olursak ne olur. Örneğin VARCHAR(MAX) alanına 60000 byte bir text girdiğimizi düşünelim. Bu durumda bu veri 8000 byte parçalara bölünerek farklı page’lerde depolanır. Ardından bu sayfaları linklendirmek için TEXT_TREE_PAGE türünde bir page oluşturulur. Bu sayfaya ilk oluşturulan DATA_PAGE’dan pointer verilir. Aşağıdaki sonuçta 64000 byte’lık bir veri 8 page’da saklanmıştır (8*8040+7720).

SQL Server mimarisi konulu bu seride aşağıdaki konuları detaylandırmaya çalışıyorum.

SQL Server’in mimarisi nasıldır?
SQL Server veri tabanı mimarisi, disk yönetim mimarisi?
SQL Server, verileri nasıl saklar, kayıt eder?
8060 byte’dan büyük verileri nasıl kayıt edilir?
BLOC, CLOB verileri nasıl sayfalarda depolanır?
Sql Server storage architecture, storage engine, storage internals
Page, Extent, Allocation Unit, IAM Page, PFS, GAM, SGAM Pages, Row Offset Nedir?
SQL Server bellek yönetimi, memory management
SQL Server işlemci yönetimi, CPU management
SQL Server disk space, disk usage
Sql Server IO performance