Prosedür (SQL Stored Procedure) Parametreleri Nasıl Sorgulanır

Veritabanı hakkında bilgi toplayan veya bazı kod parçacıklarını dinamik olarak oluşturan araçlar yazamamız gerektiğinde, bir Stored Procedure'den parametre bilgilerini almamız gerekebilir. Bu durum pek sık karşılaşılan bir durum olmasada, başımıza gelirse ne şekillerde bu sorunu çözebiliriz, ben nasıl çözdüm paylaşmak istedim.

Öncelikle elimizin altında her zaman "sp_helptext" SP'si var. Bu şekilde hakkında bilgi almak istediğimiz SP'leri sorgulayabiliriz. "sp_helptext SP_ADI" şeklinde sorguladığımız zaman SP'nin tüm içeriğini text olarak bize dönecektir. Buradan sonra string işlemleri ile gereken bilgilere ulaşabiliriz. Bu durum sorunumuz için kullanabileceğimiz en kötü yöntem ama genede bir çözüm. Özellikle standart bir kod yazım stiline sahip olmayan kodlamalarda, bir çok kez sıkıntı yaşamamız olasıdır.

Enterprise Library kullanıyorsak eğer, burada oluşurduğumuz Database'in GetStoredProcCommand metodundan bir DBCommand (adı cmd olsun) oluşturduktan sonra, gene Database'in DiscoverParameters metodunu oluşturduğumuz cmd ile kullanarak SP'mizin tüm parametrelerini alabiliriz. Bu yöntem yukarıdaki ilk öneriden çok daha garantili ve performanslı bir yöntemdir. 
DiscoverParameters metodu bize basitçe bir DbParameterCollection'ı hazırlayacaktır. Artık cmd.Parameters dediğimizde istediğimiz parametrenin her bilgisine ulaşabiliriz.

Peki Enterprise Library bu bilgileri nasıl alıyor derseniz. En bağımsız son yöntem direk veritabanına SP parametrelerini sormak. SQL Server versiyonu bu sorgularda önemlidir.

2000 İçin;
[sp_procedure_params_rowset]
Örnek:

USE MYDB

exec [sp_procedure_params_rowset] @procedure_name = 'SP_NAME', @procedure_schema = 'SP_SCHEMA'

2005 ve Muhtemelen 2008 İçin
[sys].[sp_procedure_params_managed]

Bu SP'ler detaylı parametre bilgilerini bizim seçtiğimiz SP'ler için dönecektir.

SQL 2005 üzerinde bulunan bu tür diğer kullanışlı sys SP'leri;
[sys].sp_procedure_params_90_rowset
[sys].sp_procedures_rowset
[sys].sp_procedures_rowset2 
[sys].sp_procedure_params_90_rowset2
[sys].sp_procedure_params_rowset
[sys].sp_procedure_params_rowset2

Tabloların Fiziksel Yapıları

SQL 2005 ve muhtemelen SQL 2008 için, tablo dediğimiz yapılar nasıldır, mantıksal ve fiziksel olarak nasıl işlenir dersek...

Büyükten küçüğe doğru gidersek Tablo, Extent, Page olarak sıralamamızı yapabiliriz. Bu sıradan da anlaşılacağı gibi Page'ler ile Extent'ler oluşur. Extent'ler ile de tablo dediğimiz yapı oluşur.

SQL'de tabloların oluşabileceği 3 çeşit allocation unit vardır. Tablo bunlardan bir veya daha fazlası ile oluşturulabilir. Her tabloda mutlaka olan tek allocation unit IN_ROW_DATA'dır. Diğerleri ise LOB_DATA ve ROW_OVERFLOW_DATA'dır.

IN_ROW_DATA türü allocaion unitler; büyük olmayan veri tiplerini barındırır. Eğer varchar, nvarchar, varbinary, sql_variant veri tiplerinin toplam satır boyutu 8KB üzerinde çıkarsa, ROW_OVERFLOW_DATA kullanılmaya başlanır. Bu kısımların detaylarını Page'leri incelerken göreceğiz.

LOB_DATA türü allocation unit'ler; text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) türlerini barındırır.

SQL'de Page boyutu 8KB'dir. Disk işlemleri Page üzerinden yapılır yani Page üzerinde okuma veya yazma işlemleri yapılır. Her Page 96 byte'lık bir header bilgisi ile başlar ve bu bilginin hemen ardından Data Row'lar gelir. Header içerisinde Page Number, Page Type, Page için kalan boş alan, ve Page'ın owner (Extent) bilgisi tutulur. Her bir Data Row'un, Page üzerindeki ilk Data Row'dan ne kadar sonra başladığı bilgisi Page'in en altındaki offset tablosunda tutulur. Bu tablodaki sıralama Data Row'ların Page içerisindeki sıralamasının tam tersidir.

