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

Index'lerden Corrupt kayıtları kurtarma

Aynen öyle! Şahsen, bilmiyordum. Yeni öğrendim ben de. Bu yazıda bunun nasıl olduğunu anlatmayacağım, pek vaktim yok. Fakat yine de nasıl olduğundan özetle ve teoride bahsetmek istedim. Sonra vaktim olursa belki ayrıntılı şekilde açıklama şansım da olur.

Şöyle ki, örneğin toplamda 3500 kayıt olan, alan1 alanı Clustered Index, alan2 alanı da Nonclustered Index olan bir tablonuzun 306 isimli Data Page'i Corrupt oldu diyelim. Bu Data Page de 180 tane kayıt içeriyordu diyelim. DBCC CHECKDB'yi çalıştırdığınızda, hata alan Page ID'sini göreceksiniz zaten. Daha sonra sorun yaşanan tablodaki kayıtları sorgulayın örneğin

SELECT alan1, alan2 FROM tablo ORDER BY id ASC

Bu şekilde Corrupt olan sayfaya kadar olan kayıtlar sorgulanacak ve Corrupt sayfaya gelindiğinde bağlantı kesilecektir. Fakat SSMS'teki Results penceresinde hangi kaydın id'sine kadar gelindiğini göreceksiniz, bu kayıt sayısı da 2500 olsun. Daha sonra da bu sorgunun ters yönlüsünü çalıştırın

SELECT * FROM tablo ORDER BY id DESC

Bu sorgu da 820 kayıt getirdi diyelim. Toplamda tablomuzda 3500 kayıt vardı, ilk sorgu 2500 kayıt getirdi, ikinci sorgu da 820 kayıt getirdi, böylece Corrupt olan Data Page'imizde ne kadar kayıt sayısı olduğunu bulabiliriz.

Ayrıca eğer ilgili kayıtlar belli bir değere göre ilerliyorsa, örneğin bir Identity'ye göre yine DBCC IND ('veritabanı adı', 'tablo adı', 1) komutu ile (Undocumented bir komuttur) Root Page'i bulup, oradan da hata alınan Corrupt Page'ten sonraki Page'in içine DBCC PAGE('veritabanı adı', 1, , 3) komutuyla (Undocumented bir komuttur) bakıp Corrupt olmuş Page'in içindeki kaydın nerede sonlandığını bulabiliriz. Tabii ki aynı yöntem ile Corruption'ın başladığı kaydı da bulabiliriz.

sp_helpindex 'tablo' komutuyla tablodaki Index'lerin hangi alanları kapsadığına bakarız. Bu komut INCLUDED alanları göstermeyeceği için, Index'lerin Script'lerine bakmak iyi olacaktır. Gerçek bir senaryoda böyle bir durumda ne kadar çok Index'imiz varsa, o kadar çok kaydı kurtarabileceğiz anlamına gelir. Tabii ki uygun bir yedek alma stratejimiz yoksa, en son başvuracağımız yöntemlerden biridir bu...

Bu noktada tablo isimli tablonun Schema olarak bir kopyası oluşturulmalı ve aşağıdakine benzer komutlarla Index'lerden (eğer onlar da Corrupt olmadılarsa tabii, bizim senaryomuzda sadece veri Corrupt oldu) veriler kurtarılmalıdır:

SELECT alan1, alan2 FROM tablo WITH (Index=3) ORDER BY id ASC

Nonclustered Index'ler her zaman Clustered Index Key'lerini de barındırdığı için, sadece Nonclustered Index bile böyle bir senaryoda yetecektir...

Harika değil mi?

Ekrem Önsoy

Sorgu kriterlerinde değişken kullanmanın sonuçları

Zaman zaman, sorgulardaki WHERE kriterlerinde parametre kullanmayı Best Practice olarak uyguladığını söyleyen arkadaşlara rastlıyorum. İstatistikler konusunda bir çalışma yaparken, çok taslak ve küçük bir test ortamı yaratıp size bu konuyla alakalı birkaç örnek göstermek istedim. Eminim faydası olacaktır.

Öncelikle değişken kullandığımız sorguların önceden yorumlanamadığını, run-time esnasında okunduğunu belirtmeliyim. Bu durumda da SQL Server o parametrenin değerinin ne olduğunu bilemiyor. Böylece ilgili İstatistik bilgilerinden ve dolayısıyla da en doğru Index'ten en iyi şekilde faydalanamıyor ve eğer tabloda herhangi bir İstatistik varsa, o İstatistikteki bazı bilgilerden (aşağıda ayrıntısını anlatacağım) faydalanarak ortalama bir kayıt sayısına göre Index/Table Scan veya Index/Table Seek işlemi yapıyor. SQL Server'ın Query Optimizer'ı sorgu masraflarını Cost Based olarak hesapladığı için İstatistikler SQL Server açısından hayati bir öneme sahip.

