SQL Server veri tabanı mimarisi serisinin dördüncü bölümünde Table ve Index nesnelerinin nasıl oluşturulduğu, MDF içerisinde nasıl konumlandırıldığını detaylandıracağız. Bu amaçla temel olarak aşağıdaki kavramları incelemiş olacağız.

SQL Server Table, Heap, B-Tree (Balanced Search Tree), Index
Clustered Index,
NonClustered Index,
Columnstore Index

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

SQL Server Mimarisi – III (Allocation Unit, IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA, IAM Page)
SQL Server mimarisi – II (Data Page, Extent, GAM, SGAM, IAM)
SQL Server Mimarisi – I (Database Engine, Storage Engine, SQLOS)

SQL Server’da table ve index yapıları Data Page / Extent konusunda detaylandırdığım Page birimi üzerine kurulmuştur. Database Engine, Page’leri yönetmek, şekillendirmek için iki yöntem kullanılır;

Clustered Table: üzerinde clustered index tanımlanmış olan tablo yapısıdır. Bu yapıda tablodaki veriler clustered index key olarak tanımlanmış alan üzerinden sıralı olarak saklanır. Index sayfaları verilere hızlı bulma ve erişme için B-tree yapısında düzenlenmiştir. Üzerinde clustered index bulunan indexed view’ler de (materialized views) tıpkı clustered table yapısındadır. Bu tasarımın nasıl olduğunu detaylandıracağız.
Heap: Üzerinde clustered index olmayan tablo yapısıdır. Tablodaki satırlar belirli bir sıralamaya göre kaydedilmemiştir. Aynı şekilde data page’ler arasında da belirli bir sıralama bulunmaz. Bu şekildeki bir tablo üzerinde nonclustered index oluşturulabilir.

Bir tabloda üzerinde 1 adet clustered index oluşturulabilirken ancak Nonclustered index’ten yüzlerce oluşturulabilir. Non-Clustered Index, clustered index gibi B-tree index yapısına sahiptir. En büyük farkı clustered index, tablodaki satırları belirlenmiş alan üzerinden fiziksek olarak sıralar, Non-Clustered index ise data row’ların sırasını dokunmaz yani tablo üzerinde herhangi bir sıralama uygulamaz.

Önceki makalede tablo yapısının genel olarak aşağıdaki şekilde kayıt edildiğini yazmıştık.

Her tablo bir veya daha fazla Partition içerir. Her partition da heap veya clustered index yapısında Data Page içerir. Page’ler, kolonların veri tipine bağlı olarak bir veya daha fazla allocation unit içerisinde yönetilir. Her tablo veya index için default bir partition oluşturulur. sys.partitions DMV’sinden görebileceğimiz listede index_id=0 olması o yapının Heap olduğunu, index_id=1 olması o partition’nın Clustered Index tarafından kullanıldığını belirtir. Heap veya Clustered Index için birden fazla partition oluşturulabilir.

SQL Server, Heap üzerinde hareket etmek için IAM Page’lerini kullanır. sys.system_internals_allocation_units view içerisindeki first_iam_page bir heap’a ait belirli bir partition’ının ilk IAM Page’e işaret eder. IAM’in işaret ettiği asıl datanın bulunduğu Data Page’ler ve içerisindeki satırlar sıralı olmayıp aralarında bir link bulunmaz yani Page içerisinde m_prevPage ve m_nextPage alanları boş görülür. Database Engine, bu tablodan sorgulama yapacağı zaman IAM Page’lerini kullanarak tablo datasının bulunduğu Page’leri tutan extentleri bulur. Aşağıdaki şekil bu kullanımı göstermektedir.

Bir tablo oluşturduğumuzda öncelikle default olarak bir partition ve bir index (gerçek anlamda değil) kaydı oluşturulur. Aşağıdaki gibi 3 kolonlu bir tablo oluşturalım.

CREATE TABLE MUSTERI (MusteriId INT, AdSoyad CHAR(2000), Sehir CHAR(2000))

Bu tablo için oluşturulan partition, index ve allocaiton unit bilgilerini okuyalım.

