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

Inline TVF mı yoksa Multi Statement TVF mı? Mümkünse Inline lütfen!

Merhaba!

Size kısaca Inline (ITVF) ve Multi Statement Table Valued Function (MSTVF)'ların kullanımı ve farkından bahsetmek istiyorum.

Bugün bir sunucumuzda performans kontrolü yaparken bir sorgunun çok masraf oluşturduğunu gördüm. Sorguyu incelerken içerisinde bir Table Valued Function (TVF)'ın kullanıldığını fark ettim. Bu TVF'ı kontrol ederken, sadece bir sorgudan oluşan bir işlem olmasına rağmen MSTVF olarak aşağıdaki gibi yazıldığını gördüm (kritik bilgileri değiştirdim)

MSTVF

ALTER FUNCTION [dbo].[fTVFsorgu] (@degisken1 varchar(50))

RETURNS @TVF TABLE

(

Adres varchar(250)

,Belediye varchar(50)

,Mahalle varchar(50)

,Sokak varchar(50)

,islem_kodu int

)

AS

BEGIN

   INSERT @TVF

Select 

Adres

,Belediye

,Mahalle

,Sokak

,islem_kodu

FROM dbo.tablo_adi

WHERE AdSoyad=@degisken1

RETURN

END

Görüldüğü üzere bu TVF sadece bir sorgudan oluşuyor. 

Bazılarınızın "Peki bu tür sorguları ITVF olarak yazmakta fayda var? Fark nedir ki?" dediğinizi tahmin edebiliyorum. Şöyle ki, MSTVF'lardaki Table değişkenlerinin içinde kaç tane kayıt olursa olsun, SQL Server MSTVF'lardaki Table değişkenlerinin içinde sadece 1 tane kayıt olduğunu varsayar. MSTVF'ler SQL Server Query Optimizer için de bir karakutudur. Hal böyle olunca, uygun Index'lerden ve Statistic'lerden faydalanılamaz. Yani sorgu için Query Optimizer gerekli iyileştirme işlemlerini yapamaz.

Eğer TVF'ınınız içerisinde özellikle sadece bir Statement varsa çoğu zaman bunu ITVF'a çevirmenizde büyük fayda var. Eğer birden fazla Statement varsa da o zaman mümkünse bu Statement'ları birleştirip bir tane Statement şeklinde düzenleyip bu şekilde ITVF kullanmanız ve bu şekilde test etmeniz iyi olur.

ITVF

CREATE FUNCTION [dbo].[fTVFsorgu] (@degisken1 varchar(50))

RETURNS TABLE

AS

RETURN
Select 

Adres

,Belediye

,Mahalle

,Sokak

,islem_kodu

FROM dbo.tablo_adi

WHERE AdSoyad=@degisken1

Bu arada, eğer MSTVF ile ITVF'ın Execution Plan'larını doğrudan SQL Server Management Studio ile karşılaştıracak olursanız sizi yanıltabilecek bir sonuç ile karşılaşmanız kaçınılmaz. Çünkü burada MSTVF ile ilgili tablo üstünde yapılan IO işlemi dahil edilmiyor, fakat ITVF ile yapılan IO işlemleri dahil ediliyor. İlgili ekran görüntüsünü aşağıda görebilirsiniz:

Gördüğünüz gibi ITVF kullandığımızda artık bir karakutuyla (MSTVF) uğraşmamış oluyoruz ve tüm planı görebiliyoruz. Bu sayede en çok masraf oluşturan kalemleri de belirleyip gerekli aksiyonları alabiliyoruz. Bu örnekte ben Index'i Covered Index yapmadan önce aşağıdaki ITVF ilgili Index'teki alan için SEEK yaparken, diğer alanlar için Lookup yapıyordu ve bu da büyük bir IO masrafı oluşturuyordu. Fakat Index'i Covered Index yaptıktan sonra artık tamamen SEEK yapmaya başladı ve Query Optimizer da ITVF'daki sorguyu işleyebildiği için hemen bu Index'ten faydalandı ve performans çok arttı. Bu iyileşmenin sonucu için aşağıdaki ekran görüntüsüne bakabilirsiniz. 

