SQL Server Ad-Hoc Query performansını iyileştirme

Selamlar,

Ad-Hoc query Performans iyileştirmesinin nasıl olması gerektiğini yazmadan önce Ad-Hoc query’nin ne anlam ifade ettiğini hakkında bilgi vermek konunun daha iyi anlaşılması adına önem arz ediyor. Bu sebepten Ad-Hoc Query nedir sorusunun cevabını vereyim.

Ad-Hoc Query: Ad hoc kelimesinin kökeni Latince ’den gelir ve “amaç için” anlamına gelmektedir. Yani yazılan sorgunun bir amaç için yazılıp devamının olmaması ya da nadiren olması durumuna yazılan bir sorguyu ifade etmektedir. Örnek ile açıklamam gerekirse, kullanıcı bilgilerin yer aldığı bir tablonuz olduğunu düşünün ve bu tablo üzerinden dinamik olarak farklı kullanıcı numarası üzerinden sorgulama yapıldığını varsayalım. Bu durumda yazılan sorgu aslında bir Ad-Hoc query’dir. 

SQL Server’da bir sorgu çalıştırdığınızda, SQL Server Query Optimizer tarafından çalıştırılan sorgu için execution plan oluşturulur ve bu plan üzerinden sorgunun çalışması sağlanır.

Bahsi geçen execution plan oluşturulması, basit amaçlı yazılmış bir sorgu için birkaç milisaniye içerisinde tamamlanabilirken kompleks yazılmış sorgular için daha uzun sürebilir. Bu nedenle SQL Server, aynı sorgu tekrar çalıştırılırsa diye her çalışan sorgu için oluşturduğu execution plan bilgisini önbelleğinde tutar. Düşünün, bir durum anında yazılan ve bir kez ihtiyaç için çalıştırıldığınız  ve bir daha çalıştırılmayacağınız durumlar da dahi SQL Server sorgunuzun çalışması için bir execution plan oluşturuyor ve bunu ileride tekrar çalıştırabilirsiniz diye önbelleğinde barındırıyor. Eğer tabi sorgunuzun çalışması için uygun bir execution plan yok ise SQL Server Query Optimizer tarafından yeni bir sorgu planı oluşturulur.

SQL Server 2008 ile yaşantımıza giren sık kullanılmayan sorguların execution planların önbellek verimliliğini artırmak adına sunusu seviyesinde bir özellik olan “Optimize for Ad hoc Workloads” tanıttı. Sorgu ilk kez derlendiğinde execution planı tutmak yerine Compiler Plan Stub – Derlenmiş Sapma değeri- değerini depolar. Eğer ki sorgu tekrar çalıştırılırsa Stub değerini execution plan yerine dönüştürür. Anlattığımız bu özelliğin açık mı yoksa kapalı mı olduğunu belirlemek için kullanabileceğiniz iki yöntem vardır. İlki SQL Server Management Studio’dur (SSMS) Object Explorer ‘da sunucuya sağ tıklayın ve ardından Özelliklere tıklayın, ardından advanced menüsü içerisinde “Optimize for ad hoc workloads” kısımında  varsayılan olarak False gelir, yani bir sorgu derlendiğinde ve yürütüldüğünde tüm planın önbelleğe yerleştirileceği anlamına gelir.

GUI – Ekran- dan öğrenme konusunda bu özelliğin aktif yada pasif olduğunu öğrenmek adına TSQL kullanabilirsiniz. Aşağıdaki sorgu bu ihtiyacınızı çözecektir.

SELECT name , value , description FROM sys.configurations
WHERE Name = ‘optimize for ad hoc workloads’

 Sorgu sonucu ise;

Örneğimizin çalışmasını yapmadan önce ilk adım olarak execution plan temizliğini aşağıdaki komut ile yapıyorum. Tabi SQL Server hizmetini yeniden başlatmakta cache – önbelleğin – temizlenmesini sağlayacaktır fakat bunu tercih etmiyor / kullanmıyoruz 😊

DBCC FREEPROCCACHE

Örneğimizi SQL Server 2019 Developer Edition üzerinde Microsoft’un örnek veri tabanlarından olan AdventureWorks2019 ile çalışıyor olacağım. Bahsi geçen “Optimize for Ad Hoc Workloads” özelliği şuan için kapalıdır.

Aşağıdaki sorguyu çalıştırdım.