Bir Data Row birden fazla Page içerisinde olamaz. Peki o zaman bizim 8K'dan büyük satır veya alanlarımız ne olacak derseniz. Büyük veri barındıran Data Row'lar farklı tür bir allocation unit içerisindeki Page'lerde saklanır. text, ntext, image, nvarchar(max), varchar(max), varbinary(max), ve xml veri tipleri LOB_DATA türünde, varchar, nvarchar, varbinary, sql_variant veri tiplerinden toplam içeriği 8KB üzerinde olanlar ROW_OVERFLOW_DATA türündeki allocation unit içerisindeki Page'lerde saklanır.

Normal şartlarda varchar, nvarchar, varbinary, sql_variant veri tiplerin tek başına uzunluğu 8K'yı geçemez ancak birlikte toplam uzunlukları bu değeri geçebilir. Bir INSERT veya UPDATE işlemi toplam row boyutunu (tüm kolonlar ile) 8K üzerine taşırsa, bu row ROW_OVERFLOW_DATA üzerindeki bir Page'e taşınır, bu taşımada en büyük olan kolon uzunluğuna göre bir yeni Page uzunluğu belirlenir. Taşınmadan önceki IN_ROW_DATA üzerindeki değeri de 24 byte'lık bir pointer ile güncellenir. Toplam row boyutu 8K altına düştüğünde ise, ilgili işlemler geri alınır ve row tekrar IN_ROW_DATA allocation unit üzerindeki Page' taşınır.

IN_ROW_DATA ve ROW_OVERFLOW_DATA arasındaki yazma işlemleri zaman alıcı işlemlerdir. Ayrıca taşınan kolonlar sorgu için kullanıldıklarında bu işlemde normal IN_ROW_DATA üzerindeki veri okumasına göre daha uzun sürmektedir. Bu sebeplerden tablo oluştururken bu tür toplam değerlerin 8K'yı geçip geçmediği ayrıca kontrol edilmelidir.
Bu alanların ne sıklıkla sorgulara dahil edildiği düşünülmelidir. Hem IN_ROW_DATA hemde ROW_OVERFLOW_DATA üzerinden senkron okumalar yapmak maliyetli ve zaman alıcı işlemlerdir. Eğer yapılabiliyorsa bu alanlar farklı bir tabloya çekilmelidir. Bu şekilde senkron okuma sayısı azaltılabilir. Farklı tablo join edildiğinde asenkron okuma kullanılacak, iki farklı tablo okunacak ve birleştirilecektir, kısaca ana tablo değerleri daha hızlı elde edilecektir.
Buradaki bir kritik nokta da şudur; Clustered Index Key alanları ROW_OVERFLOW_DATA üzerinde asla olamaz, eğer varchar alanımız Clustered Index Key ise, toplam uzunluğun 8K'yı geçirdiği durumunda, işlemler başarısız olacaktır. Bu konu ile ilgili daha detaylı bilgiye buradan ulaşılabilir.

Page'lerin gruplanmasıyla Extext'ler oluşur. Extent'ler (boş) alan yönetimlerinin kullanımı için kullanılır. Bir extent 8 ardışık Page'den veya 64K'dan oluşur. Verimli kullanımlar için Extent'ler birden fazla tablo Page'lerini barındırabilir, bu tür Extent'lere Mixed Extent denir. Sadece tek tablo Page'lerini bulunduran Extent'lere Uniform Extent denir. Mixed Extent'ler genelde ufak tablolar için kullanılır. Bir tablo 8 Page boyutunu aşarsa, ilgili tabloya ait Mixed Extent'ler Uniform Extent'lere dönüştürülür. Bu konunun detayını burada bulabilirsiniz.

Peki tablo türlerine göre daha kabaca yapıları incelersek nasıl bir durum ile karşılaşırız...