Hem Index'in Covered Index'e çevrilmesiyle, hem de bu Function'ın MSTVF'dan ITVF'a çevrilerek Query Optimizer'ın bu sorguyu değerlendirip bu Index'ten faydalanılmasını sağlanmasıyla nasıl bir iyileşme yaşandığını görüyorsunuz. Uygun Index olmasına rağmen MSTVF bu Index'ten faydalanamıyor.

Kolay gelsin,

Ekrem Önsoy

SQL Server 2014 (CTP2)'de Öne Çıkan Yeniliklere Özet Bakış - 1

Selam arkadaşlar,

Sizlerle birkaç yazıdan oluşan bir seri ile SQL Server 2014 Community Tehcnology Preview 2 (CTP2) sürümünde de bulunan ve şimdiye kadar açıklanan öne çıkmış bazı Database Engine özelliklerini paylaşmak istiyorum.

Bu ilk yazıda In-Memory OLTP'den bahsedeceğim.

"Hekaton" kod adıyla ortaya çıkan ve bazılarınca böyle kalması da istenen; fakat Microsoft tarafından "In-Memory OLTP" olarak adlandırılan, bildiğimiz Database Engine'e gömülmüş olan alternatif bir Engine dersek yanlış olmaz sanırım. Bildiğimiz klasik SQL Server Database Engine'ine tam olarak entegre edilmiştir. Şu anda halihazırda kullandığımız tablo ve SP yapılarına kıyasla birçooook limitasyonu bulunsa da, zamanla birçoğunun aşılacağına inanıyorum.

Bu yenilikle gelen şeyler temel olarak bir tablonun Memory Optimized Table (MOT) olarak oluşturulabilmesi. Bizim klasik tabloların da adları artık Disk Based Tables (DBT) oluverdi. MOT'lar temel olarak SQL Server servisi başlar başlamaz RAM'e yükleniyorlar. Bildiğiniz gibi DBT'lar, o tablolar üstünde işlem yapıldıkça yavaş yavaş yüklenirlerdi Buffer Pool'a, MOT'lar ile daha servis açılışında yükleniyorlar. Peki ne yararı var? diye sorarsanız hemen şöyle diyebilirim: MOT'lar için artık Blocking sorunu yaşamayacaksınız! Latch'miş, Lock'muş, artık yokmuş. Ayrıca MOT'lardaki verileri Durable olmak ve olmamak üzere saklayabiliyorsunuz. Eğer bir MOT'taki verileri Durable olmayacak şekilde saklarsanız o zaman o veriler ile ilgili işlemler sadece hafızada yapılıyor olacak ve diske işlenmeyecek. Veriler diske işlenmeyeceği için de hiç IO sorununuz olmayacak.


Peki bu teknolojiden en iyi hangi senaryolarda faydalanabilirsiniz? Birçok senaryo olabilir, fakat özellikle alışveriş sepeti, doldur boşalt, oturum yönetimi gibi senaryolar ilk aklıma gelenler; tabii ki çok daha yaratıcı olunabilir!

Tabii ki her tablo öyle kolayca MOT'a çevrilemiyor. Bu konuda bizlere kolaylık sağlayan AMR (http://msdn.microsoft.com/en-us/library/dn205133(v=sql.120).aspx) isimli bir Tool var, bu kullanılabilir. Hangi tabloların MOT'a, hangi SP'lerin Natively Compiled SP'ye çevrilebileceği konusunda fikir veriyor.

