SQL Server Plan Guide ve Kullanımı

Reading Time: 5 minutes

Plan Guide operasyonlarını genelde parameter sniffing yaşadığımız durumlarda problemlerimizi geçici olarak çözmesi için kullandığımız bir yöntemdir.

Parameter Sniffing , Parameter sniffing normal şartlar altında kendi başına bir sorun değildir, ama planlar anormal değerlere göre derlendiğinde parameter sniffing can yakabilir duruma gelmektedir. Her sorgu çalışmadan önce o sorgu için bir Query Plan (Execution Plan) oluşturulur ve bu Query Plan da sorgu çalıştırılırken kullanılan ilk parametre değerine göre oluşturulur.

Query Plan, Plan Cache’te konumlandıktan sonra (parameterize sorgular, stored procedure’ler ve diğer basit sorgular gibi) ilgili sorgular artık bu planı kullanarak çalışır. Eğer plan en uygun şekilde ve en genel talebe hitap edecek değerlerle oluşmadıysa, kötü bir performans ile çalışabilir ve bu da CPU’nun ve diğer donanım kaynaklarının verimsiz olarak kullanılmasına neden olabilir.


NOT : Plan Cache’i boşalttığınızda veya SQL Server servisini yeniden başlattığınızda (veya bazı SQL Server Instance’ı düzeyinde ayarı değiştirdikten sonra veya tekil bir planı Plan Cache’ten sildikten sonra) sorunlu çalıştırma planı gitmiş olur ve ilgili sorgu veya stored procedure ilk çalıştırışınızda yeni bir plan oluşturulur. Şansınıza yeni plan daha uygun değerler kullanılarak oluşturulabileceği için o anda “sorun çözüldü” sanabilirsiniz. Fakat Plan Cache’in bir dahaki sıfırlanışında veya herhangi başka bir nedenle ve zamanda bu plan yeniden kötü bir şekilde derlendiğinde yine bu sorunu yaşarsınız.

Bu gibi durumlardan kurtulmak için Plan Guide geçici çözüm için bir tanesi. Fakat unutmayın ki sp nizi değiştirdiğiniz anda Plan Guide ınız işlevselliğini yitirecek ve problem yaşamaya devam edeceksiniz.

İşte bu gibi durumlarda Plan Guide laırnızı da güncellemeniz gerekmektedir.
Bir Demo üzerinden konuyu ele alacak olursak ,
AdnetureWorks2016 veritabanı içerisinden personel bilgisi ve E-Mail lerini çekeceğimiz bir Stored Procedure oluşturalım.

Daha sonra sqltr_sp_Findemail isimli procedure ü Execution Plan ve İstatistik değerlerini ON ederek EXEC ediyoruz.

Execution Planımızı incelediğimizde Person tablosu üzerinde yer alan IX_2 isimli Index üzeirnden {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}27 lik Cost ile Index Scan işlemi yapıyor. Daha sonra EmailAddress tablosunda yer alan IX_1 isimli Index üzerinden de {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}48 lik Cost ile Index Scan işlemi yaparken Bu ikisini Merge Join işlemine tabi tutuyor.

Merge Join, birleştirilen iki tablonun birleştirme için kullanılan sütunları daha önceden sırlanmış ise SQL Server tarafından tercih edebilen bir operatördür.SQL Server Join işlemi ile karşılaştığında öncelikle birleştirme için kullanılacak koşuldaki değerlerin sıralı olup olmadığına bakar. Eğer sıralıysa diğer Join işlemlerinden daha performanslı çalışan Merge Join operatörü tercih edilir.Eğer sıralı değilse SQL Server ya veriyi sıralar ya da diğer Join operatörlerinden birini tercih eder. Bunu yaparken de sıralama maliyeti ile diğer operatörü kullanıldığında ortaya çıkacak maliyeti değerlendirip ona göre bir seçim yapar.

Eğer biz örneğimizde Merge Join değilde Loop Join kullanmak isteseydik peki Execution Planımız nasıl olacaktı ?

İlk etapta sp_ mizin aynısını farklı bir isimde oluşturup altına OPTION (LOOP JOIN) hintini ekliyerek EXEC ediyoruz ve nasıl bir plan çıkardığını kontrol ediyoruz.

sqltr_sp_Findemail2 EXEC edip Planı inceliyoruz.

Gördüğünüz üzere ilk plana göre daha yüksek bir performnsa sahip oldu. Merge Join yerine LOOP JOIN hintini vererek çalıştırdığımızda sp_ mizin performansı artış gösterdi. Person tablosunda Seek operasyonuna çevirirken kendini EmailAddress tablosunda ki Index Scan operasyonunun Cost unuda {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}4 e düşürdü.

Nested Loops operatörü,  iki işlemi birleştirerek tek bir sonuç elde etmek için kullanılan operatördür. Bizim sorgumuzun Execution planında EmailAddress tablosunda ki Index Scan işlemi ile  onun altındaki Person tablosu üzerinde yer alan Indeks Seek operatörünün çıktılarının birleşimi için kullanılmıştır.

LOOP JOIN hint imiz bizim işimizi gördü ise bir Plan Guide oluşturup bu sp nin hep o planla çalışmasını sağlayabiliriz artık.

Plun Guide Syntax :

Plan Guide ımzı teyid edelim. Bunun için sys.plan_guides view ını sorgulayacağız.

Plan Guide ımızı oluşturduk. Aynı zamanda bir Xevents Açıp oradan datakip edelim Plan Guide larımızı.

Daha sonra Xevent imizi START edelim.

Daha sonra plan guide oluşturduğumuz sp_ mizi çalıştırıp Execution Planını inceliyoruz.


Execution Planımız gördüğünüz üzere sağlıklı olan şekilde çalıştı. Top Operationda da detayları görmekteyiz.

Oluşturduğumuz Plan Guide ‘ı nasıl sileriz ?

Planı sildikten sonra EXEC edelim ve Execution Plan a tekrar bakalım.

Gördüğünüz gibi Planı sildiğimizde eski Execution Planı oluşturarak çalışmaya devam ediyor.


NOT :  Plan Guide sahip sp ler DROP edilemez. Hata verir.

Yukarıda hazırladığımız script te Plun Guide oluşturduğumuz sp yi en altta DROP etmeye çalışıyoruz.

Dönen hata mesajında da açıkca belirtmiş zaten. Bu sp için bir Plan guide olduğunu ve silinemeyeceğini.

 Kolay Gelsin.

Leave a Reply

Your email address will not be published. Required fields are marked *