Heap
Heap dediğimiz aslında Clustered Index'i olmayan bir tablodur, bu tabloda kayıtlar herhangi bir sırada değildir. Heap'ler yukarıdaki üç allocation unit türlerine sahip olabilir. Hangisi veya hangilerine sahip olacaklarını yukarıda anlatılan row boyutu ve veri türleri belirler. Heap'lerin Clustered Index veya Non-Clustered Index'lerden farkları; Heap Data Page kayıtları sıralı ve birbirleri ile bağlantılı değildir. (Not Linked.) sys.partitions içerisinde, tek Partition'lı Heap'ler için 1 kayıt gelir ve index_id alanının değeri 0'dır. Birden fazla partition kullanılmış ise Partition sayısı kadar kayıt gelecektir.
Aşağıdaki Heap veri tutma şeması size bir fikir verebilir. Konu ile ilgili detaya buradan ulaşabilrisiniz.
Aşağıdaki şema bir Partition içindir, Heap eğer birden fazla Partition'dan oluşuyorsa, her bir Partition üzerinde aşağıdaki yapıdan birer tane vardır.

Clustered Index
Clustered Index'lerde, Index'ler B-tree mantığı ile tutulur. En üstte bir Root Node, en altta verileri tutan Leaf Node ve araların Intermediate Level denilen yerde bağlantı Node bulunur.
Leaf Node verileri barındıran Data Page'leri tutarken, Intermediate ve Root Level Node'lar Index bilgilerini barındıran Index Page'lerini tutar. Her bir Index Row, bir değer ve bir pointer tutar. Bu pointer B-tree üzerindeki Intermediate Level Page veya Leaf üzerindeki bir Data Row olabilir. Clustered Index'lerde Page'ler, birbirlerine çift olarak bağlıdır. (Doubly-Linked) Clustered Index üzerinde yapılan aramalarda, bu bağlı olma durumu, aramanın çok hızlı bir şekilde yapılmasına olanak vermektedir.
sys.partitions içerisinde, tek Partition'lı Clustered Index için 1 kayıt gelir ve index_id alanının değeri 1'dir. Birden fazla partition kullanılmış ise Partition sayısı kadar kayıt gelecektir. Aşağıdaki şema tek Partition içindir, çoklu Partition kullanımında, aşağıdaki şemadan her bir Partition için ayrı ayrı olacaktır.Konu ile ilgili detaya buradan ulaşabilrisiniz.

Non-Clustered Index
Non-Clustered Index'ler aslında Clustered Index'ler gibi B-tree yapısındadır. Aralarındaki en önemli fark ise Non-Clustered Index'ler veri tutmazlar, Key kolonları ve Clustered Index'e veya Heap'e referans bilgisini tutarlar. Dolayısıyla Leaf Node Data Page yerine Index Page'lerden oluşur. (Include kolonlar hariç.)
sys.partitions içerisinde, index_id alanı 0'dan büyüktür. Partition'lara göre davranışı Clustered Index ile aynıdır. Bir Partition için oluşan yapısının şeması aşağıdaki gibidir.
Detaylı bilgiye buradan ulaşabilirsiniz. Non-Clustered Index'lerin key kolon sayısı en fazla 16 olabilir ve bu kolonların toplam uzunluğu 900 byte'ı geçemez.

Non-Clustered Index'lerin Heap, Unique Clustered Index ve Non-Unique Clustered Index'ler ile nasıl eşleştiğinin detaylarını buradaki yazımdan bulabilirsiniz. Include kolonları ve işleyişleri ile ilgili olarak da buradaki yazımdan faydalanabilirsiniz.

Non-Clustered Index ve Include Kullanımı

SQL Server 2005 ve 2008'de Non-Clustred Index'lere Include kolonları ekleme şansımız var. Clustered Index'ler üzerinde ise Include kullanma imkanımız yok. Zaten Clustered Index verilerimizin kendisi olduğundan dolayı, tüm kolonlarımız Clustered Index'imize Include edilmiş haldedir diyebiliriz.

Non-Clustered Indexlerde, Index'i belirleyen Key kolonların yanında nonkey kolonları "Include" edebiliyoruz. Bu NonKey kolonlar sadece Leaf Node üzerinde saklanıyor. Non-Clustered Index'lerin key kolon sayısı en fazla 16 olabilir ve bu kolonların toplam uzunluğu 900 byte'ı geçemez. Bu kurala uymayan durumlarda Include kullanımı gene bize yardımcı oluyor. Include olarak kullanılan kolonlar Index Size'a dahil edilmiyor.

Bir Müşteri tablosu düşünelim. MüşteriNo alanımız Clustered Index Key'imiz ve Müşteri Soyadı artı İsmi alanları üzerinde bir Non-Clustered Index tanımımız var. İsimle yaptığımız bir aramada bu Non-Clustered Index kullanılıyor. Örneğin; İsmi Can Uzun olan müşterilerimizi ünvanları ile ekrana dökmek istediğimizde (Emin olun sayıları çok fazladır :). Non-Clustered Index üzerinde hızlı bir şekilde ismi uygun olan kayıtlar bulunuyor, bu kayıtlardaki Clustered Index'e ait Logical RID ile Clustered Index'e ulaşılıyor, sonrasında disk üzerinde kaydın yerini bildiğimizden tüm satırı okuyor ve Ünvan bilgisine ulaşıyoruz. Bay Can Uzun şeklinde bir çok kaydı listelemiş oluyoruz.

