Dynamic String Execution ve Forced Statement Caching

Dynamic String Execution ve Forced Statement Caching

SQL Server Data Engine için en maliyetli işlemlerden biri, execution plan üretimidir. Query’e dahil olan join, group by, order by, conditionlar, diğer özel ifadelerin etkileyeceği kaynak tüketimi ve stabil çalışma yöntemi tespit edilerek result setin elde edilmesi ya da veri manipülasyonun gerçekleştirilmesi sağlanır.

DBA ya da developer olarak başlıca dikkat edilmesi gereken konu, oldukça az kaynak harcanmasını sağlayacak scriptler geliştirilmesidir.

Veri tabanıyla ilişkili olan geliştirdiğimiz uygulamalar, servisler vb. ortaya koyduğumuz her ürün için gerektiği takdirde dinamik olarak veriyi filtreleme ihtiyacı duyabiliriz. Bu nedenle akla gelen ilk yöntem olan “Dynamic Script” kullanılmaktadır.

Dynamic Script’in amacı, geliştirilen T-SQL scriptlerinin duruma göre bazı bölümlerinin query e dahil olmasını ya da olmamasını sağlamak, standart T-SQL söz dizimi ile elde edilemeyecek vb. optimum düzeyde çalıştırılmaya hazır query üretmektir. Fakat göz ardı edilen durum ise, kullanılan yöntemleridir.

Basit bir senaryo ile bu durumları değerlendirelim. Bir E-Ticaret çözümü kullanıyoruz ve veri tabanımıza anlık olarak istekler geliyor. Kayıtlı olan ya da olmayan ziyaretçiler beğendikleri ürünleri görebilmek için resme tıkladıklarında veri tabanımıza ilgili ürünü gösterebilmek amacıyla istekler geliyor. Bir ziyaretçi 5 ayrı ürünü görüntülemek için istekte bulundu ve veri tabanı tarafında gerçekleşen execution plan üretimi ve kullanımını inceleyelim.

İşlemlere başlamadan önce buffer pooldaki execution planları temizlemek için DBCC FREEPROCCACHE komutunu çalıştırıyorum. Production ortamlarında bu komutun çalıştırılması performans konusunda oldukça olumsuz sonuçlara neden olacaktır, bu nedenle izole edilmiş demo ya da test ortamları haricinde kesinlikle çalıştırmayınız.

Ziyaretçimiz sırasıyla, ProductId’si 318, 352, 461, 714 ve 719 ürünleri çağırdı. Ürettiğimiz query nin aşağıdaki gibi olduğunu düşünelim.

DynamicSQL_00

Her ProductId değeri değiştiğinde oluşan execution planları inceleyelim.

DynamicSQL_01

Görüldüğü üzere her ProductId değeri değiştiğinde yeni bir plan üretildi. Ayrıca memory üzerinde yer alan buffer poolda kapladığı alanın byte cinsinden değeri SizeInBytes alanında gösterilmektedir.

Oluşturulan dynamic scriptin çalıştırılma yöntemini EXEC(@productQuery) yerine, EXEC sp_executesql olarak seçip gerçekleşen değişiklikleri gözlemleyelim. Bu işlem için scripti aşağıdaki şekilde düzenleyelim.

DynamicSQL_02

Sırasıyla diğer ProductId leri kriter olarak verelim oluşan execution plana ve kullanım değerlerini inceleyelim.

DynamicSQL_03

İlk ProductId olan 318 i verdiğimizde diğer planlardan farklı olarak query nin başına parametrik değerimizi eklediğini görebiliyoruz. Şimdi 352, 461, 714 ve 719 ProductId lerini verip kontrollerimize devam edelim.

DynamicSQL_04

Yukarıdaki görselde UseCounts alanında yer alan değerin 5 olduğunu ve bu query için execution planın her seferinde yeniden üretilmek yerine, var olan planın kullanımının gerçekleştiğini görüyoruz.

Execution plan üretiminin maliyetli bir operasyon olduğundan bahsetmiştik. Bu nedenle özellikle tutarlı sistemlerde plan reuse kavramı önem kazanmaktadır. Dynamic Script üretiminde ve kullanımında EXEC(@query) engine tarafından ad-hoc query olarak değerlendirildiği ve Dynamic String Execution mekanizması nedeniyle her seferinde farklı plan üretilirken, EXEC sp_executesql ise Forced Statement Caching mekanizması nedeniyle mevcut plan kullanılarak, gerçekleşebilecek maliyetli operasyonlardan feragat edilecektir. Ayrıca, memory üzerinde her planın kapladığı alan düşünülürse, binlerce transaction alan yapılarda plan reuse’ın kıymeti daha da artacaktır.

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir