Kitap incelemesi: SQL Server 2014 Query Tuning & Optimization, yazar Benjamin Nevarez

Selam millet,

Aslında amazon.com'dan geçen sene sonunda satın aldığım bu kitabı, nihayet bugün bitirebildim.

Kitapta Benjamin, temel olarak şu konulardan bahsetmiş:
- Sorgu iyileştirmeye giriş,
- Sorgularla ilgili sorunların çözümü,
- Sorgu iyileştirici (Query Optimizer),
- Sorgu operatörleri,
- Index'ler,
- İstatistikler,
- In-Memory OLTP,
- Plan Cache,
- Veri ambarları,
- Sorgu işlemcisinin (Query Processor) sınırlamaları ve Hint'ler.

Kusura bakmayın biraz İngilizce/Türkçe kırması, tarzanca gibi oldu; ama bazı terimleri de çevirmeye çalışmak zor ve anlaşılmaz olabiliyor. Bununla birlikte bu terimlere zaten aşina değilseniz, bu konularla da ilgilenmiyorsunuzdur diye tahmin ediyorum, o yüzden bu noktaya çok da takılmayacağım.

Kitabın ilk bölümünde nasıl daha iyi "Execution Plan" üretilebilecek kodlar yazılabileceğinden, "Query Processor"un iç dinamiklerinden, performanslı çalışmayan sorgular için hangi SQL Server Tool'larının kullanılabileceğinden, "Execution Plan"ların nasıl okunabileceğinden bahsediliyor.

İkinci bölümde, sorgularımızın IO ve CPU gibi sistem kaynaklarını nasıl kullandığını öğrenmemize yardımcı olacak bazı teknikler gösteriliyor. Temel olarak "Extended Event"lerden ve "Data Collector"den, masraflı sorguları takip etmemize yardımcı olacak bazı "DMV" ve "DMF"lerden ve "SQL Trace"ten bahsediliyor.

Üçüncü bölümde Query Optimizer'ın nasıl çalıştığından, SQL Server'da çalıştırılmak üzere girdiğimiz bir SQL cümleciğinden nasıl bir "Execution Plan" oluştuğuna kadar, "parsing", "binding", "simplification", "trivial plan" ve "full optimization" safhalarından nasıl geçtiği ve "Query Optimizer" mimarisinin önemli parçaları anlatılıyor.

Dördüncü bölümde "Execution Plan"lardaki sık kullanılan operatörlerden bahsediliyor. Algoritmalar, karşılaştırmalı masraflar ve sık kullanılan operatörlerin hangi durumlarda ve neden kullanıldığından bahsediliyor. "Execution Engine" tarafından sıralama (Sorting) ve Hashing kavramlarının nasıl kullanıldığına, verileri birleştirme (Join) ve kümeleme (Aggregation) algoritmalarına da değinilmiş.

Beşinci bölüm Index'lere ayrılmış. Temel olarak B-Tree Index'lerinden bahsedilmiş, bu Index'lerin ne zaman ve nasıl kullanıldıkları ve Index'lerin parçalanması (fragmentation) konusu anlatışmış. Bunların yanında "Database Tuning Advisor" ve Index'lerle ilgili DMV'lerden de bahsedilmiş.

Altıncı bölümde İstatistikler'in SQL Server tarafından nasıl faydalanıldığı, istatistiklerin elementleri tanıtılıyor ve açıklanıyor. Ayrıca burada da Beşinci bölümdeki gibi İstatistiklerin bakımından bahsedilmiş.

Yedinci bölüm SQL Server 2014 ile birlikte gelen In-Memory OLTP (Hekaton) özelliğinin tanıtımına ayrılmış. Hatırlarsanız Benjamin Nevarez, geçen seneki Microsoft Türkiye ofisinde sevgili arkadaşım Yiğit Aktan'ın düzenlediği organizasyonla SQLSaturday'e de katılmıştı ve SQLSaturday öncesindeki Cuma günü de bir Precon oturumu yapmıştı. Bu Precon'da da, ertesi günkü SQLSaturday etkinliğinde de yine Hekaton'dan bahsetmişti.