Şunu vurgulamakta fayda var, burada size temel olarak göstermek istediğim şey, SQL Server'ın İstatistikleri kullanarak yaptığı tahminler ve gerçekler. SQL Server yaptığı tahminleri girilen WHERE kriterindeki veriye (parametre, değişken, Literal) göre İstatistikleri kullanarak (varsa?) yapar. Örneğin bir tabloda 100.000 adet kayıt varsa ve siz sorgunuzla 30.000 kayıt döndürecekseniz SQL Server sorgularda kullanılan kriterlere göre ilgili İstatistikleri de kullanarak Index/Table Scan operatörünü kullanarak yapacaktır bu işlemi. Sorgulanacak çok fazla kayıt olacağı için doğru olan, bu operatörle daha az masraflı olacağı için bu olurdu. Eğer bu işlemi Index Seek operatörüyle yapmaya kalkarsa çok daha masraflı olacaktır. İşte tam bu noktada SQL Server tabloda, sorgunuzdaki WHERE kriterlerine göre kaç kayıt olduğunu İstatistikler yardımıyla hesaplar. Bunu SQL Server Management Studio'daki Execution Plan'ındaki ilgili operatörün üstünde beklerken çıkan Hint'te Estimated Number of Rows olarak görürsünüz. Sorgunuzu çalıştırdıktan sonra ortaya çıkan sonucu da Actual Number of Rows olarak görürsünüz. Normal şartlar altında bir Execution Plan'ı incelerken bu iki alandaki değerlerin eşit olmasını beklersiniz. Eğer değillerse ve iki değer arasında çok fark varsa, ya İstatistikler güncel değildir ya da değişken kullanıldığı için SQL Server ... WHERE ...'de girilen değeri bilemediği için Estimated Number of Rows değerini İstatistikteki (hesaplaması için aşağıdaki resme bakabilirsiniz) bazı verileri kullanarak tahmin edecektir.

Resim1: Estimated Number of Rows hesaplaması

Birkaç ekran görüntüsüyle örnekleyerek anlatmak genelde konuların anlaşılması için çok faydalı oluyor diye düşünüyorum.

Örneğimde kullandığım stat_test tablosunda 2 tane alan var. Biri (i alanı) INT, diğeri (tarih alanı) DATETIME. "i" alanı Clustered Index, "tarih" alanı için de NIX_tarih adında Nonclustered bir Index var.

Aşağıdaki ekran görüntüsüne bakarsanız Literal kullanarak yazdığım sorguyu görebilirsiniz:

Resim2

select * from stat_test where tarih = '2014-02-20 00:00:00.000'

Yukarıdaki ekran görüntüsünde Actual Number of Rows ve Estimated Number of Rows değerlerini işaretledim, lütfen dikkatle bakın. Gördüğünüz gibi iki değer de birbirinin aynısı. Yani SQL Server bu sorguyu çalıştırırken tarih alanı için sorgunun kaç tane kayıt döndüreceğini zaten biliyordu. Bu nedenle de kayıtları getirirken en doğru operatörü kullanabiliyor. 

Aşağıdaki ekran görüntüsündeki Actual Number of Rows ve Estimated Number of Rows değerlerine bakarsanız farklı değerler olduğunu görürsünüz. Bu sorguda değişken kullanıldığı için SQL Server İstatistikleri kontrol etmeden önce tarih alanı için hangi değerin kullanıldığını bilmiyor. Bu nedenle de Resim1'deki ekran görüntüsünde açıkladığım hesaplamayla yapabileceği en iyi şekilde tarih alanı için tabloda kaç tane kayıt olduğunu tahmin etmeye çalıştı ve buna göre de bir operatör seçti. Bizim örneğimizde kayıt sayısı çok fazla olmadığı için sorgunun bu haliyle de Index Seek operatörünün seçildiğini görüyorsunuz. Fakat farklı kayıt sayılarında farklı ve muhtemelen yanlış operatörler seçilecek ve IO, RAM, CPU gibi kaynakların çok kötü bir şekilde kullanıldığı gözlemlenecektir.

Resim3

Sonuç olarak, eğer tabloda aslında ilgili alan için (tarih diyelim) 100.000 adet kayıt varken, kullanılan değişken nedeniyle İstatistikten tam verimli bir biçimde yararlanılamaması nedeniyle bu kayıt sayısı 1.000 olarak tahmin edilebilir ve bunun neticesinde Index Scan yerine Index Seek operatörünün kullanılmasına karar verebilirdi SQL Server ve bu durumda da işlemi gerçekleştirmek için yanlış operatörler kullanılacak ve birçok durumda olduğu gibi sistem çalışamaz hale gelecekti.

Bu konu aslında çok derin bir konu ve normalde en az 3-4 saat aralıksız ve derinlemesine çalışılması gereken bir konu, ama size fikir olsun diye çok özetle ve temel düzeyde anlatmaya çalıştım. 

Kolay gelsin,

Ekrem Önsoy