Birçok konu gibi, bu konuda çok uzun ve derin bir konu. Fakat temel olarak bilmenizde fayda olan birkaç şeyi paylaşmaya çalışacağım sizlerle.
SQL Server'da çalıştırdığınız sorgular, sorgunun tipine göre Adhoc, Prepared, Proc... gibi Cache tipi şeklinde kaydedilir. Adhoc sorguların planları çoğu zaman sadece 1 kere kullanılır. Eğer sisteminizde çok fazla Adhoc sorgu varsa, muhtemelen hem RAM kaynağınızı hem de CPU kaynağınızı ve haliyle de IO kaynağınızı verimli kullanamıyorsunuz demektir. Bununla birlikte eğer dinamik SQL kullanıyorsanız bu sorunlara SQL Injection gibi olası güvenlik sorunları da eklenir. Önceden de dediğim gibi bu yazımda bunların ayrıntısına girmeyeceğim, size Adhoc sorguların Procedure Cache'te ne kadar yer kapladığını nasıl kontrol edebileceğinizi ve bu sorunla nasıl başa çıkabileceğinizi aktaracağım. Aktaracağım diyorum, çünkü bu konudaki birçok şeyi ben de Kimberly L. Tripp'ten öğrendim.
Adhoc sorguya örnek vermeden geçmek istemem:
SELECT * FROM Tablom WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom T WHERE AlanAdim LIKE '%xxx%'
SELECT * FROM Tablom WHERE AlanAdim LIKE '%xxx%'
Her ne kadar tüm bu sorgular birebir aynı işi yapıyor olsa da, hepsi için de farklı planlar oluşturulur. Adhoc sorgular içerisindeki bir boşluk farkı bile farklı plan yaratılmasına neden olur. Eğer "Optimize for Adhoc Workloads" özelliğini de kullanmıyorsanız, o zaman Procedure Cache'inizde hiç kullanılmayacak olan MB'larca ve hatta GB'larca çöp Execution Plan olabilir. İşte ben de size bunları nasıl görebileceğinizi ve bunlardan kurtulabileceğinizden bahsedeceğim.
Öncelikle "Optimize for Adhoc Workloads" seçeneğinden bahsedeyim. Bu seçenek ile, her Adhoc sorgu çalıştırılışında yeni bir plan oluşturulmasını kısmen de olsa engellersiniz. Bu sayede plan yine oluşturulur, fakat çok özet bir şekilde oluşturulur ve KB'larca ve bazen MB'larca yer kaplayacağına (evet, bir Execution Plan'ın boyutu MB'ları bulabilir ve eğer benzer Adhoc sorgular çok fazla çalıştırılırsa sıkıntı yaşarsınız) Byte'lar ölçüsünde yer kaplar. Eğer aynı sorgu birebir şekilde 2. kez çalıştırılırsa, tam Execution Plan'ı o zaman oluşur. Bu özelliği aşağıdaki şekilde etkinleştirebilirsiniz. Sistemlerin pek çoğu için bu özelliğin kullanılmasında büyük fayda vardır ve varsayılan olarak kapalıdır.
sp_configure 'optim', 1
RECONFIGURE
Not: Sanılanın aksine sp_configure ile kullandığınız parametreleri birebir yazmak zorunda değilsiniz, bu şekilde diğer seçenekler arasında eşsiz olacak şekilde kısalttığınızda da kullanabilirsiniz.
Procedure Cache'inizdeki planları Cache tiplerine göre gruplayıp, kullanım oranlarıyla ve plan boyutlarıyla listelemeyi aşağıdaki sorgu ile yapabilirsiniz:
SELECT
[Cache Type] = cp.objtype,
[Total Plans] = COUNT_BIG (*),
[Total MBs] = SUM(CAST (cp.size_in_bytes AS DECIMAL (18,2))) / 1024 / 1024, [Avg Use Count] = AVG(cp.usecounts),
[Total MBs - USE Count 1] = SUM(CAST((CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) AS DECIMAL(18,2))) / 1024 / 1024,
[Total Plans - USE Count 1] = SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END),
[Percent Wasted] = (SUM(CAST((CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) AS DECIMAL(18,2))) / SUM (cp.size_in_bytes)) * 100
FROM sys.dm_exec_cached_plans AS cp
GROUP BY cp.objtype
ORDER BY [Total MBs - USE Count 1] DESC;
Bu sorguyu bir Production ortamında çalıştırdığınızda aşağıdaki gibi bir sonuç alırsınız:
Bu ekran görüntüsünde görebileceğiniz gibi, her ne kadar bu sistemde de "Optimize for Adhoc Workloads" özelliği kullanılsa da, en çok plan Adhoc. Bunun temel nedeni de Dinamik SQL kullanımıdır ve doğru Covered Index'lerin kullanılmıyor oluşudur. Birçok sistemde (EXEC ... ile çalıştırılan sorgular Dinamik SQL'dir) dinamik SQL kullanmak kaçınılmazdır ve yerine göre doğrudur da. Fakat olabildiği kadar çok Stored Procedure ve sp_executesql kullanılmalıdır. Çünkü bu sorgular parametre alırlar ve bu sayede doğru Execution Planlar oluşturulabilir. Bu Execution Planlar ise Adhoc sorgularda olduğu gibi boşluk farkından bile tekrar kullanılamayacak planlar değildir. Eğer sisteminizde çok fazla Adhoc plan varsa, o zaman bunun için bir şeyler yapmanızda fayda var. Örneğin dinamik sorgularınızı olabildiğince Stored Procedure ve sp_executesql komutlarıyla yeniden yazabilirsiniz ve mümkün olan sorgular için Covered Index'ler oluşturabilirsiniz. Bu şekilde Adhoc planları olabildiğince azaltmış olursunuz. Eğer her şeye rağmen Adhoc sorguları yeterince azaltamıyorsanız, o zaman ne yapabileceğinizi aşağıda anlatacağım, fakat önce Adhoc sorguların gereğinden fazla olduğunu nasıl hesaplayabileceğinizden bahsedeyim biraz.
Yukarıdaki ekran görüntüsünden görebileceğiniz gibi Adhoc Plan'lar sistemde toplam 1.7GB yer kaplıyor. Bu miktarın 1.2GB'lık bölümü ise sadece ve sadece 1 kere kullanılmış olan Adhoc Plan'lara ait. Yani Adhoc planların %70'i çöp plan. Bu miktar zaman zaman çok daha fazla olabiliyor. Örneğin toplamda bir SQL Server Instance'ı için Max Server Mem ayarını 64GB yaparsanız ve Procedure Cache'teki Adhoc Planların miktarı 1-2GB ise, bu çok olarak sayılabilir. Eğer Covered Index oluşturmak, dinamik sorguları Stored Procedure ve sp_executesql ile yeniden yazmak gibi yöntemleri kullanamıyorsanız veya her şeye rağmen böyle bir sonuçla karşılaşıyorsanız o zaman DBCC FREESYSTEMCACHE ('SQL Plans') komutuyla Adhoc sorguları Procedure Cache'ten temizleyebilirsiniz. Tabii ki bunu bir sefer yapmanız tüm sorununuzu çözmeyecektir. Bunu zaman zaman uygulamak gerek. Yine Kimberly L. Tripp'in bu konuda yazdığı bir
makale var. Bu makalede, çöp plan miktarı ölçülerek gerektiğinde bu DBCC komutunun bir SQL Agent Job'ı yardımıyla düzenli bir şekilde nasıl çalıştırılacağı da anlatılıyor.
Bir sistemde DBCC FREESYSTEMCACHE('SQL Plans') çalıştırıldıktan sonra Procedure Cache'in ne kadar düştüğünü aşağıdaki ekran görüntüsünden görebilirsiniz. Bunu bir Production ortamından aldım:
Gördüğünüz gibi DBCC FREESYSTEMCACHE('SQL Plans') komutunu çalıştırmadan önce Plan Cache'imin boyutu 2.6GB boyutlarındaydı. Bu komutu çalıştırdıktan ve Adhoc Planlardan kurtulduktan sonra 380MB'lara düştü. Komutun çalışmasının ardından tekrar okun yukarı doğru tırmandığını görebilirsiniz. Bunun nedeni de bu komutun sadece anlık olarak işe yaradığının ve zaman zaman çalıştırılması gerektiğinin göstergesidir. Procedure Cache'inizde gereksiz, bir daha kullanılmayacak olan planların durmasını istemezsiniz. Önceden de dediğim gibi bu sorunun kalıcı çözümü, doğru Covered Index'lerin oluşturulması, olabildiğince çok Stored Procedure ve sp_executesql kullanılmasıdır; fakat kaçınılmaz durumlarda da DBCC FREESYSTEMCACHE('SQL Plans') kullanabiliriz.
Son olarak, DBCC FREESYSTEMCACHE'i lütfen DBCC FREEPROCCACHE ile karıştırmayın. DBCC FREESYSTEMCACHE('SQL Plans') komutuyla sadece ve sadece Adhoc planlarını temizlemiş olursunuz. Stored Procedure, Trigger vs. Planlarını silmiş olmazsınız. DBCC FREEPROCCACHE komutunu ise çok gerekmedikçe kesinlikle bir üretim ortamında çalıştırmanızı tavsiye etmem. Çünkü Execution Plan oluşturulması zaten çok CPU maliyetli bir iştir ve bunun tekrar tekrar yapılmasını istemezsiniz.
Ekrem Önsoy