--Partition
SELECT * FROM sys.partitions WHERE object_id=OBJECT_ID('MUSTERI')
--Index
SELECT * FROM sys.sysindexes WHERE id=OBJECT_ID('MUSTERI')
--Allocation Unit
SELECT * FROM sys.system_internals_allocation_units SAU
	JOIN sys.partitions PT on SAU.container_id = PT.partition_id and PT.object_id = OBJECT_ID('MUSTERI')

Şu an tabloda herhangi bir kayıt olmadığı için herhangi bir page allocate edilmedi.

SELECT O.name, O.object_id, PT.index_id, IAU.total_pages, IAU.used_pages, IAU.data_pages, first_iam_page, first_page, root_page
FROM sys.objects O
    JOIN sys.partitions PT on O.object_id = PT.object_id
    JOIN sys.allocation_units AU on AU.container_id = PT.hobt_id
    JOIN sys.system_internals_allocation_units IAU on IAU.container_id = AU.container_id
WHERE O.object_id = object_id('MUSTERI')

index_id=0 olması bu tablonun Heap yapısında olduğunu gösterir. root_page alanı sadece index yapılarında kullanılır. Heap için uygulanmaz. Şimdi tabloya bir kayıt INSERT edip son sorguyu tekrar çalıştıralım. Bu sorguda sayfa numaraları hexadecimal olarak sunulduğu için decimal formata da çevirelim.

INSERT MUSTERI VALUES(1,'Ad_1','Sehir_1')
GO
SELECT O.name, O.object_id, PT.index_id, IAU.total_pages, IAU.used_pages, IAU.data_pages, 
sys.fn_PhysLocFormatter(IAU.first_iam_page)first_iam_page, 
sys.fn_PhysLocFormatter(IAU.first_page)first_page, 
sys.fn_PhysLocFormatter(IAU.root_page)root_page
FROM sys.objects O
    JOIN sys.partitions PT on O.object_id = PT.object_id
    JOIN sys.allocation_units AU on AU.container_id = PT.hobt_id
    JOIN sys.system_internals_allocation_units IAU on IAU.container_id = AU.container_id
WHERE O.object_id = object_id('MUSTERI')

Kayıt sonrası 1 adet IAM Page, 1 adet Data Page olmak üzere 2 sayfanın oluştuğunu görülmektedir. IAM Page No=145 ve İlk Data Page=144 oluşturulmuş.

Önceki makaleden öğrendiğimiz komutlarla tablonun sayfalarını listeleyelim. Bunun için sys.dm_db_database_page_allocations kullanılabileceği gibi basitçe DBCC IND komutu da kullanılabilir.

DBCC TRACEON(3604)
DBCC IND('BLOG', 'MUSTERI', -1)

Eğer bu tabloya sorgu gönderecek olursak öncelikle Storage Engine, IAM Page(PageId:145) üzerinden extent’leri tespit eder ve o extent içerisinden ilgili Page’leri tarar.

Bir kaydın fiziksel konumunu öğrenmek yani tam olarak hangi page ve slot içerisinde olduğunu öğrenmek için %%physloc%% komutu kullanılır. Bu komutun detayını SQL Server’da Satırın Fiziksel Konumu (%%physloc%%) makalemde bulabilirsiniz. Tabloya 2 kayıt daha ekleyip kayıtların Page bilgisini de listeleyelim.

INSERT MUSTERI VALUES(2,'Ad_2','Sehir_2')
INSERT MUSTERI VALUES(3,'Ad_3','Sehir_3')
GO
SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) FROM MUSTERI

Heap modundaki bir tablodan sorgulama yaptığımız zaman o tabloya ait tüm Data Page’ler okunur. Bu da performans sorununa neden olur. MUSTERI tablomuzda “WHERE MusteriId=1” filtresi verdiğimizde 144 nolu Page’de duran bu kayıt için sadece 144 nolu Data Page’in okunacağını bekleriz oysa 144, 145 (IAM) ve 146 sayfaları okunmuş olur.

