28 Şubat 2020 Cuma

Hata: Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.

HATA:
Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.

AÇIKLAMA:
Always On Availability Group kullandığınızda eğer Secondary Replica'larınız Readable ise o zaman bu hata ile karşılaşabilirsiniz.

Bunun yanısıra Read Committed Snapshot Isolation Level kullandığınız ve Always On Availability Group olmayan senaryolarda da bu hata ile karşılaşabilirsiniz. Çünkü bu hata temel olarak hata mesajında da iletildiği gibi Version Store ile ilgilidir.

ÇÖZÜM:
Eğer bu hata ile karşılaşıyorsanız ya iş yükünüz için tempdb'ye fazla küçük bir disk kapasitesi ayırmışsınız demektir ya da tempdb'de herhangi bir nedenle kullanılabilecek alan kalmamıştır ve/veya tempdb'nin bulunduğu disk kapasitesi dolmuştur.

tempdb veritabanı dosyalarınızın bulunduğu disk kapasitesini kontrol etmenizi, tempdb kapasite doluluğu ile ilgili sorunu çözmenizi tavsiye ederim. Ondan sonra büyük olasılıkla bu hata mesajından da kurtulacaksınız.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com

2 Aralık 2019 Pazartesi

SQL Server'da Hafıza Kullanımı

Bazen müşterilerimden, bazen de forumlarda insanlardan SQL Server'ın hafıza (RAM) kullanımı hakkında benzer soru ve şikayetleri alıyorum: "CPU kullanımı düşükken veritabanı sunucusundaki RAM kullanımının %90 olması normal mi?" veya "SQL Server'da performans sorun var, RAM'in tamamını kullanıyor!".


İnsanları endişelendiren ekranın görüntüsü

Sorunun kısa yanıtı: "Evet, çok normal ve aslında tam da görmek istediğimiz manzara!"

Bundan sonrası sorunun uzun yanıt kısmı, vakti ve merakı olan okumaya devam edebilir.

Veritabanlarındaki tablolarınızda bulunan kayıtları ilgilendiren bir sorgu çalıştırdığınızda SQL Server öncelikle ilgili kayıtları içeren Page'ler* zaten hafızada mı (Buffer Pool) yoksa değil mi diye kontrol eder, şayet ilgili Page'ler hafızada değilse diske gider ve ilgili Page'leri hafızaya alır. Sonrasında ilgili kayıtlar için işlemler gerçekleştirilir.

* Page, SQL Server'daki en küçük depolama birimidir, 8 kilobayttır ve tablolarınızdaki kayıtlar bu mantıksal birimlerde saklanır.

Hafıza (RAM), disk donanım kaynağına göre çok daha hızlıdır. Bu nedenle SQL Server hafızadan olabildiğince çok faydalanmak ister ve aynı nedenle bir Page diskten hafızaya alındığında, o Page'in olabildiğince makul bir süre hafızada kalmasını isteriz, ki SQL Server sürekli diske gidip tekrar aynı Page'leri hafızaya taşıma işlemi gerçekleştirmesin. En sık kullanılan Page'lerin en makul miktar ve sürede hafızada kalması* yararımızadır.

* Bir Page'in ortalama hafızada kalma süresini Performance Monitor'deki "Page Life Expectancy" (PLE) sayacıyla ölçebiliriz.

Eski iyi pratikler PLE değerinin 300 (saniye cinsinden) iyi bir beklenti olduğunu söyler, fakat 300 zamanımızda artık oldukça düşük bir değer. Peki en iyi değer nedir? Herkes için en iyi değer diye bir değer yoktur, bu değer iş yükünüze, uygulamanızın çalışma doğasına, imkanlarınıza/bütçenize ve beklentinize göre değişir. Bununla birlikte, her halükarda beklentimiz ve hedefimiz, Page'lerin olabildiğince uzun süre hafızada kalması olmalıdır.

Varsayılan Ayar
Yeni bir SQL Server Instance'ı kurduğunuzda varsayılan olarak "Max Server Memory" ayarı 2147483647 megabayttır, yani bir anlamda Buffer Pool için "kullanabildiğin kadar RAM'i kullan"dır. Buna SQL Server literatüründe "dinamik hafıza yönetimi" denir. 

