SQL Server Cache Plan

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. […]

SQL Server Cache Plan

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

*/
Benzer Yazılar

SQL SERVER RIGHT-LEFT PARTITION

SQL Server 5 gün önce

SQL Server’da partitioning, büyük veritabanı tablolarını daha yönetilebilir ve performanslı hale getirmek amacıyla kullanılan bir tekniktir. Bu teknik, tablonun verilerini fiziksel olarak değil, mantıksal olarak parçalara ayırır. Veriler, belirli bir partition function ve partition scheme kullanılarak farklı bölümlere yönlendirilir. Partitioning, özellikle büyük veri kümeleriyle çalışan veri tabanlarında sorgu performansını artırır ve veri yönetimini kolaylaştırır. Partition Function ve Partition Scheme nedir? Partition Function: Verilerin hangi kriterlere göre bölüneceğini belirler. Örneğin, bir tarih aralığına göre verileri ayırmak. Partition Scheme: Verilerin hangi filegroup’larda depolanacağını belirler.   Örnek olarak Range LEFT ve Range RIGHT olmak üzere iki ayrı tabloda partition nasıl yapılır sizlere göstereceğim. İlk olarak Range LEFT olan partition yapısından başlayacağım. Öncelikle Veri tabanıma yeni filegroup ve file ekliyorum Şimdi sıra FUNCTION ve SCHEME oluşturmakta ben tablomu yıllık olarak partition yaptım sizler ihtiyaçlarınız doğrultusunda aylık,günlük vs yapabilirsiniz. LEFT partition dediğimiz olay vermiş olduğunuz tarih aralığına eşit bir veri geldiğinde bu veriyi solundaki partition […]

Veritabanı Recovery Pending Durumu ve Düzeltme Seçenekleri

SQL Server 2 hafta önce

İçindekilerVeritabanı Recovery Pending Durumu Nedir?Veritabanı Neden Recover Pending Duruma Düşer?Recovery Pending Durumu Nasıl Çözülür?SonuçKaynaklarVeritabanı Recovery Pending Durumu Nedir? SQL Server’da veritabanları bazı nedenlere bağlı olarak “Recovery Pending” (Kurtarma Bekleme)  moduna geçebilir. Veritabanın düzgün bir şekilde kapatılmaması, eksik veya bozuk log dosyaları, disk depolama sorunları, sistemde yaşanan anormal şekilde çökmeler veya MS SQL Server’daki hatalar bu duruma sebep olabilir. Recovery durumu, aslında veritabanını tekrar kullanılabilir hale getirmek için bir kurtarma işlemi yürüttüğünü ifade eder ve üç aşamadan oluşur; Analysis (Analiz): Transaction log incelemesi yapılması ve işlemlerin tamamlanma (Commit) durumunun kontrol edilmesi, Redo (Yeniden İşleme): Tamamlanmış (Commit) ancak henüz diske yazılamamış olan işlemlerin yeniden işlenmesi, Undo (Geri Alma): Başlamış (Begin) ancak tamamlanmamış (Commit) işlemlerin  geri alınmasıdır.   Veritabanı Neden Recover Pending Duruma Düşer? SQL Server Restart Süreci SQL Server servisi restart edildiğinde üzerinde bulunan tüm veritabanları tutarlılığın sağlanması için otomatik olarak recovery moduna girer ve redo/undo işlemleri sürecince devam eder. Ani Sistem […]

SQL Server DMV ve DMF – 6

SQL Server 2 hafta önce

Bu yazımızda DMV ve DMF Serimizin 6.sına devam edeceğiz. Bir önceki seride Memory’ye ilişkin DMV ve DMF’leri ele almıştık. Bu yazıda Memory konusunda devam edeceğiz. SQL server’da Memory kavramı en önemli kavramlardan biridir. Özellikle tüm transaction işlemlerinin önce Buffer sonra disk üzerinden devam ettiğini düşünürsek buffer’ın oynadığı kritik rolü daha iyi anlayabiliriz. Bu yazıda Memory’nin durumunu ve monitör edilmesine bakacağız. Özellikle Performans sorunlarında memory konusunda sorun yaşandığı durumda nasıl okumak gerektiği önemli rol oynamaktadır. Hangi database’de, hangi tablo’da sorun yaşandığına ilişkin bilgilere bu paylaşım sonrasında görebileceğiz. SQL Server’ın Memory kullanım durumunu incelediğimde; select physical_memory_in_use_kb/1048576.0 AS ‘physical_memory_in_use (GB)’, locked_page_allocations_kb/1048576.0 AS ‘locked_page_allocations (GB)’, virtual_address_space_committed_kb/1048576.0 AS ‘virtual_address_space_committed (GB)’, available_commit_limit_kb/1048576.0 AS ‘available_commit_limit (GB)’, page_fault_count as ‘page_fault_count’ from  sys.dm_os_process_memory; Görsel – 1   Physical_memory_in_use: Kullanımda olan Fiziksel Memory miktarını gösterir. locked_page_allocations: Memory’de lock’lanmış olan Page’lerin miktarını belirtir. virtual_address_space_contained: SQL Server VAS(Virtual Adress Space) için ayrılan miktarı belirtir. available_commit_limit: SQL Server tarafından kullanılabilecek Memory Miktarını gösterir. […]

0 Yorum

Yorum Yaz

Rastgele