SQL Server’daki verilere hızlı erişim için Clustered veya Non Clustered Index olmak üzere iki index yöntemi kullanılır. Index dataları B-Tree algoritmasına göre düzenlenir. Bu düzeni anlamak için B-Tree’dan bahsetmek faydalı olacaktır.

• Başındaki “B”nin tam olarak nerden geldiği bilinmese de “Balanced Tree” olarak hafızalarda kalan B-tree algoritmasının temel amacı verileri ağaç yapısında tutup arama süresini kısaltmaktır.
• B-Tree’daki her Page, Index Node (Düğüm) olarak tanımlanır.
• En üstteki node, Root Node (Kök) olarak tanımlanır.
• Ağacın en altındaki node’lar Leaf Node (Yaprak) olarak tanımlanır. Bütün yapraklar aynı sevidedir.
• Root Node ile Leaf Node’lar arasındaki düğümler Intermediate Level olarak tanımlanır. Tablodaki kayıt sayısına göre 1’den fazla Intermediate Level olabilir. Yani B-Tree’da 1 adet Root, 1 adet Leaf olabilir ama 1’den fazla Intermediate olabilir.
• Root Level ve Intermediate Level için Non Leaf Level kavramı da kullanılır.
• Her index satırı Key Value ve Pointer (Gösterici) olmak üzere iki değer taşır. Pointer, ya intermediate level page’a veya leaf level’daki satıra işaret eder.
• B-tree’da her düğümdeki veriler sıralı olduğu için bir düğümde arama yapılırken tüm değerlerle tek tek karşılaştırma yapılır.(Doğrusal veya Sırasal Arama – Linear Search)
• Index her level’daki Page’lar birleriyle linkli olarak tutulur.
• Veriler ya son düğüm olarak yapraklarda tutulur veya yapraktaki pointer’in gösterdiği alanda (RAM, Disk üzerindeki) tutulur.

Aşağıdaki şekilde basit bir B-tree yapısı gösterilmiştir.

Bu ağaçta arama işlemi kökten yani Root Node’dan başlanır. Aranılan veri bu düğümde yoksa veri bu düğümdeki veriyle karşılaştırılır ona göre düğümün sağına soluna veya ortasına yönlendirilir. Örneğin aşağıdaki örnekte 19 sayısını aradığımızda ilk önce Root Node ile karşılaştırma yapılır. Aranılan anahtar (19), kök anahtardan (13) büyük olduğu için kök anahtarının sağındaki pointer takip edilerek 17-24 düğümüne konumlanır. Bu düğümün ilk anahtarı (17) ile karşılaştırma yapılır, 19 değeri 17 ile 24 arasında olduğu için bu iki anahtar arasındaki pointer izlenir ve 19 bulunur.

Clustered Index ile Non-Clustered Index arasındaki en önemli fark, Clustered Index mimarisinde tüm satırlar sıralı olup B-tree ağacının son yaprağında datanın yani data page’in kendisi bulunur. NonClustered index’te ise datalar sıralı olmayıp ağacın son yaprağında data page değil index page bulunur. Index Page’da her satırda Nonclustered key value ve row locator bulunur. NonCluster bir heap veya clustered table üzerinde tanımlanabileceği için Row Locator ya pointer gibi doğrudan Row’un kendisine işaret eder veya o row için tanımlı olan clustered index key’i içerir. Yani eğer tablo Heap formatındaysa Row Locator, Data Dosya Numarası | Sayfa Numarası ve Satır Numarası bilgisinden oluşan bir pointer içerir. Bu bilgiye Row ID (RID) denilir. Eğer tablo üzerinde clustered index var ise veya indexed view ise Row Locator, o satıra ait clustered index key’i içerir.

https://warren.chinalle.com/’dan aldığım aşağıdaki şekiller bu üç durumu çok güzel şekillendirmiş.

Clustered Index

Heap üzerine kurulu Non-Clustered Index

Clustered Index üzerine kurulu Non-Clustered Index