Kurulumlardan sonra yapılacak ayar değişikliklerinden biri de bu ayarı makul bir değer* ile değiştirmektir. Çünkü aynı sunucu üstünde SQL Server'ın haricinde işletim sistemi ve diğer elzem uygulama ve hizmetlerin de çalışması ve çalışacak her uygulamanın da hafızaya ihtiyacı olacaktır. Varolan hafıza kaynağının tüm bu uygulamalar arasında herhangi bir çatışmaya neden olmayacak şekilde dikkatlice paylaştırılması gerekiyor.

* Makul değer sunucudan sunucuya ve kullanılabilir fiziksel hafıza miktarına göre değişiklik gösterir. Ayrıca ayarlayıp unutmamak, ayardan sonra sürekli izlemek gerekir. Çünkü zamanla ihtiyaçlar değişebilir. Hafıza kaynağımızı atıl şekilde de bırakmak istemeyiz, sanal hafıza (Windows Page File) kullanılmasını da istemeyiz.

Eğer "Max Server Memory" ayarını yapılandırmazsanız veya yanlış yapılandırırsanız o zaman Task Manager veya başka araçlarla sunucuda kullanılan hafıza oranına bakarsanız %100'leri görebilirsiniz*, işte bu hiç istediğimiz bir durum değildir. Çünkü böyle bir manzara uygulamalar arasında hafıza için çatışma yaşandığı veya yaşanabileceği, sanal hafıza alanının kullanımı gibi şeyleri getirir akla. Performans açısından hiç istemediğimiz bir durum.

* Eğer sunucunuzdaki fiziksel hafıza miktarı, sunucu üstündeki veritabanının hacminden daha yüksekse hafıza kullanımının %100'e çıktığını hiç görmeyebilirsiniz de. Misal sunucuda 128GB RAM varsa ve o sunucu üstündeki toplam veritabanı boyutu 10GB ise muhtemelen hafıza kullanımınız 15-20GB'ı hiç geçmeyecektir.

En sık kullandığınız kayıtlara ait Page'ler hafızadayken sunucu üstündeki başka bir hizmetin veya uygulamanın veya işletim sisteminin hafıza ihtiyacı nedeniyle SQL Server ile hafıza çatışmasına girdiğini ve bu çatışma sonucunda en yüksek işlem hacminin gerçekleştiği bir vakitte işletim sisteminin hafıza kaynağının bir kısmını diğer uygulamalar ve hizmetlere ayırmak için SQL Server'ın Page'lerinin bir kısmını RAM'den attığını ve bunun sonucunda da SQL Server'ın aşağıdaki hatayı ürettiğini düşünün?

"A significant part of sql server process memory has been paged out. This may result in a performance degradation."*

* Bu bir örnek değil, gerçek hayatta tam da böyle oluyor. Bu durum oluştuğunda bu hatayı SQL Server Error Log ve Windows Application Event Log'ta bulabililirsiniz.

Özellikle kritik veritabanı sunucuları için bu tüylerimizi diken diken eden bir manzaradır. Bu olumsuz sonucu son kullanıcılarınız hemen hissedecektir. Çünkü bu durumda uygulamalarınız bloklanma, donmalar ve yavaşlıklar yaşarlar ve bunların sonucunda uygulamalarınız zaman aşımı hataları alabilir. Sonuç olarak ciddi ve düzensiz performans sorunları yaşarsınız ve performans açısından istikrarsız bir ortamınız olur.

Windows Local Security Policy'deki "Lock pages in memory" ayarı sayesinde işletim sisteminin SQL Server'ın Page'lerine müdahale etmesini engelleyebilirsiniz. Fakat yukarıdaki açıklamalardan görebileceğiniz üzere bu sandaldaki bir deliği tıkar, diğer deliğin açılmasına neden olur. Çünkü belli ki bir hafıza ihtiyacı var ve bu bir şekilde karşılanmalı, gerekiyorsa Windows Page File kullanılarak. Diğer hizmet sunucularını bilemiyorum; ama şahsen ben sunucumdaki Windows Page File'ın rutin olarak kullanıldığını görmek istemem.