Sekizinci bölüm "Plan Cache"e ayrılmış. Evet, her konu çok önemli, fakat ben Plan Cache'in özellikle kariyerinde belli bir noktaya gelmiş kişiler tarafından çok iyi bilinmesi gerektiğini düşünüyorum. SQL Server 2005 ile gelen DMV'ler sayesinde Plan Cache'teki birçok bilgiye çok daha ayrıntılı ulaşabiliyoruz. Özellikle performans sorunlarının çözümünde, Plan Cache'teki verilerden bu DMV'lerle faydalanabilmek çok önemli. Benjamin de bu bölümde sorgularımızın planlarının nasıl Plan Cache'te tutunup birden fazla kez kullanılabileceğinden ve diğer ilgili ayrıntılardan bahsetmiş.

Dokuzuncu çok kısa bir şekilde veriambarlarına değinmiş. Aslında burada temel olarak anlattığı şey OLTP ile OLAP sistemler arasındaki farkı vurgulamak ve SQL Server 2012 ile birlikte gelen Columnstore Index'lere değinmek olmuş. Ufaktan Fact tablolara, Star Join'lere, Batch işlem modundan da bahsetmiş.

Onuncu ve son bölümde ise SQL Server'daki bazı sınırlamalara dikkat çekmiş. Örneğin SQL Server'ın ender de olsa iyi Execution Plan'lar oluşturamadığından ve bu durumlarda neler yapılabileceğinden bahsedilmiş.

Genel olarak kitabı beğendim ben, alıp okumanızı tavsiye ederim.

Ekrem Önsoy

Implicit Conversion neden "kaka"dır?

Selam arkadaşlar,

Şimdiye kadar bu ve buna benzer çok sorunu çözdüm, ama şimdi bir vakit aralığı buldum ve istatistikleriyle birlikte güzel bir örnek olacağını düşündüğüm için bunu özellikle sizlerle paylaşmak istedim. Lütfen bu örneği küçük bir ders gibi görün ve dikkatlice inceleyin. T-SQL'ı işinde kullanıyor olanlarınız için, kariyerinizin geri kalanında da çok işinize yarayacaktır.

Öncelikle "Implicit Conversion" nedir, kısaca önce bunu tanımlayayım. Diyelim ki aşağıdaki gibi bir tablonuz var:

CREATE TABLE dbo.Gonderilenler(

GonderiNo BIGINT,

…

)

CREATE TABLE dbo.Gonderilecekler(

GonderiNo VARCHAR(50),

…

)

Ve aşağıdaki gibi bir Stored Procedure (SP)'ünüz var:

CREATE PROCEDURE dbo.GonderiSorgula

@GonderiNo DECIMAL(11,0)

AS

SET NOCOUNT ON;

SELECT … FROM dbo.Gonderilenler WHERE GonderiNo = @GonderiNo;

…

SELECT … FROM dbo.Gonderilecekler WHERE GonderiNo = @GonderiNo;

Burada dikkatinizi çekmek istediğim şey, Gonderilenler ve Gonderilecekler tablolarının ikisinde de GonderiNo adında bir alanın bulunması, fakat bu iki alan için farklı veritiplerinin kullanılması. Maalesef birçok ortamda, aynı maksatla kullanılan ve farklı tablolarda bulunan alanlar için yazılımcıların farklı veritiplerini kullandığını görebiliyoruz. Yazılımcılar bu konuda gerçekten çok dikkatsiz olabiliyorlar. Örneğin bir ortamımda, aynı amaçla kullanılan alan için, farklı onlarca tabloda, farklı veritipleri kullanıldığını gördüm. Bu sorunu düzeltmek de ayrı bir sorun, bundan da belki başka bir yazımda bahsederim.