Bu yazıda oluşturduğumuz MUSTERI tablosunu DROP edip aşağıdaki formatta CREATE edip tabloya 6000 kayıt INSERT edelim. Ardından MusteriId alanı üzerinde bir clustered index oluşturalım.

CREATE TABLE MUSTERI (MusteriId INT IDENTITY(1,1), 
	AdSoyad as 'AdSoyad_'+ CAST(MusteriId as CHAR(2000)), 
	Sehir as 'Sehir_'+ CAST(MusteriId as CHAR(2000))
)
GO
INSERT INTO MUSTERI DEFAULT VALUES
GO 6000

GO
CREATE CLUSTERED INDEX IX_MusteriId ON MUSTERI(MusteriId)

Oluşturduğumuz Index hakkında bilgi almak için sys.dm_db_index_physical_stats isimli DMV kullanılır. SQL Server’in önceki sürümlerinde DBCC SHOWCONTIG ve DBCC INDEXDEFRAG komutları kullanılıyordu.

SELECT index_depth
, index_level
, record_count
, page_count
, avg_page_space_used_in_percent
, min_record_size_in_bytes
, max_record_size_in_bytes
, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID ('BLOG') , OBJECT_ID ('MUSTERI'), 1, NULL, 'DETAILED');

index_depth: Bu indexin derinliğini belirtir. Bu index 2 derinliklidir. Yani ağaç 2 seviyelidir.
index_level: Buradaki değer o sayfanın ağacın hangi seviyesinde olduğunu belirtir. 0: her zaman Leaf Row’u temsil eder. “1” değeri ağaç 2 derinlikli olduğu için doğrudan Root Level’i temsil ediyor. Eğer ağaç 2 derinlikli olsaydı “1” değeri intermediate level’daki index page’leri, “2” değeri de rool level’daki index page’i temsil ediyor olacaktı. Sonuca baktığımızda 1 tane root ve 10 tane de leaf level’da asıl datayı tutan data page bulunmaktadır.

Page’lerin listesini alalım.

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_type_desc 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('MUSTERI'), NULL, NULL, 'DETAILED')

--Veya
DBCC TRACEON(3604)
DBCC IND('BLOG', 'MUSTERI', -1)

DBCC PAGE(BLOG, 1, 190, 3) komutuyla Root Page’in düzenine bakalım.

ChildPageId: Root Page altında bulunan sayfayı belirtir. Non-Leaf Level Page’lerde alt level page’lere erişmek için ChildFileID ve ChildPageID bilgileri tutulur. Şekildeki UNIQUIFIER kolonu, Clustered Index Unique olarak tanımlanmadığı durumda, anahtar değerlerini tekilleştirmek için 4 kullanılır. 4 byte integer değerindedir.

Sonuca göre 630 numaralı müşteri 189 nolu sayfada duruyor. Aynı şekilde 3200 nolu müşteri 198 nolu Page’de duruyor. Bu iki müşteriyi satırların fiziksel konumuyla birlikte SELECT edelim.

SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) FROM MUSTERI WHERE MusteriId IN (630, 3200)

Buradaki en önemli konulardan biri de Heap formatındaki tablodan farklı olarak Clustered Index olduğu için sadece 630 ve 3200 nolu Data Page’ler belleğe taşınır. Tabi bunları adresleyen 190 nolu Index Page de buffer’a alınır. Yani sadece ihtiyaç duyulan sayfalar için I/O yapılmış olur.

Şimdide bir Non-Clustered Index oluşturalım. AdSoyad alanı 900byte’dan büyük olduğu için index key olarak kullanılamayacaktır. Bu nedenle AdSoyadRumuz ve SehirRumuz isminde yeni kolon ekleyip aşağıdaki gibi güncelleyip AdSoyadRumuz üzerine NONCLUSTERED INDEX oluşturacağım.

ALTER TABLE MUSTERI ADD AdSoyadRumuz VARCHAR(100)
UPDATE MUSTERI SET AdSoyadRumuz='A'+ CAST(MusteriId as VARCHAR)