Bazen...
Bazen tüm yapılması gereken ayarları yaptıktan sonra bile zaman zaman Page Life Expectancy değerinin birden dip yaptığını, misal 100'lerin altına kadar indiğini görebilirsiniz. Böyle anlarda eğer bir takip mekanizmanız varsa veya bilgisayar başından anlık olarak kontrol edebilirseniz ender çalışan, oldukça büyük tablolardan oldukça yüklü miktarda verilerin çağrıldığı masraflı bir sorgunun çalıştığını görebilirsiniz. Özellikle karışık iş yüklerinin olduğu* ortamlarda bu durumla karşılaşılabilinir.

* Hem OLTP işlemlerin hem de raporlama işlerinin çalıştığı ortamlar.

Eğer ortam kritik bir veritabanı ortamı ise farklı raporlama tasarım ve çözümlerini masaya getirmekte fayda olabilir, ki böylece ortam OLTP iş yükü için çok daha homojen hale getirilebilir ve böylece ortamın performans açısından çok daha istikrarlı olması sağlanabilir; fakat bu tamamen farklı bir yazının konusu.

Sonuç
Sonuç itibariyle: 
- SQL Server veritabanlarınızdaki tablolara ait kayıtları olabildiğince uzun süre fiziksel hafızada tutmak istersiniz,
- Bunun için Performance Monitor'deki "Page Life Expectancy" sayacının değerini en azından birkaç gün izleyip doğru noktada olup olmadığınızı ölçebilirsiniz,
- Bunları sağlamak için de sunucunuzda yeterli miktarda fiziksel hafıza kaynağı bulunduğundan ve kurulumdan sonra SQL Server'ın "Max Server Memory" ayarını yapılandırdığından emin olmalısınız.

Veritabanı sunucularınızı her zaman yakından takip etmenizi öneririm, çünkü her an, her şey değişebilir. Veritabanını kullanan uygulamalarınızdaki kodlar, iş birimlerinizin ihtiyaçları, kampanyalar, fiziksel/sanal ortamlarınızdaki altyapı değişiklikleri veritabanı sunucularınızın performansını etkileyen başlıca faktörlerdir. Eğer sunucularınızı sürekli izlerseniz, anormal bir durum karşısında hangi değerin normal, hangisinin anormal olduğunu rahatlıkla ayırt edebilir ve doğrudan anormalliklere odaklanabilirsiniz.

Microsoft SQL Server veritabanı sunucularınızında sorun mu yaşıyorsunuz? Size yardımcı olabilirim!

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com


7 Kasım 2019 Perşembe

SQL Server 2019 Standard Edition'daki Yenilikler

4 Kasım tarihinde Microsoft tarafından SQL Server 2019'un RTM olduğu duyuruldu. SQL Server 2019 olmadan önce tabii ki önizleme versiyonları yayınlanmıştı, bu nedenle birçok özelliğini öncesinde deneyimleyebildik. SQL Server 2019 RTM olmadan hemen önce hangi özelliklerin sadece Enterprise Edition'da, hangi özelliklerin Standard Edition'da olacağını öğrenmiştik. Artık ürün RTM olunca, yani her şey netleşince, SQL Server 2019 Standard Edition ile yeni gelen özelliklerden ve eski Enterprise, yeni Standard Edition özelliklerinden bazılarından özetle* bahsedeyim istedim.

* Ancak özetle bahsedebileceğim, çünkü her özelliğin ayrıntıları deniz derya. Standard Edition'daki tüm yeni özelliklerin duyurusuna buradan ulaşabilirsiniz.

Ek Bilgi: Software Assurance müşterileri için yine son günlerde duyurulan sürekli kullanılabilirlik ve felaketten kurtarma seçeneklerine dair lisanslama kolaylığını LinkedIn'de paylaşmıştım. Görmeyenler için buraya da not etmiş olayım.