Nerede kalmıştık efendim? Evet, Implicit Conversion nedir, nasıl oluştur? Eğer yukarıdaki dbo.GonderiSorgula isimli SP'yi çalıştırırsanız, @GonderiNo değişkeninin veritipi ile dbo.Gonderilecekler tablosundaki GonderiNo alanının veritipleri uyuşmayacağı için nur gibi Implicit Conversion sorununuz olacaktır. SQL Server kendi içerisinde Numeric veritipi olan DECIMAL'ı, VARCHAR'a çevirecektir (Implicit Conversion tam olarak budur) ve bu nedenle de dbo.Gonderilecekler.GonderiNo alanı için güzel bir Index de olsa bu Index'ten faydalanılamayacak ve Index Scan işlemi yapılacaktır. Bu da, tüm tablonun taranması anlamına gelmektedir ve tablo ne kadar büyükse, o kadar büyük bir sorununuz var demektir. Implicit Conversion ile sadece IO değil, CPU sorunu da yaşarsınız. Örnekleri ve delilleriyle birlikte hepsini göstereceğim.

Bugün karşılaştığım sorunlu SP'nin bahsini ettiğim sorunlu kısmının önceki ve sonraki Execution Plan'larını da aşağıda görebilirsiniz. 

Aşağıdaki ekran görüntüsündeki INSERT operatörünün üstündeki üçgen içerisindeki ünlem işareti de Execution Planları incelerken her zaman dikkatinizi çekmeli. Eğer bu operatörün üstünde gelir ve beklerseniz bir Tooltip çıkacaktır (veya F4->Properties) ve orada size Implicit Conversion hakkında bir uyarı gösterilecektir. Tabii ki aşağıdaki senaryo için bu böyle. Başka senaryolarda ve operatörler için bu üçgen içerisindeki ünlem işareti "Spill to tempdb" gibi anlamlara da gelebilir.

SP'nin iyileştirmeden önceki Execution Plan'ı.

Aşağıda da, SP'nin yaptığım iyileştirme çalışmasından sonraki Execution Plan'ını görebilirsiniz.

SP'nin iyileştirmeden sonraki Execution Plan'ı.

Peki bu uyuşmazlığın, iyileştirme öncesindeki ve sonrasındaki etkisi nasıl? Lütfen aşağıda paylaştığım grafiğe bir gözatın. 

Bu Script kendi yazdığım bir Script. Bu Script sayesinde Cache'lenebilen sorguların ve nesnelerin eski istatistiklerine ve Execution Plan'larına ulaşabiliyorum. 

Grafiğin en üst kısmında, kırmızı ile işaretlediğim yerde SP'nin şu anki, yani iyileştirmeden sonraki masrafını göreceksiniz; alttaki kayıtlar ise, SP'nin iyileştirme öncesinde nasıl çalıştığına dair istatistikler. Gördüğünüz gibi kaynak kullanım masraflarındaki iyileşmeler binlerce kat.

Peki ne yaptımda Execution Plan Index Scan'i terk edip Index Seek yapmaya başladı? INSERT operatörünün üstündeki üçgen içerisindeki ünlem nereye gitti?

Benimki gibi gerçek hayat senaryolarında, her zaman her şey mükemmel olmuyor. Zaten öyle olsa, Implicit Conversion sorunu da yaşamıyor olurdum. Gerçek senaryomda, SP içerisinde sorgulanan tablonun ilgili alanının veritipini maalesef değiştiremiyorduk. Yani özellikle bir tablodaki ilgili alanın veritipi, diğer tablolarınkinden farklı kalmak durumundaydı. Nedenleri bana kalsın. Fakat benim her şeye rağmen bir çözüm üretmem gerekiyordu, çünkü ortada bir sorun vardı. Ben de en azından bu tablodaki ilgili alanın veritipini değiştirinceye kadar aşağıdaki çözümü ürettim. SP içerisine aşağıdaki gibi bir değişken tanımladım:

DECLARE @GonderiNo_str VARCHAR(50) = CAST(@GonderiNo AS VARCHAR(50));

