Verilerinizi ne kadar verimli depoluyorsunuz?

Selam millet,

Leaf Level'daki sayfalarınızın doluluk oranının ne olduğuna en son ne zaman baktınız? Veya hiç baktınız mı? Index Fragmentation konusunun başlığı altındaki Dahili Parçalanma, tam da anlatıyor olacağım şeylerden bahseder. Şimdi Index Fragmentation konusuna girmeyeceğim, sadece atıfta bulunmak istedim, çünkü birbirleriyle alakalı konular. Size, gerek B-Tree elemanlarının sayfa sayılarını nasıl bulabileceğinizi ve bu sayfalardaki ortalama sayfa doluluk oranına nasıl bakacağınızı anlatmak istiyorum.

Bu bilgilere ulaşmak için sys.dm_db_index_physical_stats DMV'sinden faydalanıyoruz. Burada sizlere örneklerle anlatmak için, Production ortamındaki bir tablomuzu kullanacağım. Bu tabloda verinin yanında bol bol da Index var. Tablom ise Clustered yapıda. Elbette tablonuz Heap yapıda olduğunda veya BLOB veriler tuttuğunuzda veya bir kaydın tek başına bir sayfaya sığmadığında farklı bazı sonuçlarla da karşılaşıyor olacaksınız, fakat hepsini bir yazıya sığdırmak zor. O nedenle bu yazıda sadece Clustered yapıdaki bir tablomdan örnek vereceğim ve bu tablodaki tüm kayıtlar bir sayfaya sığacak küçüklükte.

Kullandığım sorgu şöyle:

SELECT index_id, partition_number, index_type_desc, index_level, page_count, avg_page_space_used_in_percent, record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tablo_adı'), NULL, NULL, 'detailed')

Not: sys.dm_db_index_physical_stats DMV'sinin ayrıntıları için BOL'dan faydalanabilirsiniz.

Bu sorguyu çalıştırdığımda dönen sonuç da aşağıda:

Önceden de dediğim gibi, bu yazımda Fragmentation'a değinmeyeceğim, maksadım gerek Leaf Level'da, gerekse diğer Level'larda sayfa doluluk oranı, ortalama sayfa boyutu gibi ayrıntıları göstermek. Bu nedenle sorguya sadece ilgili alanları ekledim. Şimdi bu alanlardan nasıl bilgilere ulaşabileceğinizi izah etmeye çalışayım.

index_id: Bu alanı sys.indexes tablosundaki index_id alanıyla birleştirip Index'in adını bulabilirsiniz. İlgili kaydın, hangi Index'e ait olduğunu belirtiyor.

partition_number: Eğer tablonuzun birden fazla Partition'ı varsa (ki SQL Server 2005'ten beri tüm tablolar varsayılan olarak 1 Partition'dır) o zaman burada ilgili kaydın hangi Partition'a ait olduğu belirtilir. sys.dm_db_index_physical_stats DMV'sinin 4. parametresi (ben NULL kullandım yukarıda) Partition numarası içindir, eğer tablom Partitioned olsaydı, o zaman sadece belli bir Partition için de çalıştırabilirdim sorgumu.

index_type_desc: (Varsa) Index'imizin tipinin açıklamasını belirtiyor. Eğer tablomuz Clustered ise zaten index_id'si de 1 olacaktır, eğer daha büyük bir sayı ise o zaman bu kayıt Nonclustered bir Index ile ilgilidir. Heap tablolarda ise bu değer 0'dır.

index_level: B-Tree'deki katmanları simgeler. 0, Leaf Level'dır, yani tüm gerçek verinin (sayfalar içerisinde) bulunduğu katman. Yukarısında Branch Level (Intermediate Level da denir)'lar vardır ve en üstte de Root Level vardır. Yani buradaki en yüksek değer Root Level'ı temsil eder. En yüksek değer ile 0 arasında kalanlar da Brach Level'ları. Ben bu sorguya ekran görüntüsü çok genişliyor diye index_depth alanını katmadım, o alanda ilgili Index'in toplam kaç katmandan oluştuğu belirtiliyor. Örneğin index_id = 1'in index_depth'i 4'tür.