SELECT * FROM [AdventureWorks2019].[Person].[Person] Where BusinessEntityID = 3

Sorgumu çalıştırdıktan sonra, sorgu planın alabilmek için aşağıdaki sorguyu çalıştırıyorum.

SELECT c.usecounts
, c.objtype
, t.text
, q.query_plan
, size_in_bytes
, cacheobjtype
, plan_handle
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS q
WHERE t.text LIKE ‘%select% *%’ AND q.dbid = (select database_id from sys.databases where name =’AdventureWorks2019′)
ORDER BY c.usecounts DESC

Sorguyu ilk kez çalıştırmış olmama rağmen önbellek üzerinde bu sorgu ve execution plan için “106496” byte kullanılmıştır.  AdventureWorks üzerinde çalıştırdığım sorguda BusinessEntityID üzerinde değişiklikler yapıp tekrar çalıştırıp tekrar execution planları kontrol ediyorum.

 Sorgu1:

SELECT * FROM [AdventureWorks2019].[Person].[Person]  Where BusinessEntityID = 11

Sorgu2:

SELECT * FROM [AdventureWorks2019].[Person].[Person]  Where BusinessEntityID = 6

Sorgu3:

SELECT * FROM [AdventureWorks2019].[Person].[Person]  Where BusinessEntityID = 20

Yukarıdaki 3 sorguyu çalıştırdıktan sonra tekrar execution plan detaylarını inceleyelim.

Gördüğünüz üzere daha önce çalıştırdığımız BusinessEntityID = 3 sorgusunun haricinde diğer üç sorgu içinde ayrı ayrı execution plan oluşturulmuş ve her plan önbellek üzerinde aynı boyutlarda bir yer kaplamış.

“optimize for ad hoc workloads” özelliğini  aktif edip tekrar sorgularımızı çalıştıralım.  Bu işlem için aşağıdaki sorguyu çalıştırmak yeterli olacaktır.

EXEC sys.sp_configure N’show advanced options’, N’1′  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’optimize for ad hoc workloads’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options’, N’0′  RECONFIGURE WITH OVERRIDE
GO

Sorgu çıktısı;

Tekrar plan cache’lerini tekrar temizleyip sırası ile daha önce çalıştırdığımız sorguları tekrar çalıştıralım.

DBCC FREEPROCCACHE

Yukarıda çalıştırdığımız sorguları tek tek tekrar çalıştıralım ve plan listesini tekrar kontrol edelim.

SELECT * FROM [AdventureWorks2019].[Person].[Person]  Where BusinessEntityID = 3

Özelliğin aktif edilmesi ile birlikte gördüğünüz gibi Complied Plan Stub değerini görüyoruz. Önbellek üzerinde kapladığı boyutta ise düşüş gözlenmektedir.

Aynı sorguyu aynı parametre ile birden fazla sayıda çalıştırıp tekrar plan detaylarını inceleyelim.

Artık Compiled Plan Stub durumu söz konusu değil, yani çalıştırılan sorgunun birden çok çalıştığını -ki örneğimizde 12 kere çalışmış – ve boyutunun bir sonraki çalışmada değişlemeyeceğini biliyoruz.

Peki örneğimizdeki BusinessEntityID bilgisi değiştirip sorguyu tekrar çalıştırırsak ne olacak? BusinessEntityID = 20 olarak sorguyu tekrar çalıştırıyorum.

Farklı bir plan oluşturmuş ve Compiled Plan Stub olarak duruyor. Aynı sorgunun bir sonraki çalışmasında Compiled Plan Stub değeri Compiled Plan olarak değişiyor olacaktır.

Ad Hoc sorguların performans iyileştirilmesi konusunda “Optimize for Ad Hoc Workloads” özelliğinin ne derece etkili olduğunu gözlemlemiş olduk. Yönetimini gerçekleştirdiğiniz sistem üzerinde Ad Hoc sorguları inceleyip, ne sıklık ve ne boyutta olduklarının değerlendirilmesini yaptıktan sonra özelliğin aktif edilmesi konusunda çalışma gerçekleştirebilirsiniz.

Bir sorun halinde sosyal medya hesaplarımızdan bana konu hakkında danışabilir yada sisteminizde yaşadığınız performans sorunlarının iyileştirilmesi adına destek isteyebilirsiniz.

Hepinize data’lı günler dilerim.

Çağlar Özenç
DMC Bilgi Teknolojileri