Öncelikle SQL Server'ın önceki versiyonlarında da olan, fakat sadece Enterprise Edition müşterilerinin kullanabildiği ve SQL Server 2019 ile birlikte Standard Edition'da da kullanılabilen özelliklerden bahsedeyim.

Transparent Database Encryption (TDE): Bu özellik SQL Server 2008'den beri var ve bununla verilerimizi barındıran dosyaları diskte şifrelemiş (encryption) oluyoruz. Ayrıca veritabanı yedek dosyalarımız da otomatik olarak şifrelenmiş oluyor. Böylece veritabanımıza bir Login veya uygulama açığı ile giremeyip, veritabanı dosyalarımıza ulaşabilecek kötü niyetli kişiler, bu dosyaları alsalar dahi gerekli sertifikalar olmadan verilere ulaşamıyorlar. Güvenlik açısından oldukça önemli bir özellik.

Always Encrypted with secure enclaves: Veritabanınızın bulunduğu bir SQL Server Instance'ında "sysadmin" üyesi bir Login, özelleştirilmiş bir şifreleme yöntemi kullanmadığınız müddetçe o Instance'taki tüm veritabanlarındaki tüm verilere ulaşabilir. Always Encrypted özelliği ilk defa SQL Server 2016 Enterprise Edition ile gelmişti. Bu özellik sayesinde kritik olan veriler sütun bazında şifrelenebiliyor, uygulamalar ihtiyaç duydukları verileri veritabanından çekerken veya veritabanına işlerken şifrelemeyi kendi taraflarında uyguluyorlar. Veritabanı yöneticisinde veya kötü niyetli kişilerde ilgili sertifikalar olmadan şifreli veriler deşifre edilemiyordu. Bu özelliğin bu versiyonunun bazı sınırlamaları vardı, örneğin sorgunuzla bir veriye ulaşmak istediğinizde "=" kullanmanız gerekiyordu, belli desenlere göre (WHERE ... LIKE ...) veya büyüktür, küçüktür operatörleriyle arama yapamıyordunuz.

Microsoft "secure enclaves" eklentisiyle bu sorunları çözmeyi hedefliyor. Çünkü bu kısıtlar bu özelliğin kullanımını sınırlayan kısıtlardı. "secure enclave" sunucu tarafında, SQL Server kapsamında, güvenli bir hafıza bölümünde SQL Server Database Engine içerisindeki şifrelenmiş hassas verileri düz metin olarak işleyen bir güvenli ortam.

Bu grafiği yukarıda adresini verdiğim sayfadan aşırdım.

Açıkçası o kadar şirkete girip çıkıyorum, onlarca sunucunun yönetimine destek oluyorum henüz hiç bu özelliği kullananı görmedim de, duymadım da. Tabii bunun haklı nedenleri var. Öncelikle TDE gibi oturmuş bir özellik değil, henüz yeni. Bu versiyonda da gördüğümüz gibi eklemelerle eksiklikleri giderilmeye çalışılıyor. Bununla birlikte -korkutmak gibi olmasın ama- TDE gibi "oturmuş" dediğimiz özelliklerde bile zaman zaman korkunç Bug'lar oluşabiliyor. Sonuç itibariyle denediğiniz bir özellik doğrudan verinizi etkiliyorsa, gerçekten çok temkinli olmakta, dünya çapında literatürü takip edip, bol bol mümkün olduğunca çok senaryoyu içerecek testler yapıp özelliğin yan etkilerini iyi anlamak çok çok çok önemli. Ne kadar vurgulasak az.

Aklınıza "Peki TDE ile Always Encrypted arasındaki temel fark nedir?" sorusu gelmiş olabilir, hemen cevaplayayım. TDE veritabanınızdaki verinin tamamını* diskte (at rest) şifreler, Always Encrypted ile ise veritabanınızdaki belli bir tablonun belli sütunlarını şifrelersiniz. TDE kullandığınızda "sysadmin" rolünün üyesi biri o veritabanındaki verilere ulaşabilir; fakat Always Encyrpted'ta bu mümkün değil**.