In-Memory OLTP ile gelen bir başka yenilik ise hemen bir üst pragrafta da bahsettiğim Natively Compiled SP'ler (NCSP). Bizim bildiğimiz klasik SP'lerin NCSP'ye çevrilmesiyle %70 oranında iyileştirmeler yaşandığını gördüm, Microsoft çok daha fazla iyileşme yaşanabileceğini söylüyor. Bu iyileşme de temel olarak hem MOT'ların NCSP ile daha entegre çalışabilmesinden hem de NCSP'lerin doğrudan C koduna çevrilip (bir SP NCSP'ye çevrildiğinde bu SP için NTFS'te bir *.dll dosyası oluşturuluyor) çok daha kestirme ve etkin bir şekilde çalıştırılıyor olmasından kaynaklanıyor.

Bu konuda şimdilik son olarak söylemek istediğim şey ise gerçekten çoook kısıtlama var! Kesinlikle bu teknolojiyi kullanmadan önce kısıtlamalarına bakmalısınız ve nasıl çalıştığını iyice anladığınızdan emin olmalısınız.

İpucu: Güç Yönetimi

Bugün Glen Berry'nin bir kursunu izlerken gördüm ve sizlerle de paylaşmak istedim. Glen Berry, özellikle SQL Server OLTP sistemleri için Windows Server düzeyindeki Control Panel'daki Power Options planlarının çok kritik olmasından bahsediyor. Varsayılan Power Options planı Balanced, fakat SQL Server OLTP sistemler için seçili olması gereken plan High Performance planıymış. Glen, bu değişiklikle işlemciye göre %15 - %25 arası performans iyileşmesi gördüğünü kaydediyor.

Ayrıca, eğer fiziksel sunucunuzun BIOS'una erişebiliyorsanız Glen oradaki Power Management ayarının da OS Control veya Disabled olarak işaretlenmesini tavsiye ediyor.

Muhakkak denemeye değer! Bu değişiklik hakkındaki tecrübelerinizi benimle de paylaşırsanız çok sevinirim.

Ekrem Önsoy

Hekaton

Merhaba,

Bazılarınız belki duydu, belki ilk defa görüyor; fakat Microsoft şu anda SQL Server için Hekaton adında bir teknoloji üstünde çalışıyor. Bu teknoloji ile ilgili tanıtımlar yapıyor ve bazı firmalarda deneme sürümlerini kullandırtıyor.

Bu teknoloji, bazılarınızın QlickView'den de bileceği gibi, verinin hafızada depolanmasını sağlamak ve erişimin diskten değil de, doğrudan RAM'de yapılmasını sağlamak.

Şimdi SQL Server konusunda tecrübeli olan bazı arkadaşlarımın aklına "SQL Server'da zaten Buffer Pool denen bir şey var ve veriler zaten olabildiğince hafızada tutuluyor ve işlemler bu şekilde gerçekleştiyiro, Hekaton'un farkı ne ki?" diye bilir; şöyle ki, Hekaton belirlediğimiz tabloların ve Stored Procedure'lerin ne kadar sık kullanıldıklarına bakmadan ve durağan olarak hafızada kalmasını sağlıyor. SQL Server'ın Buffer Pool'undaki veriler ise kullanım sıklıklarına göre ve hafızanın büyüklüğüne göre bazen diskten bazen hafızadan okunabiliyor. Ayrıca Hekaton ile hafızada depolanan veriler için hazırlanmış Stored Procedure'ler, hafızada kullanılmak üzere özel olarak derleniyor ve bu şekilde bu özellikten en iyi ve performanslı şekilde yararlanılması sağlanıyor.

İşin en güzel yanı ise, Stored Procedure'lerinizi yine bildiğiniz şekilde T-SQL dilinde yazıyorsunuz. Herhangi bir değişiklik yapmanız gerekmiyor. Aynı şekilde, donanım tarafında da herhangi bir değişiklik yapmanıza şart değil. Varolan donanımınızla da Hekaton'u kullanabileceksiniz.

Hekaton, bildiğimiz SQL Server ürünü ile entegre edilmiş şekilde, yan bir ürün olarak değil, sonraki versiyonlarda karşımıza çıkacak.

Ekrem Önsoy