Peki Non-Clustered Index'imize Unvan alanını Include edersek ne olur. Bu durumda Non-Clustered Index üzerinde uygun olan kayıtlar gene hızlı bir şekilde bulunacak ancak bundan sonra Unvan bilgisi Index'imiz içerisinde zaten olduğundan, Clustered Index üzerinden daha fazla I/O işlemi yapmamıza gerek kalmayacak. Eğer isim aramalarının çoğunda Unvan bilgisi de bize gerekli ise, bu alanı Include yaparak çok ciddi bir performans kazanımı elde edebiliriz.

Bu noktada şunu unutmamak lazım, sorgumuzda tablodaki beş alan bize gerekli ama Index'imizde key ve nonkey alanlarda sadece dördü varsa, SQL motoru Clustered Index üzerinden satırı okumak zorunda kalacaktır. Bu sorguda Include kolonlarımızın olması bizim için bir avantaj olmayacaktır. Diğer yandan Include edilen her kolon Index boyutumuzu arttıracaktır, her Non-Clustered Index'e birçok Include yapmamız bir süre sonra disk sıkıntısı yaşamamıza sebep olacaktır. Özellikle varchar(max) gibi büyük veri tipleri bize çok fazla yer sıkıntısı yaşatacaktır. Bunun yanında Include için kullanılan kolonlar güncellendiğinde Non-Clustered Index'lerin de Include alanları güncellenecektir. 
Kısacası Include alanlar uygun şekilde kullanıldığında bize büyük performans artışı sağlayacaktır. Ancak bu kolonların verileri diskte hem Clustered Index hemde Non-Clustered Index üzerinde olacağından INSERT, UPDATE işlemleri bu durumdan etkilenecektir.

Hangi alanları Include yapmalıyız dersek.

SQL motoru, istatistiklere göre key kolon önerilerinde ne kadar başarılıysa, nonkey kolon önerilerinde de o kadar başarısızdır. Neredeyse tüm tablo kolonlarını size Include edin şeklinde önerecektir. Key kolonlarımıza eklemek istemediğimiz veya gerek duymadığımız, ancak key kolonlarımız ile sürekli veya çok sık kullanılan alanları Include olarak ekleyebiliriz.


Mevcut bir Index'i silip baştan oluşturmadan, Include alanları değiştiremediğimizi de unutmamalıyız.

Clustered Index Seçimi

Clustered Index seçimi tablolarımızdaki en önemli seçimdir diyebiliriz. Clustered Index, fiziksel olarak disk üzerindeki sıralamanın oluşturulmasında kullanılan clustered key veya clustered key'ler içerir. Detayını buradaki yazımda okuyabilirsiniz.

Peki Clustered Index seçerken nelere dikkat etmeliyiz, veya kesinlikle önerilen bir yöntem varmıdır dersek. Genel veritabanı kuralı olan duruma göre değişir lafı burada da geçerli :)

Öncelikle hangi tablolarda Clustered Index kullanmamalıyız konusunu aradan çıkaralım. Sürekli güncellenen veya tablolar arası taşıma durumlarının çok olduğu tablolarda Clustered Index kullanılmayabilir, bu sadece bir öneridir, uygun durum sistemden sisteme değişebilir, bu sebepten iki seçenekte denenerek daha verimli olan tercih edilmelidir. Bir diğer durum ise, büyük kolonlardan oluşan tablolarda her Non-Clustered Index için Clustered Index Key Logical RID kullanılacağından, index boyutları çok büyük olacaktır. Bu tür tablolarda da Clustered Index ve Heap denenmeli, daha verimli olan yöntem tercih edilmelidir.

Clustred Index kullanmaya karar verdiysek, şimdi hangi kolon veya kolonları seçmemiz gerektiğine karar vermemiz kalıyor geriye.

Örneğin çok sayıda INSERT alan tablolarda yeni gelen kaydın tablonun en altına eklenmesi genellikle tercih edilen bir yöntemdir. Bu durumda giderek artan değerlerden oluşan bir Clustered Index yapısı tercih edilebilir. Bu tür bir seçimde seçilen key'in Unique olup olmaması konusuda önemlidir, onun detaylarını da buradan okuyabilirsiniz.