* Veritabanınızın tempdb'yi kullanma olasılığına karşın tempdb'yi dahi şifreler, ta ki Instance'taki en son TDE özelliği açık veritabanı kalmayıncaya kadar.
** Sertifika olmadıkça.

Accelerated Database Recovery (ADR): Sanırım 1 ay önceye kadar kimse bu özelliğin Standard Edition'a geleceğini tahmin edemezdi. Çünkü çok "Enterprise Edition" kokan bir özellik. Yeterince tecrübesi olanlarınız muhakkak yaşamıştır, çok uzun süren ve veri değişikliği yapan bir sorguyu durdurduğunuzda veya tam o esnada Database Engine servisini (çeşitli korkunç nedenlerle) yeniden başlattığınızda Rollback'in / Recovery sürecinin tamamlanmasını beklemek tam bir işlence olabiliyor. Bunun en kötü örneklerinden birini sanırım 2010 senesinde yaşamıştım, çok kritik bir uygulamamızı barındıran SQL Server 2000 (evet, biliyorum...) Instance'ındaki Rollback 8 saat sürmüştü. Tam bir cehennem.

ADR sayesinde Rollback / Recovery süreçleri anında sonlanıyor. Laf olsun diye "anında" demedim, gerçekten anında sonlanıyor. Bunun nasıl olduğunu merak edenler bu başlıkta paylaştığım dokümantasyona bakabilir.

Intelligent Query Processing (IQP): Bu özellik ailesindeki her bir özellik değil, ama bazıları Standard Edition'a da geliyor. Bu bazılarından en öne çıkanı ve dünyada en çok ses getireni "Intelligent Database: scalar UDF inlining" özelliği oldu. Yazılımcı arkadaşlar User Defined Function (UDF) kullanımını sever, çünkü kodu bir kere yazarsın ve birçok yerde kullanabilirsin, ayrıca kodu okumayı da kolaylaştırır, çünkü o UDF içerisinde ne olduğunu zaten bilirsin. Fakat SQL Server Query Optimizer açısından UDF kullanımı sıkıntılıdır. Örneğin bir sorguda UDF kullanıldığında o sorgu paralel çalışamaz ve sorgunun çalıştırma planında UDF ile ilgili performans sorununu göremezsiniz. "Intelligent Database: scalar UDF inlining" sayesinde ise UDF'i çağıran sorgu içerisinde UDF sanki elle yazılmış kod gibi açılır, yani yukarıda saydığım olumsuz durumlar ortadan kalkmış olur.

Diğerleri için ilgili dokümantasyonu inceleyebilirsiniz.

Şimdilik bu kadar. Bu yazı ile maksadım sizleri SQL Server 2019 ve Standard Edition'daki yeni özelliklerden genel olarak haberdar etmek, bazı yenilikleri de vurgulamaktı. Umarım faydalı olur.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com



14 Ekim 2019 Pazartesi

SQL Server'da "SQL Injection" Saldırı Vakası

Birkaç aydır kendi ürünüm olan Microsoft SQL Server ortamları için veritabanı izleme uygulamam Kangal'ın yeni major versiyonuna son halini vermek konusunda ve bir yandan da müşteri ortamlarının ihtiyaçları ve yeni projeler konusunda çalışıyorum. Son gelişmelerden sonra bir tecrübeyi paylaşmak istedim.

Kangal'ın yeni versiyonuna SQL Injection saldırılarını yakalayacak yeni bir özellik ekledim. Pilot ortamlarda başarılı sonuç elde ettikten sonra ilgili güncellemelerin tüm müşteri ortamlarına da akmasını sağladım.

Yeni versiyonun pilot süreci devam ederken bu özellik bazı müşterilerde zaman zaman alarm üretiyordu, alarm geldiğinde ilgili IT yöneticisine bu alarm için gelen e-postaya cevaben "planlı bir pentest çalışması mı yapılıyor?" diye soruyordum ve "evet" cevabını alınca hayatımıza devam ediyorduk. Bu ortamlardan birinde, bir müşteriden gene alarm geldi. Her zamanki gibi IT yöneticisine yine bir pentest çalışması olup olmadığını sordum ve "hayır" cevabını aldım. Tabii o anda hemen yangın zilleri çalmaya başladı; IT yöneticisi, Yazılım Müdürü ve hatta Genel Müdür, bu saldırıdan haberdar olması gereken herkese ulaştım. Sorunun üstünde hepbirlikte çalıştık, yazılım tarafındaki açıklar tespit edildi ve en kısa sürede soruna müdahale edilip bertaraf edilmiş oldu.


