Bu yazımızda sql server’da arka planda kullanılan, kaynak yönetimini ve sorgularımızı doğrudan ilgilendiren “Cache Plan” hakkında bilgileri ele alacağız.
SQL Server’da bir sorgu ilk kez çalıştırıldığında derlenir (compile) ve query için bir plan oluşturulur. Bu plan sayesiyle sorgu hangi adımları izleyeceğini plana göre gerçekleştirir. Ve bu plan SQL Server’da Plan Cache içerisinde depolanır.
Görsel -1
Plan cache içerisinde depolanan planlar sayesinde query tekrar geldiğinde yeni bir plan oluşturmasına gerek kalmaz ve aynı planı kullanarak sonucu geri döndürür.
Bu sorgu planının ön bellekte kalma süresi sorgunun ne kadar kullanıldığı ile alakalıdır. Daha sık kullanılan sorgular plan cache’te daha fazla kalır.
Plan Cache içerisinde tutabileceğim maximum plan sayısı 160.036. SQL Plans, Object Plans, Bound Trees, Extended SPs her biri için bu rakam 40.009’dur.
Yoğun ad hoc sorguları SQL Plan bucket sayımızı arttırabilir ve bu durum SOS_CACHESTORE spinlock contention yaşamamıza neden olabilir. Bu durumun üstesinden -T174 Trace flag ekleyerek sayıyı 4 katına çıkarabiliriz.
Size olarak ise;
0-4 GB memory’e sahip olduğunuz server üzerinde, maksimum %75’e ulaşabilirsiniz.
4-64 GB memory’e sahip olduğunuz server üzerinde, maksimum +%10’a ulaşabilirsiniz.
64 GB üzerinde memory’e sahip olduğunuz server üzerinde, maksimum +%25’e ulaşabilirsiniz.
Görsel – 2
Cache size değerini kontrol etmek için bir başka değiştirebileceğimiz değer ise sp_configure içerisinde “Optimize for ad hoc workload” değeri default option değeri 0’dır. Bu değeri 1’e setlediğimizde plan cache adhoc query’ler için plan cache size değerini arttırmış olacağız. Aşağıdaki sorgu ile bu değerin açık olup olmadığını kontrol edebilirsiniz.
Görsel – 3
USE master GO SELECT CASE value_in_use WHEN 0 THEN 'Optimize for Ad Hoc Workloads is Turned Off' WHEN 1 THEN 'Optimize for Ad Hoc Workloads is Turned On' END AS [Optimize for Ad Hoc Workloads Current Status] FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'; GO
Görsel – 4
Bu değeri açmak istediğinizde aşağıdaki sp_configure komutlarını kullanarak açabilirsiniz. Fakat bu değeri açmadan önce kullanılan plan size ve sayısını kontrol etmenizde fayda var. Aşağıdaki script yardımı ile plan cache limit değerlerini kontol edebilirsiniz.
SELECT objtype AS [CacheType], COUNT_BIG(*) AS [Total Plans], SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs], AVG(usecounts) AS [Avg Use Count], SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1], SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1] FROM sys.dm_exec_cached_plans GROUP BY objtype ORDER BY [Total MBs – USE Count 1] DESC GO
veya
-- Plan cache contents and memory use by object type SELECT objtype, COUNT_BIG(*) AS plan_count, SUM(CAST(size_in_bytes AS DECIMAL(12, 2)))/(1024*1024) AS size_mb, AVG(usecounts) AS avg_use_counts, SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans, SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS DECIMAL(12, 2)))/(1024*1024) AS single_use_mb FROM sys.dm_exec_cached_plans WHERE cacheobjtype = N'Compiled Plan' GROUP BY objtype;
“Optimize for ad hoc workloads” değerini aşağıdaki komutlar yardımı ile aktif edebiliriz;
sp_configure 'show advanced options',1; RECONFIGURE; GO sp_configure 'optimize for ad hoc workloads',1; RECONFIGURE; GO sp_configure 'show advanced options',0; RECONFIGURE; GO
Plan Cache dar boğazı yaşadığımızda bazı durumlarda cache boşaltmak bize avantaj sağlayabilir. Aşağıdaki sorgu ile single -use plan cache’ten dolayı, memory’i temizleme ihtiyacımız olup olmadığını bize bilgisini verecektir.
DECLARE @MB decimal(19,3)
, @Count bigint
, @StrMB nvarchar(20)
SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024
, @Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END)
, @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans
IF @MB > 10
BEGIN
DBCC FREESYSTEMCACHE('SQL Plans')
RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB)
END
ELSE
BEGIN
RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
END
Go
Bu işlemi otomatik olarak cache planların temizlenme işlemi aşağıdaki sorgu kullanılabilir. Ama bunun sisteme yük getireceğini unutmamak gereklidir.
DECLARE @MB decimal(19,3)
, @Count bigint
, @StrMB nvarchar(20)
SELECT @MB = sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024
, @Count = sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)
, @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans
IF @MB > 1000
DBCC FREEPROCCACHE
ELSE
RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
Go
Bu işlemi otomatik hale getirmek isterseniz, aşağıdaki Script’ten faydalanabilirsiniz.
https://www.sqlskills.com/blogs/kimberly/wp-content/uploads/2010/4/sp_SQLskills_CheckPlanCache.sql
Yukarıdaki scriptler yardımı ile planları temizleyeceğizdir fakat planları temizlemek bize yoğun sistemlerde dar boğaz yaratabilmektedir. Bunun sebebi ise her gelen sorgunun tekrardan yeni plan oluşturması ve bununda sistemde memory kullanımını arttırması demektir. Bu durumun sonucu bize CPU bottleneck olarak yansıyacaktır. Tüm Bu işlemleri Instance, Database ve Session bazında kontrol edip temizleme işlemlerini gerçekleştirebiliriz.
Tüm Db’lerdeki planları temizlemek istediğimizde ;
DBCC Freeproccache()
Speficif bir tipteki(object plans, SQL Plans etc.) planları temizlemek istediğimizde;
DBCC FREESYSTEMCACHE('SQL Plans');
Tek bir DB’deki Planları temizlemek istediğimizde;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Spesifik bir plana ait planı temizlemek istediğimizde;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE(<plan_handle>);
Plan Cache içerisinde neler barındırır dediğimizde;
Object Plan’lar: Store Procedure’ler, Function’lar ve Trigger’lar.
SQL Plans’lar: Prepared Plan’lar, Adhoc Plan’lar.
Diğer : Bound tree’ler ve Extented Store procedure Reference’leri.
Granularity: Statement Başına (per-statement) execution planlar. Object değil.
Statement başına Planlar (Plans per Statement): single statment için eğer farklı execution context’lere sahip ise multiple plan oluşturulabilir. Veya bir paralel plan üretilmiş olabilir.
Cache plan ile ilişkili olan Dynamic Management Object’ler bize hangi sorgunun hangi plan ile kaç kez çalıştırıldığı bilgisini verecektir. Aşağıdaki scirpt bize bu konuda faydalı olacaktır.
SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan, qstats.execution_count FROM sys.dm_exec_cached_plans AS cplan CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan Inner Join sys.dm_exec_query_stats AS qstats on cplan.plan_handle = qstats.plan_handle ORDER BY cplan.usecounts DESC
Bu Dynamic Management Object’lerin kendi aralarındaki ilişkin yapısını Görsel – 5 üzerinde inceleyebilirsiniz.
Görsel – 5
Database özelinde ve hangi query’nin nasıl bir performans tüketimi yaptığı konusunda aşağıdaki script bize fayda sağlayacaktır.
SELECT CASE st.dbid WHEN 32767 THEN 'resourcedb' WHEN NULL THEN 'NA' ELSE DB_NAME(st.dbid)END AS [database], OBJECT_NAME(st.objectid) AS object_name, SUBSTRING( st.text, ( qs.statement_start_offset / 2 ) + 1, (( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) AS sql_statement, qs.exec_count, qs.avg_logical_reads, qs.avg_CPU_ms, qs.avg_time_ms, qs.avg_logical_writes, qp.query_plan FROM ( SELECT TOP ( 10 ) plan_handle, statement_start_offset, statement_end_offset, execution_count AS exec_count, total_worker_time / ( execution_count * 1000 ) AS avg_CPU_ms, ( total_elapsed_time / ( execution_count * 1000 )) AS avg_time_ms, CASE WHEN total_logical_reads > 0 THEN ( total_logical_reads / execution_count ) ELSE 0 END AS avg_logical_reads, CASE WHEN total_logical_writes > 0 THEN ( total_logical_writes / execution_count ) ELSE 0 END AS avg_logical_writes FROM sys.dm_exec_query_stats ORDER BY ( total_logical_reads / execution_count ) DESC ) AS qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.avg_logical_reads DESC OPTION ( RECOMPILE );
AdHoc Query’de aynı sorguların farklı plan kullandığını örneklemek istersek aşağıdaki örnek bizim için güzel bir örnek olacaktır. Bu sayede bize aynı sorgunun farklı plan kullandığı ve bize single-use plan kullanımının nasıl olduğu ve cache size sorununa nasıl sebep olduğunu daha iyi anlayabiliriz.
SELECT HASHBYTES('MD5','SELECT * FROM Person.Person') UNION ALL
SELECT HASHBYTES('MD5','SELECT * FROM person.Person') UNION ALL
SELECT HASHBYTES('MD5','SELECT * FROM Person.Person')
/*
0xF2D4F28DA93156A5BB487B019F1F0191
0x76F700BB3DC09FF482E1E4A77C7392E8
0xB1D875A858F4D410D9E866C40E523683
*/
0 Yorum