Clustered Index key güncellenen bir kolon olmamalıdır. Bu her güncelemede kaydın fiziksel olarak yer değiştirmesi demektir, bu hem performansı çok kötü etkileyecek hemde index üzerindeki fragmantation'ı arttıracaktır.

Clustered Index, disk üzerindeki fiziksel sıralama demek olduğundan, BETWEEN, >, >=, <, <= gibi sorgu filtreleri eğer Clustered Index Key üzerinden sorgulama yaparlarsa SQL kaynak kullanımı en alt seviyede olacak ve disk üzerinden fiziksel okuma ile kayıtlar çok hızlı elde edilebilecektir. Clustered Index Key aynı şekilde Sort işlemlerinde kullanıldığında, veriler zaten sıralı olduğundan ek bir işleme gerek kalmayacaktır. Group By kullanımında gene sıralı verilerin gruplanması çok daha hızlı olacaktır.

Bu bilgilerin dışında ben kişisel olarak, bu tabloların ne kadar sürede bakım görecekleri ile de çok ilgilenirim. Uygun şartlarda veritabanları haftada bir veya en azından iki haftada bir bakımdan geçmelidir. Ancak benim çalıştığım neredeyse hiç bir şirket bu şekilde bir bakımı düzgün olarak yapmamaktadır. Index yapıları bozulduğunda index'ler sisteme destek değil köstek olmaya başlarlar. Fragmantation değerlerinin %10'lara geldiği nice kritik kullanılan tablo görmüşümdür ve bu şirketler günde yüzbinler veya bazen milyonlarca transaction alan şirketler. Bakım yapmama konusunda inatçı veya umursamaz olan müşterilere veritabanı tasarlarken Clustered Index Key kesinlikle artan bir değer olmalıdır diyebilirim. Non-Clustered Index'ler bozulduğunda buna yapabileceğimiz birşey yok ama en azından bu şekilde Clustered Index'lerin bozulmamasını sağlamış oluyoruz.

Düzgün bakım yapıldığını varsayarak ilerlersek. Az INSERT alan tablolarda INSERT maliyeti kesinlikle ihmal edilmelidir. Yani müşteri bilgilerini tutan bir tablo ile müşterilerin satın alma bilgilerini tutan tablo aynı şekilde değerlendirilmemelidir.

Bir bankayı düşünelim, sizce bir banka günde kaç yeni müşteriyi veritabanına ekleyebilir. Bir de şunu düşünelim, bu bankanın tüm müşterileri bir günce kaç kredi kartı işlemi yapabilir. Bu şekilde düşündüğümüzde sürekli INSERT alan ve az INSERT alan tabloları kafamızda canlandırabilmiş oluyoruz.

Bankamız eğer müşteri numarası ile işlemleri yapıyorsa MüşteriNo alanımızı Clustered Index Key olarak seçebiliriz, ama genellikle isim ve soyad bilgisi ile sorgularımızı yapıyorsak o zaman burada İsim ve Soyad alanları beraber Clustered Key Index olarak seçilebilir. Bu şekilde isim ile yapılan aramalarımız çok daha hızlı sonuçlanacaktır. Soyad bilgisinin isme göre daha değişken olduğunu da düşünürsek Soyad ve İsim sırası ile Clustered Key Index oluşturmamız daha da mantıklı olacaktır. Buradaki amacımız isim ve soyad ile yapılan aramalarda en üst performansı almaktı ve bunu başardık. 
Peki dezavantajlarımız neler oldu?
Öncelikle Soyad ve İsim Unique değildir, bir çok kişi aynı Clustered Index Key'e sahip olacaktır, SQL bunu engellemek için uniquifier denilen 4 byte'lık bir görünmez kolonu Clustered Index'imize ve dolayısıyla tüm Non-Clustred Index'imize ekleyecektir.
Tablomuza az da olsa yeni eklenen kayıtlar sıralı olmadığından, fiziksel olarak diğer kayıtların aralarına girmek durumunda kalacak, kayıt kaydırma işlemleri başlayacak ve tablomuzda fragmantation oluşacaktır, bunu engellemek için düzenli bakım yapmamız ve fill factor değerini yüksek tutmamız gerekecektir.

Peki sırayla artan bir Müşteri No bizim Clustered Index Key'imiz olsaydı ve Soyad İsim alanına Non-Clustered bir index tanımlamış olsaydık olmaz mıydı?

