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

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

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

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

Query Store - Tecrübe

Query Store'u (QS) neredeyse ilk çıktığından beri kullanıyorum. Doğru ayarlarla kullanıldığında oldukça faydalı olabilecek bir özellik. Microsoft'a bunun için teşekkürler!

QS'un nasıl etkinleştirildiğini veya nasıl ayarlanması gerektiğini internetten rahatlıkla bulabilirsiniz. Benim bu yazı ile size anlatmak istediğim ise yönettiğim bir ortamda QS ile yaşadığım tatsız tecrübelerin özet bir derlemesi.

Önce sorun yaşadığım ortam hakkında biraz bilgi vereyim. Veritabanının boyutu 2TB civarında, çalıştırılan kodlar veritabanında tutulmuyor, yani Stored Procedure ve saire yok. Sorgular uygulama tarafından dinamik olarak oluşturuluyor, bazıları parametreli, bazıları doğrudan değerleriyle birlikte geliyor.

Böyle bir veritabanı için QS dahili veritabanına 10GB alan tanımladım, biriken veriyi de en fazla 1 gün tutsun istedim; ama iş yükünün, sorguların doğası ve yaptığım izleme ayarı (monitoring) gereği bu alan yeterli gelmedi. 20GB'ı denedim olmadı, en son 25GB'ta bıraktım ve bunun bile zaman zaman yetmediğini gördüm. Bazen bakıyorum QS Read/Write modunda, bazen bakıyorum Read Only moda geçmiş, ki dahili veritabanı alanı yetmediğinde QS Read Only moda geçer. 

Bu dahili QS alanını çok büyük tutmak istemiyordum, çünkü önceden nahoş tecrübeler yaşamıştım. QS'in Garbage Collector'ü eski kayıtları silerken Blocking'e ve yavaşlıklara neden olabiliyor. Bunun yanısıra maalesef ispatlayamıyorum*; fakat QS'in dahili ve kontrol edemediğimiz bakım işlemleri sırasında Resource Semaphore sorununa neden olduğunu gözlemledim.

* Bir sorunun nedenini net kanıtlarla açıklayamamak gerçekten utanç verici ve beni çok rahatsız eden bir durum. Fakat uygulamanın açık kaynak kodlu olmadığını, bu özellik hakkındaki dokümantasyonun sınırlı olmasını ve tüm ayrıntılara ulaşamadığımızı göz önünde bulundurmanızı rica ediyorum.

Bu yan etkilerin yanısıra, QS'un etkin olduğu veritabanının Database Engine servisini yeniden başlattığımda canlı veritabanının gelen sorguları kabul etmediğini, sorguların zaman aşımı hataları aldığını gördüm. Sorguların bekleyişine dair ekran görüntüsünü aşağıda görebilirsiniz. Bu sorunun nedeninin ise, QS'un dahili veritabanı yüklenirken bu işlemi varsayılan olarak "senkron" modda yaptığını, bunun varsayılan ayar olduğunu, bu dahili veritabanı yüklenirken de canlı veritabanının hiçbir sorguyu kabul edemediğini gördüm. Bu anın ekran görüntüsü aşağıda.

QS açılışı

Dahili QS veritabanının 25GB olduğunu ve 2TB'lık canlı veritabanının içerisinde önce bu 25GB'lık veritabanının yüklenmesi gerektiğini, canlı veritabanınızın ancak bu yükleme işlemi bittikten sonra sorgu kabul edebileceğini düşünün. Kritik bir ortamda bu hiç de hoş bir durum değil. Bu konuda araştırma yaparken başka bir arkadaşın bu yükleme sırasında 3 saat beklediğini gördüm! Felaket.

Neyse ki 7752 kodlu bir Trace Flag (TF) mevcut. Bu TF, QS'un yüklenmesinin asenkron şekilde yapılmasını sağlıyor. Bu sayede örneğin Database Engine servisi yeniden başladığında ve canlı veritabanınızın Recovery işlemi tamamlanır tamamlanmaz, QS'un dahili veritabanının yüklenmesinin bitmesini beklemeden canlı veritabanınız sorguları kabul etmeye başlıyor ve QS veritabanının yüklenmesi arkaplanda ve asenkron olarak devam ediyor.