Saldırı esnasında Kangal'ın ürettiği alarmlardan bir örnek


SQL Injection saldırısı nedir? 
Özellikle dinamik SQL kullanılan uygulamalarda, arayüzlerde gerekli korunma önlemleri de alınmadıysa, uygulamaların ürettiği SQL cümleciklerinin arasına veya sonuna "enjekte edilen"/eklenen SQL cümlecikleriyle veritabanlarınıza ve hatta veritabanı sunucunuza saldırganların müdahale edebilmesidir çok özetle. Bunun nasıl yapılabileceğine dair internette oldukça fazla kaynak mevcut.

Bu sorunu fark edemeseydik neler olabilirdi?
Saldırganlar veritabanımızda birçok şey yapabilirlerdi. Örneğin görmelerini istemediğimiz verileri görebilirlerdi, kayıtlarda değişiklikler yapabilirlerdi, tablolarımızı ve hatta veritabanlarımızı silebilirlerdi, hatta disklerimizi formatlayabilir, sunucumuzu kapatabilirlerdi, ki saldırgan arkadaş bunu da denedi.

Neler yapabilecekleri, bizim proaktif olarak ne kadar önlem aldığımıza, uygulamamızı ve yetkilendirme politikamızı ne kadar sıkı tuttuğumuza, en iyi pratikleri ne kadar yakından izlediğimize ve ne kadar disiplinli olduğumuza göre değişir. Eğer tüm uygulama kullanıcılarına "sysadmin" veya "db_owner" yetkisi verilen, SQL Server servis hesabı için en iyi pratiklerin uygulanmadığı, Instance seviyesindeki ayarlara önüne gelenin güvenliği düşünmeden müdahale ettiği bir ortam olsaydı, yaşadığımız bu saldırı felaketle sonuçlanabilirdi.

Özellikle küçük ve orta ölçekli şirketlerin böyle saldırıları fark edecekleri mekanizmaları (uygulama ve kalifiye personel) olmuyor. Eğer bu konuda yetenekli bir uygulamanız yoksa bu tür saldırıları fark ettiğinizde oldukça geç kalmış olabilirsiniz. Çünkü bu saldırıda saldırganın çalıştırdığı komutları uygulamanın çalıştırdığı onca komuttan göz kontrolüyle ayıramazsınız. Ancak uygulama hatalarını yakalayan bir mekanizmanız varsa ve mekanizmanın yakaladığı anormallikler konusunda sizi uyaran alarmlar varsa bu saldırıyı böyle yakalarsınız ya da saldırıyı vakitlice fark edemezsiniz ve ancak saldırının sonuçları sayesinde saldırıdan haberdar olursunuz, ki bu da hiç hoş bir deneyim olmaz. 

Kangal uygulamalarınızın veritabanına karşı çalıştırdığı SQL kodlarına dair tüm hataları takip eder. Bir hata oluştuğunda bu hataya dair ayrıntıları kayıt altına alır. Yeni eklenen SQL Injection koruma özelliği sayesinde de bu üretilen hataları analiz eder ve olası SQL Injection saldırılarına karşı tüm ekibinizi uyarır. Olası saldırılar veya uygulama hatalarınız hakkında en kısa sürede, ayrıntılı bir şekilde haberdar olmuş olursunuz.

Kangal'a eklenen yeni özelliklerden bir diğeri de veritabanı yedeklemesi ile yapılan veri hırsızlığına karşı izleme yapması ve alarm üretmesi. Bu konuya da bir dahaki yazımda değineceğim.