Olurdu, bu şekilde uniquifier alanına gerek kalmazdı ve Clustered Index fragmantation derdimizde ortadan kalkardı, müşteri no ile gelen sorgular çok hızlı olurdu ve sıralı çok sayıda müşteri numarasını çok hızlı bir şekilde sorgulayabilir, gruplayabilir ve sıralayabilirdir.

Kısacası bunlar bizim tercihlerimiz ile ilgili. Bizim tercihlerimizi de sistemin kullanılma şekli, ve sistemden beklelenlerin öncelikleri belirlemeli. Burada ilk başta yapılan tasarım daimi olacak diye bir kural yok, yaptığımız hatanın farkına varınca gerekli değişiklikleri yapma şansımız olabilir, sonuçta bazı durumlar denenmeden bilinemiyor.

Sürekli çalışan sistemlerdeki tablolara dokunmak son derece zor veya bazen imkansız olabiliyor, bu tür durumlarda SQL 2005 ile gelen ve çeşitli durumlarda kullanılabilen ONLINE ve MAXDOP parametrelerini araştırmakta fayda var.

Temel Index İşlemleri

Index'ler üzerinde ne yapabiliz konusuna başlamadan önce, clustered ve non-clustered index'ler nelerdir diyorsanız sizi buraya davet ediyoruz, bu yazıda anlatılan Physical RID ve Logical RID mantığı bir kere oturduktan sonra büyük ihtimalle SQL'in nasıl davranacağını tahmin edebilir hale geliyoruz. Tek fark versiyonların çalışma şekillerinin değişebiliyor olması.

Sonradan Clustered Index Oluşturmak 
Bir tabloda clustered index sonradan oluşturulduğunda, bu değişiklik tüm kayıtların fiziksel oladak disk üzerinde sıralamalarının değişmesi ve tüm non-clustered indexlerin de eskiden kullandıkları physical RID alanlarının clustered key Logical RID bilgisi ile güncellenmesi manasına gelir. Bu durumda tüm non-clustered index'ler Rebuild edilir(baştan oluşturulur).

Clustered Index'imiz Var Ama Siliyoruz 
Bir tabloda varolan bir clustered index'i sildiğimizde, yani heap'e geçerken; üstteki işlemin tersine, non-clustered indexlerde bulunan Logical RID bilgileri Physical RID bilgileri ile değiştirilir. Bu durumda da tüm non-clustered index'ler Rebuild edilir.

Unique Clustered Index ve Non-Unique Clustered index farkları için buradaki yazımı okuyabilirsiniz.

Index Rebuild çalışmalarında öncelikle Clustered Index Rebuild edilmeli, sonrasında Non-Clustered Index'ler rebuild edilmelidir. Clustered Index ilk oluşturulurken, işlem tamamen bitene kadar disk üzerindeki eski veriler saklı kalır. Büyük tablolarda yeterli disk alanının olduğu kontrol edildikten sonra, Clustered Index oluşturulmalı veya değiştirilmelidir.

Clustered Index Rebuild İşlemi
Varolan bir Unique Clustered Index'imizi Rebuild ettiğimizde, disk üzerinde clustered key bilgisine göre, Clustered Index sıralamasına uygun olarak düzenlenir. Burada Logical RID değişmediği için Non-Clustered Index'ler bu işlemden etkilenmezler.

Non-Unique Clustered Index Rebuild edildiğinde ise, SQL 2000 buradaki uniquifier alanını günceller ve bu sebepten tüm Non-Clustered Index'ler Rebuild edilir. Ancak bilğim kadarıyla SQL 2005 ve SQL 2008 aynı uniquifier'ı kullanmaya devam ettiği için Non-Clustered Index'ler bu işlemden etkilenmezler.

Clustered Index Değişimi
Clustered Index için kullanılan kolon veya kolonlar değiştirildiğinde, -barındırdığı veri değil, clustered key olarak seçilen kolonların yerine farklı kolonlar seçildiğinde, eklendiğinde, çıkarıldığında- Clustered Index baştan oluşturulur, bu disk üzerindeki sıralamanın değişmesi ve dolayısıyla Non-Clustered Index'lerde bulunan Logical ID'lerin artık geçersiz olması demek, çünkü barındırdıkları Logical ID eski clustered key için gerçerli olarak kalacak. Bu sebepten Non-Clustered Index'ler Rebuild edilir.

SQL 2005 ile gelen table partition işlemleri, sadece clustered key alanının disk yerini etikelediği için bu durumlarda Non-Clustered Index'ler tekrar Rebuild edilmezler. Kullandıkları Logical RID partition sonrasında da geçerli olacaktır. Aynı durum filegroup değiştirilmesi içinde geçerlidir.

