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
Hiç yorum yok:
Yorum Gönder