Meraklıları için İpucu:
Eğer benzer bir uygulamayı kendi olanaklarınızla kendi ortamlarınızda yapmak isterseniz bunu Extended Event'lerle yapabilirsiniz. Extended Event'in "error_reported" adında bir Event'i var. Bu Event ile SQL hatalarını kayıt altına alabilir, bu kayıtları taratıp anormallikleri raporlatabilirsiniz.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com


26 Haziran 2019 Çarşamba

Bir Sorgu Çalıştırma Planını Görmek İsteyip Göremediğinizde...

SQL Server ile belli bir tecrübesi olan herkesin bildiği gibi bir T-SQL komutunu çalıştırdığınızda, bu komut için önce bir çalıştırma planı (Execution Plan) oluşturulur, bu plan Plan Cache'te depolanır* ve komutunuz bu plana göre** çalışır.

* "Optimize for ad-hoc workloads" ayarı devrede olduğunda ad-hoc sorgular için çalıştırma planının tamamı depolanmaz Plan Cache'te, yalnızca "Stub" denilen küçük bir bölümü depolanır. Böylece Plan Cache bir daha muhtemelen kullanılmayacak binlerce plan ile dolup taşmaz.

** İstisnai durumlarda yolda plan değişebilir, işte bu nedenle Estimated Execution Plan ile Actual Execution Plan farklı olabilir.

Belli bir Batch veya stored procedure için Plan Cache'teki çalıştırma planını görmek istediğimizde sys.dm_exec_query_plan isimli DMV'den faydalanabiliriz. Örneğin:

SELECT [query_plan] FROM sys.dm_exec_query_plan();

komutuyla X isimli stored procedure'ün plan handle'ını sorgulayıp çalıştırma planını aldığınızı düşünün. Eğer bunu yeterince çok denediyseniz, ilgili sorgunun planının Plan Cache'te olduğundan emin olduğunuz halde yukarıdaki sorgunun zaman zaman null değeri döndüğünü görmüşlüğünüz olmuştur. Bu yazımda bunun nedenini açıklamak istedim.

Bir stored procedure içerisinde temp table kullanılıyorsa, sorgu belli koşullara göre birkaç seçenek içeriyorsa veya "RECOMPILE" seçeneği ile çalıştırılıyorsa o zaman deferred compilation denilen olay gerçekleşiyor ve SQL Server stored procedure ilk defa çalıştırılırken sorgunun tamamı için çalıştırma planı oluşturmuyor. 

sys.dm_exec_query_plan isimli DMV çalıştırma planını bir bütün olarak getiriyor. Ya hep, ya hiç. Eğer Batch'inizdeki veya stored procedure'ünüzdeki tüm bloklar için çalıştırma planı varsa*, o zaman bu DMV ile ilgili sorgunuza dair oluşturulan çalıştırma planını Plan Cache'ten edinebilirsiniz.

* SQL Server 2005'ten beri Batch içerisindeki tüm kod blokları için ayrı ayrı çalıştırma planı üretiliyor.

Peki Batch'inizde veya stored procedure'ünüzde deferred compilation nedeniyle henüz çalıştırma planı olmayan komutlar varsa, ama siz olduğu kadarını görmek istiyorsanız ne yapacaksınız? sys.dm_exec_query_plan DMV'si bu durumda null sonucunu döner, işte bu durumda sys.dm_exec_text_query_plan isimli DMV'yi kullanmalısınız.

sys.dm_exec_text_query_plan isimli DMV'yi kullanırken eğer "statement_start_offset" ve "statement_end_offset" değerlerini girmezseniz yine sys.dm_exec_query_plan DMV'si ile yaşadığınız sorunu yaşarsınız. Bu nedenle bunu atlamamanız önemli.

Örnek kullanım:
SELECT  
    CAST([qp].[query_plan] AS XML) AS [query_plan],
[qs].[execution_count],
...
FROM sys.dm_exec_query_stats AS [qs]   
CROSS APPLY sys.dm_exec_text_query_plan ([qs].[plan_handle], [qs].[statement_start_offset], [qs].[statement_end_offset]) AS [qp]
WHERE OBJECT_NAME([qp].[objectid], [qp].[dbid]) = 'sp_adı';


Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com