Clustered ve Non-Clustered Index

Index'ler konusuna gelirsek iki index örneğimiz var, clustered index bir anlamda tablonun kendisi demek, non-clustered index ise, kabaca belirli alanların direk clustered key ile eşleştirilerek tüm tablonun taranmasına engel olmak demek diyebiliriz. Yazımın sonunda basit bir örnek vereceğim.

Detaylara girmeden önce sıkça kullanılan bir kısaltma RID : Record ID anlamına gelir. İki türlü karşımıza çıkar, Physical ve Logical. Bildiğim kadarıyla Physical RID disk bilgisini içerirken, Logical RID clustered index bilgisini içerir.

Heap : Bir tabloda eğer clustered index yoksa bu tabloya heap diyebiliriz. Bu tablolarda bir kaydı bulmanın tek yolu full table scan yapmaktır. Pek rastlanmaz ama, tablomuzda clustered index yok ama non-clustered index var diye düşünürsek, SQL her non-clustered index kullanımında table scan yapmaktansa, non-clustered index'in heap üzerindeki Physical RID ile eşleştirir. Bu non-clustered index key için kullanılacak olan satır bu disk alanındadır şeklinde bir işaretleme olarak düşünebiliriz bu durumu.

Clustered Index: Disk üzerinde fiziksel olarak tablodaki kayıtların sıralanmasını sağlayan index'tir, aslında tablonun kendisidir de diyebiliriz. Buradaki sıralamayı sağlayan alana cluster key diyebiliriz. Cluster key olabiliyorsa unique olmalıdır. Ancak bazı durumlar için bu şekilde bir yapı kullanılamayabilir. Non-Unique Clustered Index Kullanımı için buradaki yazımı okuyabilirsiniz. Clustered index ve non-clustered index barındıran tablolarda, non-clustered index'ler clustered index'lerin Logical RID bilgisini tutarlar. Heap durumunda bu Physical RID idi. Bu non-clustered index key için kullanılacak olan satır bu Logical RID ile eşleşen clustered index alanındadır diye bunu düşünebiliriz. Clustered index zaten bulunduğu disk bilgisine sahiptir ve bu şekilde uygun disk alanına ulaşılır. Clustered index eğer araya kayıt alırsa disk üzerinde belirli kaydırma işlemleri yapmak durumunda kalacaktır, bu kaydırma işlemlerini, clustered index disk bilgilerinin güncellenmesi olarak düşünebiliriz. Non-clustered index'ler disk alanı (Physical RID) ile değil, clustered index'in Logical RID bilgisi ile eşleştiği için, bu kaydırmalarda non-clustered index'ler etkilenmezler.
Heap kullanımında ise emin olmamakla beraber sanırım bütün yeni kayıtlar disk üzerindeki son tablo kaydının sonrasına aktarılır. Zaten kayıtlar için belirli bir sıra gerekmediğinden yeni kayıt, son kayıt ardına yazılır ve bir kaydırma işlemi olmaz. Yani ben yapsam böyle yapardım :)

Heap ve Clustered Index arasındaki bir fark da Read performansındadır. Clustered Index üzerinde kayıtlar sırayla bulunduğundan dolayı, Heap'e kıyasla 8 kat daha hızlı veri okunabilir. Bunu güzel ama eski bir yazıdan okumuştum, yeni SQL versiyonları ile bu değerler değişmiş olabilir. Kaynak.

Basit bir örnek vermeyi deneyeyim;
Can diye bir müşteri olsun ve bu müşterinin 1000 tane alış işlemi olsun, oldukça iyi bir müşteri :)
Alım işlemleri tablomuzda 20.000 işlem olsun, ve işlem numaramız bizim clustered key alanımız olsun. Örneğimizi basit tutmak için müşterilerin de isimle bu tabloda tutulduğunu varsayalım. 

Bu durumda 5 numaralı alım işlemini bulmak istediğimizde clustered index scan yaparak SQL olabilecek en hızlı şekilde bize kayıt bilgilerini dönecektir.

Can'ın işlemlerini bulmak istediğimizde, sistem tüm tabloyu tarayacak ve 20.000 kaydı inceledikten sonra bulduğu kayıtları işlem numarası ile eşleştirerek yukarıdaki senaryodaki gibi devam edecektir. 

Peki biz Can'ın isim bilgisinin olduğu alana bir non-clustered index eklersek ne olacak? Gene 20.000 kaydı incelemeyecek miyiz?