ALTER TABLE MUSTERI ADD SehirRumuz VARCHAR(100)
UPDATE MUSTERI SET SehirRumuz='S'+ CAST(MusteriId as VARCHAR)

CREATE INDEX IX_AdSoyadRumuz ON MUSTERI(AdSoyadRumuz)

Tablo üzerinde oluşmuş indexlerin fiziksel durumlarını listeyelim.

SELECT index_depth
, index_level
, index_type_desc
, record_count
, page_count
, avg_page_space_used_in_percent
, min_record_size_in_bytes
, max_record_size_in_bytes
, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats (DB_ID ('BLOG') , OBJECT_ID ('MUSTERI'), NULL, NULL, 'DETAILED');

DBCC IND(‘BLOG’, ‘MUSTERI’, -1) komutuyla yeniden Page’leri listediğimizde IndexID=2, PageType=2 yani Non-Clustered tabanlı Index Page’leri geldiğini görürür. Bu listenin de Root Page’i 216 nolu Page görünüyor.

DBCC PAGE(BLOG, 1, 216, 3) komutuyla sayfanın içeriğine göz atalım.

Tablo üzerinde Clustered Index olduğu için oluşturduğumuz Non Clustered Index data row yerine Cluster Index (MusteriId) alanına pointer etmiş durumda. Bu tabloda “AdSoyadRumuz=A2930” değerini aradığımızda bu alan üzerinde NonClustered Index olduğu için öncelikle bu index’in Root Page’ni bulur ardından bu değerin olduğu Page’i bulur ve oradaki Cluster Index Key’e atlar. Fakat aradığımız data Non Clustered Index içerisinde mevcutsa yani Cluster Index’e gitmeye gerek kalmazsa sadece Non Clustered Index Page’leri belleğe taşır. Aşağıdaki sorgularda ihtiyacımızı Non Clustered Index’te karşıladığımız için Clustered Index’e gitmeyecektir.

SELECT MusteriId FROM MUSTERI WHERE AdSoyadRumuz='A2930'
SELECT AdSoyadRumuz FROM MUSTERI WHERE AdSoyadRumuz='A2930'
SELECT COUNT(*) FROM MUSTERI WHERE AdSoyadRumuz='A2930'

Yani Non Cluster Index Root Page=216 ve “A2930″un bulunduğu Index Page=325’i okuması yeterli olacaktır. Fakat AdSoyadRumuz ve MusteriId dışında bir alanı talep edersek o zaman tablonun kendisine gitmesi gerektiği için Clustered Index Page’leri de belleğe taşınmış olur. Aşağıdaki sorgularda NonClustered Index Page’lerle birlikte Clustered Index Page’ler de (Root Page=190, Data Page=249) okunmuş olur.

SELECT SehirRumuz FROM MUSTERI WHERE AdSoyadRumuz='A2930'
SELECT * FROM MUSTERI WHERE AdSoyadRumuz='A2930'

“SELECT Sehir FROM MUSTERI WHERE AdSoyadRumuz=’A2930′” sorgusunda Clustered Index’e gitmeye gerek yok çünkü bizim örnekte “Sehir” kolonu “MusteriId” kolonundan hesaplanan bir computed column olarak tanımlanmıştı.

Eğer tablo üzerinde Clustered Index olmasaydı NonClustered Index, doğrudan HEAP üzerinde çalışacaktı yani RID’ye işaret edecekti. Bu durumda Index Page’in içeriği aşağıdaki gibi olacaktı.

SQL Server 2005 ile birlikte gelen Covering Index diğer indexler gibi depolanır tek farkı index key ile birlikte ihtiyaç duyduğumuz diğer kolon veya kolonların da index page’lerde tutuluyor olmasıdır. Yani ihtiyaç duyulan tüm data leaf level page’lerde tutulur.

SQL Server 2012 ile birlikte gelmiş olan ve sadece Enterprise, Evaluation ve Developer sürümlerinde bulunan Columnstore Index türü de yazıda anlatığında Page yapısında tutulur.

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’da table, index, clustered, non clustered index nasıl çalışır?
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