Notlar: 
- QS ile ağırlıklı olarak Stored Procedure kullanılan ortamlarda benzer bir sorun yaşamadım. 
- Veri ve işlem hacmi düşük olduğunda muhtemelen gelen sorgular ağırlıklı olarak dinamik SQL olduğu halde sorun yaşamayacaksınızdır.
- QS ile ilgili yaşayacağınız olası sorunlar, veritabanına gelen iş yükü ve yükün doğasına göre değişkenlik gösterebilir. 


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

SQLBits'ten notlar

SQLBits uzun yıllardır İngiltere'de gerçekleştirilen bir SQL Server etkinliğidir, Microsoft ve uluslararası topluluk bu etkinliğe oldukça ilgi gösterir. Bu etkinliğin sonuncusu 27 Şubat - 2 Mart 2019 tarihleri arasında gerçekleştirildi. Bu yazımda etkinlik boyunca sergilenen oturumlarda benim dikkatimi çeken konular hakkındaki notlarımdan bazılarını sizlerle de paylaşacağım.

Not: Bu etkinliğe fiziksel olarak gitmediğimi, oturumları uzaktan izlediğimi belirteyim. Yani bunu siz de yapabilirsiniz. Oturumların kayıtlarına buraya tıklayarak ulaşabilirsiniz.

Not: Etkinlik sırasında SQL Server 2019'un CTP 2.3'ü yayınlandı.

1- Sunuculuğunu Buck Woody'nin üstlendiği açılış konuşmasında Bob Ward'tan SQL Server 2019 ile birlikte artık sistem veritabanlarının da Always On Availability Groups'a katılabileceğini öğrendim. Bu sayede Instance seviyesindeki Login, Job ve benzeri nesneler de ikincil sunuculara otomatik olarak aktarılabilecek. Bunu Script'lerle yapmak gerçekten pek eğlenceli değildi, nihayet bu özelliğin geliyor olduğunu bilmek güzel. CTP 2.3'te yok, ama gelecek CTP'lerde görecekmişiz.

2- SQL Server 2019 ile birlikte 

"String or binary data would be truncated." * 

hata mesajının çok daha anlamlı bir hale getirileceğini zaten duymuştuk; ama tam olarak nasıl olacağını ben bilmiyordum. 

* Bilmeyenler için, bu hata mesajı örneğin 20 karakter uzunluğundaki bir metin alanına 21 karakterlik veri yazmak istediğinizde oluşur. Eğer bu işlemi yaptığınız kod bloğu içerisinde, ki SP veya toplu bir komut olabilir, tam olarak hangi kod satırındaki hangi tabloda hangi alanda işlem yaparken bu hatanın oluştuğunu bilemezsiniz ve bu sıkıcı bir "debug" çalışmasıdır.

Bu hata mesajının yeni biçimi şöyle oluyor 

"String or binary data would be truncated in table 'tablo adı', column 'sütun adı'. Truncated value: 'değer'." 

Nihayet! Hatta bunun için SQL Server 2017 ve 2016'ya kadar geriye dönük güncelleme çıkacaklarmış. Bu konuda Microsoft'un bir blog yazısını da buldum, okumak isteyenler buyursun.

3- Tempdb'de de güzel bir değişiklik var. SQL Server 2019 ile birlikte artık bu sistem veritabanındaki tüm sistem tabloları in-memory OLTP "Schema_Only" olarak oluşturulacak. Böylece sistem tablolarında Latch Contention sorunu oluşmayacak. Sonuçta tempdb yeniden başladığında boş olarak başlıyor, yani verinin kalıcı olmasına (durability) ne gerek var? Şu anki CTP 2.3'te yok bu özellik, ama gelecek CTP'lerde görecekmişiz.