Ve yukarıdaki senaryoya göre anlatacak olursak SP'min kodunu aşağıdaki gibi değiştirdim:

CREATE PROCEDURE dbo.GonderiSorgula

@GonderiNo DECIMAL(11,0)

AS

SET NOCOUNT ON;

DECLARE @GonderiNo_str VARCHAR(50) = CAST(@GonderiNo AS VARCHAR(50));

SELECT … FROM dbo.Gonderilenler WHERE GonderiNo = @GonderiNo;

…

SELECT … FROM dbo.Gonderilecekler WHERE GonderiNo = @GonderiNo_str;

Bu değişiklikten sonra bu SP'nin Execution Plan'ı yukarıda gösterdiğim gibi değişti.

SP'nin içerisindeki asıl @GonderiNo değişkeninin veritipi dbo.Gonderilenler tablosu için doğru olduğundan, bu için bu değişkeni kullanmaya devam ettim, ama dbo.email_gonderilenler tablosu için yukarıda tanımladığım yeni @GonderiNo_str veritipini kullandım. Böylece dbo.Gonderilecekler tablosu sorgulanırken Implicit Conversion yapılmamış ve varolan Index'ten en iyi şekilde faydalanılmış oldu. Evet, tek yaptığım buydu ve binlerce kat iyileşme elde ettim.

İyi kod yazmalar!

Ekrem Önsoy

Hata: Cannot enable compression for object...

HATA:
Msg 2786, Level 16, State 1, Procedure sp_estimate_data_compression_savings, Line 20
The data type of substitution parameter 3 does not match the expected type of the format specification.
Msg 7738, Level 16, State 2, Line 1
Cannot enable compression for object '#sample_tableDBA05385A6FF40F888204D05C7D56D2B_______________________________________________________________________000000063644'. Only SQL Server Enterprise Edition supports compression.

AÇIKLAMA:

Developer, Evaluation veya Enterprise Edition olmayan bir SQL Server Instance'ına bağlanıp, bir tablonuzu ROW veya PAGE veri sıkıştırma yöntemiyle sıkıştırırsanız ne kadar kazanım elde edeceğinizi hesaplamak için aşağıdaki komutu çalıştırırsanız bu hata ile karşılaşırsınız.

EXEC sp_estimate_data_compression_savings 
      @schema_name =  'schema_adi'  
   ,  @object_name =  'tablo_adi' 
   , @index_id =  NULL
   , @partition_number = NULL

   , @data_compression =  'PAGE' ;

ÇÖZÜM:
Örneğin eğer Express veya Standard Edition kullanıyorsanız bu hata ile karşılaşırsınız. Çünkü veri sıkıştırma özelliği sadece yukarıda, açıklama bölümünde belirttiğim Edition'lar için kullanılabilirdir. Maalesef Microsoft ne kazanım elde edebileceğimizi görmemiz için, hesaplama için kullanılabilecek bu SP'yi bile kullanmamıza izin vermiyor. Mantıklı olanı önce bu özellik ile ne elde edebileceğimizi görüp, ona göre Edition yükseltme işlemi yapıp yapmamaya karar vermek gibi görünüyor, yani bence de öyle, ama maalesef Microsoft böyle uygun görmemiş. Bu nedenle veri sıkıştırma ile nasıl bir kazanım elde edeceğinizi görmek için Evaluation, Developer veya Enterprise Edition kullanmanız gerekiyor.

Ekrem Önsoy

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

HATA:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

AÇIKLAMA:
Uzaktaki bir sunucuya karşı, bir istemciden sorgu çalıştırdığınızda, sorgu çalışıyorken sunucu ile aranızdaki bağlantı koptuğunda böyle bir hata ile karşılaşırsınız.

Örneğin uzaktaki sunucuya VPN aracılığı ile bağlantı kurdunuz diyelim, internet bağlantınız gitti, o zaman bu hatayı alırsınız.

ÇÖZÜM:
İnternet bağlantınızın ve örneğin VPN bağlantınızın sağlıklı kurulu olduğundan emin olun.