Cevap hayır, sebebi ise non-clustered index alanlarının sıralı olarak saklanıyor olması. Kabaca düşünürsek ismi C ile başlamayan kayıtların SQL'in incelemesine gerek kalmayacaktır. Gene kabaca düşünürsek; tabloyu tararken D harfine geldiğimizi varsaysak devam etmemize gerek kalmacayaktır.

Non-Unique Clustered Index Kullanımı

SQL server işleyişinde önerilen, clustered index'in olabiliyorsa unique olmasıdır. Peki eğer biz unique olmayan bir clustered index tanımlamayı seçersek neler olur. Bu durumda SQL tabloya yeni bir kolon daha ekler, bu kolona uniquifier denir ve 4 byte yer kaplar. Bu kolon tablodaki her satırda vardır ve clustered index'in aynı olabildiği durumlar için belirleyici görevi görür. Satırın belirleyici olmasında rol aldığı için, diğer non-clustered indexlerde de bu kolon vardır. Bu sebeplerden non-unique clustered index kullanımlarında ek bir disk alanı kullanımı olacağı unutulmamalıdır. Bu ek kolonun performansa da az da olsa etkisi olacaktır.

Non-Unique Clustered Index'leri belirli durumlarda kullanmanız gerekebilir, örneğin veritabanına bakım yapmama konusunda inatçı bir müşteri ile çalışıyorsanız :)

Tablolar için fragmantation çok önemlidir, ve önerilen durum clustered index'in artan değerler alması ve dolayısıyla yeni kaydın her zaman tablonun en alt kısmında yer almasıdır. Genellikle sorun yaşanan veritabanları eski ve bir çok yeni geliştirme geçirmiş, ilk tasarlanırken akla gelmeyen bir çok işi de yapar hale gelmiş veritabanlarıdır. Bu tür durumlarda performans çalışmalarında tablolarda kritik değişiklikler yapmak sistemin işleyişini tümden etkileyebilir. Örneğin, tüm sistemde unique'lik bir GUID ile sağlanıyorsa ve bu GUID sizin işlem tablolarınızda bir clustered index ise, aynı zamanda önceki örnekteki gibi bakım yapmayan bir müşteri ile karşı karşıyasanız, kısaca durum şudur: tabloda artık düzgün çalışan bir clustered index kalmamış demektir.

Bu tür durumlarda, GUID'i clustered index olmaktan çıkartarak unique index olarak eklemek uygun olabilir, clustered index olarak bir artan ID veya işlem zamanı kullanılabilir. Ancak farklı kayıtlar için, işlem zamanı -zor da olsa- sistemde aynı olabilir, aynı olmasa bile  SQL aynı olabilme ihtimaline karşı yukarıdaki uniquifier kolonunu tabloya eklemek zorunda kalacaktır.

Sonuç olarak her neredeyse tabloda bir clustered index olmalıdır ancak clustered index her zaman unique olmalıdır diye bir gereklilik yok, imkan varsa unique olması sisteme büyük performans sağlayacaktır. Bu bilgileri düşünerek non-unique clustered index kullanılabilir, veya kullanılmayabilir :)

SQL 2008 ve Date, Time Veri Tipleri

Time : SQL 2008 ile aramıza katılan bir veri tipi. Sadece saati veya sadece tarihi tutmak istediğimizde char kullanmak yerine bu tipleri kullanabilyoruz. Ancak Time 3-5 byte yer kaplıyor, eski dostumuz smalldatetime 4 byte, yani Time tipini kullanırken amacımız yerden kazanmak değil. 100 nanosaniyeye kadar hassas bir saat bilgisi tutabiliyor. Bu bilgiyi işletim sisteminden sorguluyormuş, ne kadar hassas bilgi alacağı biraz da işletim sistemine bağlı. Ancak bu kadar hassas saat bilgisi kolay kolay ihtiyaç duyulacak bir bilgi gibi gelmedi bana, belki bilimsel çalışmalarda önemli olabilir.

Date : Bu veri tipide SQL 2008 ile geldi. Gün bazında tarih bilgisini tutuyor ve 3 byte yer kaplıyor. char(8) olarak tutulan tarih bilgilerinin aslında bu şekilde tutulması hem yer kazanımı açısından hemde SQL'in tarih kıyaslamalarının hızlanması açısından faydalı olacaktır gibi geldi bana, ama sonuçta SQL bu, en iyisini bulmak için bolca denemek gerekiyor :)

Eski veritiplerinin yukarıdaki yeni tiplere nasıl çevrileceği konusunuda araştırmak gerekli.

MSDN sayfasına buradan ulaşabilirsiniz.