4- Yine SQL Server 2019'da Accelerated Database Recovery (ADR) adında yeni bir özellik geliyor. SQL Server ile özellikle yoğun ortamlarda çalışanların ve yöneticilerin illa tecrübe ettiği bir hadisedir, çok uzun bir veri değişikliği sorgusu çalışır, bunun sahayı felç ettiği biraz geç anlaşılır, sonra yukarıdan büyük baskı gelir ve sorunlu sorgu tespit edilir ve durdurulmaya çalışılır; fakat sorgu durur mu? Durmaz. Çünkü Rollback süreci başlamıştır. Duruma göre bu oldukça uzun bir süre devam edebilir ve bu süreçte Blocking'ler, ciddi yavaşlıklar gibi sıkıntılar yaşanabilir. Hatta bazıları umutsuzca SQL Server Database Engine servisini yeniden çalıştırmayı dener, tabii ki bu beyhude bir girişimdir, çünkü Recovery süreci tamamlanmadan eski mutlu günlere dönemezler. İşte SQL Server 2019 ile birlikte gelecek olan ADR tüm bu sorunları çözüyor. Tabii ki her şeyin bir bedeli var, ADR'ın da. Bunun için bir çeşit "row versioning" yöntemi kullanılmış, ama SQL Server 2005 ile birlikte gelen gibi değil, çünkü ADR'ın Database Engine servisi yeniden başlatıldığında da işe yaraması gerekiyor; ama malum servis yeniden başladığında tempdb sıfırlanıyor, bu nedenle işe yaramaz. İşte bu yüzden ADR için "row versioning" işlemi ADR'ın etkinleştirildiği veritabanının içinde gerçekleştiriliyor, tempdb'de değil. Geçen gün Brent Ozar bunun demosunu yayınlamıştı, merak edenler buyursun.

5- Profesör Mark Whitehorn'un "Graph databases - What, how and why" isimli sunumunu çok keyifli bulduğumu belirtmek isterim. Konuyu SQL Server özelinde değil, genel olarak "Graph Database" modeli çerçevesinde anlatıyor; fakat sonuç itibariyle Node'lar, Edge'ler ve bunların birbiriyle ilişkisi. O yüzden birebir alakalı.

6- Microsoft'ta Program Manager olarak çalışan Pedro Lopez sunumunda ISV'lere uygulamalarınızı SQL Server versiyonuna, bulut veya on-prem olmasına göre değil, SQL Server Compatibility Level'a göre sertifikalandırın diyor. SQL Server versiyonunu yükselttiğinizde uygulamanın performansının daha kötü olabileceğinden çekinerek versiyon yükseltmemeyi düşünmeyin, misal bir uygulama veritabanı sunucusunu SQL Server 2012'den SQL Server 2016'ya yükselttiğinizde eğer veritabanının Compatibility Level'ını SQL Server 2012 seviyesine getirirseniz veritabanınızdaki kodlar yine SQL Server 2012'deki performansla çalışacaktır diyor (mealen). Çünkü yeni versiyonlarda Query Processor'da yapılan performans ile ilgili geliştirmeler ancak Compatibility Level'ın değiştirmesiyle devreye giriyor. Pedro Lopez Microsoft'un ISV'lerin sertifikalandırma konusundaki bakış açısını değiştirmek için çalışmalara devam ettiğini, hatta Sharepoint'in gelecek versiyonunun da bu şekilde sertifikalandırılacağını söylüyor.

Bununla birlikte "discontinued feature" olarak adlandırılan özelliklerin Compatibility Level'dan bağımsız olarak yeni versiyonlarda çalışmayacağını vurgulamakta fayda var. Örneğin SQL Server 2012'de Discontinued Feature olarak belirlenen bir özellik, veritabanını bir SQL Server 2019 Instance'ına taşıdıktan sonra Compability Level'ı 110 da yapsanız çalışmayacaktır. Bu nedenle versiyon yükseltme çalışmasının ön analizini dikkatlice yapmalısınız.

"Deprecated" özellikler ise Compability Level Protection kapsamında yeni versiyonlarda da desteklenmeye devam ediyor, yani SQL Server 2012'de "Deprecated Feature" listesine giren bir özellik, veritabanı sunucunuzu SQL Server 2019'a yükseltseniz bile Compability Level'ını 110 olarak ayarladığınızda çalışmaya devam ediyor.