page_count: İlgili katmanda kaç adet sayfa (Page) olduğunu gösterir. Haliyle tüm kayıtlar Leaf Level'da bulunduğu en çok page_count değeri olanlar index_level'ı = 0 olanlardır. Burada hemen bilmeyen arkadaşlar için belirteyim, SQL Server'da veriler sayfalarda tutulur. Bir sayfanın içerisinde birçok kayıt barındırılabilir, bu tamamen kayıtların büyüklükleriyle ilgilidir. 1 sayfanın boyutunun 8kb olduğu notunu da ekleyeyim.

avg_page_space_used_in_percent: İlgili katmandaki sayfaların ortalama doluluk oranını belirtir. Örneğin yukarıdaki ekran görüntüsündeki Clustered Index'imin Leaf Level'ınındaki sayfalarımın ortalama doluluk oranı yuvarlarsak %92.88 imiş, fena bir oran değil. Eğer bu oran yeterince yüksel değilse, o zaman öncelikle kullandığınız Fillfactor değerlerine bir gözatın. Bunun yanında, tablolarınızdan yüksek miktarda veri silme işlemi yaptığınızda da bu oranın düştüğünü gözlemleyebilirsiniz. Sayfalarınızın doluluk oranları ne kadar düşükse, sadece disk altyapınızı değil, hafıza kaynaklarınızı da verimsiz kullanmış olursunuz. SQL Server önceden de belirttiğim gibi kayıtlarınızı sayfalar şeklinde saklar. Her sayfanın da 8kb olduğunu belirtmiştim, yani diskten veri okuyacağı veya yazacağı zaman sayfanın doluluk oranı fark etmeksizin yapacaktır bu işlemi. Örneğin 100 kayıtlık bir sorgu çalıştırıldığında, bu 100 kayıt sadece 1 sayfadan da okunabilir, yani maliyeti 8kb olur, onlarca sayfadan da, onlarca kb. Tabii ki maliyet çok daha büyük kayıt sayılarında duruma göre katlanacaktır.

record_count: sayfalarınızda tutulan toplam kayıt sayısını belirtmektedir. Bu sefer aşağıdaki ekran görüntüsüne bakın lütfen. Bu sefer bu tablo için hem sp_spaceused komutunu, hem de sys.dm_db_index_physical_stats sorgusunu birlikte çalıştırdım. Ekran görüntüsünden görebileceğiniz üzere, sp_spaceused komutuyla dönen kayıt sayısı ile (rows) sys.dm_db_index_physical_stats'ten dönen kayıt sayısı (record_count) birebir aynı. Yine aşağıdaki ekran görüntüsündeki ilk kaydı örnek olarak alırsak, 8.152.785 adet kaydın 600.059 adet sayfada tutulduğunu görebiliriz. Bu bilgilerle de tablomuzdaki verinin boyutunu x 8kb işlemiyle öğrenebiliriz. Bu hesabı ve sonucunu ve sp_spaceused'tan gelen sonuç ile nasıl eşleştiğini aşağıdaki ekran görüntüsünde bulabilirsiniz.

min_record_size_in_bytes: Bu değer, sayfalarımız içerisindeki en küçük kaydın boyutunun bilgisini verir. Örneğin Clustered Index'imizin Leaf Level'ın'daki en küçük kaydımız 277 byte imiş.

max_record_size_in_bytes: Bu değer de sayfalarımız içerisindeki en büyük kaydın boyutunun bilgisini verir. Örneğin Clustered Index'imizin Leaf Level'ın'daki en büyük kaydımız 844 byte imiş.

avg_record_size_in_bytes: Adından da anlaşılacağı üzere tüm sayfalardaki tüm kayıtların ortalama boyutu bilgisini veriyor.

Bu yazımda sizlere sayfalarımızı içerisindeki verilerin genel durumu hakkında bilgi alabileceğimiz ve bununla ilgili neler yapabileceğimiz hakkında genel bir bilgi paylaşmaya çalıştım. Umarım verilerinizin nasıl saklandığına ve bunun nasıl optimize edilebileceğine dair bir fikir edinmişsinizdir.

Sevgiler,
Ekrem Önsoy