7- SQL Server on Linux ilk duyurulduğunda hemen bir VM oluşturmuş, üstüne Ubuntu kurmuş ve SQL Server 2017'yi de onun üstüne kurup Host'taki SQL Server Management Studio'dan Ubuntu kurulu VM'deki SQL Server 2017'ye bağlanmış ve bunun da yazısını yazmıştım. Fakat Container nedir, SQL Server ile neden ve nasıl kullanılır açıkçası pek bilgim yoktu. Bob Ward'un "Inside SQL Server Containers" isimli oturumunu izlediğimde ise oldukça güzel bir resim oturdu kafamda. Bir veya daha fazla imajdan bir veya daha fazla Container'ın nasıl çalıştırılabileceğini, nasıl saniyeler içerisinde SQL Server'ın güncellenebileceğini veya daha üst bir versiyona yükseltilebileceğini, neden farklı imajlar oluşturmak isteyebileceğimi, Container kapandığında veritabanlarımı kaybetmemek için neler yapabileceğimi ve ne gibi senaryolarda SQL Server'ı bir Container'da (misal Docker) çalıştırmak isteyeceğimi öğrendim. Eğer bu konuda merakınız varsa, bu oturumu kaçırmamanızı özellikle tavsiye ederim.

Not: Bu oturumun seviyesi 400'dür. Yani ileri seviye.

Önemli hatırlatma:
SQL Server 2008 ve SQL Server 2008 R2 versiyonları için Microsoft Extended Support Temmuz 2019'da bitiyor, yani 4 ay kaldı. SQL Server veritabanı sunucularınızın SQL Server'ın yeni versiyonlarına Microsoft'un önerilerine %100 uyumlu olarak, ama saha tecrübesini de göz ardı etmeden yükseltilmesi, bu yükseltme çalışmasının en verimli, risksiz, hızlı ve profesyonel şekilde yapılması konusunda desteğe ihtiyacınız varsa lütfen tıklayın. Şirketinizin ihtiyaçlarına en uygun SQL Server Edition'ını birlikte belirleyelim, en düşük lisans maliyetiyle, en kısa kesinti süresiyle ve hiç veri kaybetmeden SQL Server sunucularınızın versiyonunu yükseltelim.

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

HATA MESAJI: The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\130\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)

HATA MESAJI:
The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\130\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)

AÇIKLAMA:
Replikasyonunuz sorunsuz çalışırken bir gün Distribution Agent'ınız böyle bir hata alarak durabilir. Haliyle replikasyonunuz da durur ve yukarıdaki hata mesajını görürsünüz.

Benim senaryomda ben bu sorun ile Transactional Replication kullanırken karşılaştım. A sunucum Publisher idi, Distribution veritabanım da A sunucusundaydı. B sunucusu da Subscriber idi ve Pull yöntemiyle A sunucusuna bağlanıp replikasyon ile aktarılacak işlemleri uyguluyordu.


ÇÖZÜM:
Bahsi geçen hata mesajı A sunucusundan geliyordu. Bu nedenle öncelikle A sunucusuna odaklandım. Aslında hata mesajı kendi içinde net, belli ki Distribution Agent (DISTRIB.exe) 'C:\Program Files\Microsoft SQL Server\130\COM' yolunda bazı geçici dosyalar oluşturmak istiyor; fakat yetkisi olmadığı için (errorcode 5 "Access is denied" demektir) bu hatayı alıyor.

Haliyle ben de A sunucusundaki 'C:\Program Files\Microsoft SQL Server\130\COM' yoluna SQL Server Database Engine servisi için yazma yetkisi verdim. Fakat sorunun hala çözülmediğini gördüm. Sonra aklıma replikasyon için farklı bir Domain hesabı kullandığım geldi, bu sefer "COM" klasöründe bu kullanıcı için yetki verdim; fakat sorunum hala çözülmemişti.

Sonra aklıma Microsoft'un sağ gösterip sol vurduğu eski hatıralarım geldi. Distribution Agent'ın da B sunucusunda olduğunu anımsayınca eski dost Microsoft'un benimle yine maytap geçtiğini anladım. İlgili kullanıcılara B sunucusundaki "COM" klasöründe yetki verince sorunum çözülmüş oldu.