SQL Server Plan Cache Temizlemenin 9 Farklı Yolu

SQL Server Cache Temizleme Yöntemleri Bu yazımızda SQL Server’da cache problemleri ile karşılaştığımızda hangi yöntemle nasıl çözümler üretebileceğimizin üzerinde duracağız. Bu işlem sistemde öngörülemeyen sorunlara yol açabileceğinden, komutları çalıştırmadan önce uzmanlardan yardım almanızda fayda olacaktır.   Cache üzerinde sorun yaşamamıza neden olacak bazı durumlar vardır; Query’lerde çok fazla Recompile kullanımı. Özellikle Store Procedure içinde yer alan Option Recompile kullanımı. Dynamic T-SQL code’ların çok fazla kullanımı. SQL Server Instance’ın ihtiyacı olan memory’den daha az memory verilmesi. Uzun süre çalışan ve size olarak çok fazla data çeken query’ler Index’siz kullanılan query’ler. Birden fazla SQL Server Instance kullanımında var olan Memory’den daha fazla memory kullanımına izin verilmesi.   Yukardaki durumlar memory dar boğazı yaşamamıza neden olacaktır ve bunun sonucunda cache temizleme ihtiyacı duyacağımız durumlar ortaya çıkması olasıdır. Cache temizlemenin birçok yöntemi vardır. Bu yöntemleri birlikte ele alacağız.   1-) DBCC FREEPROCCACHE Bu komut yardımı ile bir Resource pool’u ya da specific bir planı […]

SQL Server Plan Cache Temizlemenin 9 Farklı Yolu

SQL Server Cache Temizleme Yöntemleri

Bu yazımızda SQL Server’da cache problemleri ile karşılaştığımızda hangi yöntemle nasıl çözümler üretebileceğimizin üzerinde duracağız.

Bu işlem sistemde öngörülemeyen sorunlara yol açabileceğinden, komutları çalıştırmadan önce uzmanlardan yardım almanızda fayda olacaktır.

 

Cache üzerinde sorun yaşamamıza neden olacak bazı durumlar vardır;

  • Query’lerde çok fazla Recompile kullanımı. Özellikle Store Procedure içinde yer alan Option Recompile kullanımı.
  • Dynamic T-SQL code’ların çok fazla kullanımı.
  • SQL Server Instance’ın ihtiyacı olan memory’den daha az memory verilmesi.
  • Uzun süre çalışan ve size olarak çok fazla data çeken query’ler
  • Index’siz kullanılan query’ler.
  • Birden fazla SQL Server Instance kullanımında var olan Memory’den daha fazla memory kullanımına izin verilmesi.

 

Yukardaki durumlar memory dar boğazı yaşamamıza neden olacaktır ve bunun sonucunda cache temizleme ihtiyacı duyacağımız durumlar ortaya çıkması olasıdır.

Cache temizlemenin birçok yöntemi vardır. Bu yöntemleri birlikte ele alacağız.

 

1-) DBCC FREEPROCCACHE

Bu komut yardımı ile bir Resource pool’u ya da specific bir planı temizleyebilirsiniz. Ya da cache’deki tüm sql planlarıda temizleyebilirsiniz.

 

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

 

---Resource Pool Bilgisini Getirir.

SELECT name AS [Resource Pool Name], cache_memory_kb/1024.0 AS [cache_memory (MB)],

used_memory_kb/1024.0 AS [used_memory (MB)]

FROM sys.dm_resource_governor_resource_pools;

 

–Bir Resource Pool İçerisindeki plan cache’leri Flush eder. Örnek olarak https://www.veritabani.org/sql-server-resource-governor/ makalesindeki pool’u kullandık.

DBCC FREEPROCCACHE ('RaporPool');

GO

Görsel 1

 

–Specific bir sorguya ya da store procedure ait sorgunun plan cache’ni temizlemek istediğimizde

 

--Store Procedure ait plan cache için bir kaç kez çalıştırıp plan üretiyorum

EXEC [dbo].[uspGetManagerEmployees] 243

 

--Bu query'e ait plan handle buluyorum.

--OPTION(RECOMPILE) ile plan cache'den gelen query'i tutuyorum.

use AdventureWorks2022

SELECT cp.plan_handle , cp.objtype, cp.usecounts

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE OBJECT_NAME (st.objectid) LIKE '%uspGetManagerEmployees%'

OPTION (RECOMPILE);

 

–Store Procedure’in cache kullandığı plan handle bulduktan sonra remove ediyorum.

DBCC FREEPROCCACHE (0x05000D006049BE2360EA367C2502000001000000000000000000000000000000000000000000000000000000);

 

--Instance altında bulunan tüm planları temizlemek istersek

DBCC FREEPROCCACHE

 

--Çalıştırdıktan sonra bize mesaj vermesini istemiyorsak

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

2-) DBCC FREESYSTEMCACHE

 

DBCC FREESYSTEMCACHE( 'ALL' [, pool_name ] ) [WITH { [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ]]

 

–ad hoc ve prepared query’lerin plan cache’lerini flush eder.

DBCC FREESYSTEMCACHE ('SQL Plans');

 

SELECT

dm_exec_sql_text.text,

dm_exec_cached_plans.objtype,

dm_exec_cached_plans.size_in_bytes,

dm_exec_query_plan.query_plan

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(dm_exec_cached_plans.plan_handle)

CROSS APPLY sys.dm_exec_query_plan(dm_exec_cached_plans.plan_handle)

WHERE dm_exec_cached_plans.cacheobjtype = N'Compiled Plan'

AND dm_exec_cached_plans.objtype IN(N'Adhoc', N'Prepared')

AND dm_exec_cached_plans.usecounts = 1

ORDER BY dm_exec_cached_plans.size_in_bytes DESC;

 

Görsel 2

–Cache’de kullanılmayan plan’ları temizler.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

 

–Bir Resource pool içindeki ad hoc ve prepared planları temizlemek istersek

DBCC FREESYSTEMCACHE ('SQL Plans', 'RaporPool');

 

–Cache’deki Temp table ve table variable’ların planlarını temizler

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

 

–Tempdb’deki tüm plan cache’leri temizler

DBCC FREESYSTEMCACHE ('tempdb')

 

–İlgili Database’in tüm plan cache’lerini temizler.

DBCC FREESYSTEMCACHE ('userdatabase')

 

3-) DBCC FLUSHPROCINDB

–Database’in adından ait olduğu ID çekiyoruz.

DECLARE @intDBID INT;

SET @intDBID = (SELECT [dbid]

FROM master.dbo.sysdatabases

WHERE name = N'AdventureWorks2022');

 

–Bir Database ait ID’si üzerinden plan cache’leri flush eder.(SQL Azure’da bu komut çalışmaz.)

DBCC FLUSHPROCINDB (@intDBID);

 

4-) ALTER DATABASE  WITH T-SQL script

–Mevcut database’in cache plan’larını temizler.

–Bu özellike Sql Server 2016 ve sonrası ile birlikte, azure’da da mevcut.

USE AdventureWorks2022;

GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

 

5-) DBCC FREESESSIONCACHE

–Server üzerindeki SQL Server, Redis, NCache vb. distrubuted implementasyonların cachelerini temizler.

DBCC FREESESSIONCACHE

 

6-) DBCC FLUSHAUTHCACHE

–Bu komut oturum açmak için kullanılan Firewall ve login bilgilerini cache’ten temizlemek için kullanılır. Kullanımı daha çok Azure üzerindeki cache’ler içindir. Kullanıcı bilgileri master database’inde bulunduğu için çalıştırmak sakıncalı olabilir.

DBCC FLUSHAUTHCACHE

 

7-) Using sp_recompile

–Specific bir object’in(procedure,tablo,trigger,view,function etc.) planını temizlemek için kullanılır.

EXEC sp_recompile N'Object';

EXEC sp_recompile N'uspGetManagerEmployees';

 

8 -) DBCC DROPCLEANBUFFERS

 

DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

DBCC DROPCLEANBUFFERS ( COMPUTE | ALL ) [ WITH NO_INFOMSGS ]

 

–Database’lerin dirty ve clean page’leri hakkında bilgi verir.

SELECT *,

[DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],

[CleanPageCount] * 8 / 1024 AS [CleanPageMB]

FROM

(SELECT

(CASE WHEN ([database_id] = 32767)

THEN N'Resource Database'

ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

SUM (CASE WHEN ([is_modified] = 1)

THEN 1 ELSE 0 END) AS [DirtyPageCount],

SUM (CASE WHEN ([is_modified] = 1)

THEN 0 ELSE 1 END) AS [CleanPageCount]

FROM sys.dm_os_buffer_descriptors

GROUP BY [database_id]) AS [buffers]

ORDER BY [DatabaseName]

GO

 

–İlgili database’in buffer pool’undan buffer’ı, columstore object pool’unda columstore object’leri temizlemek için kullanılır. Başlangıçta Chekpoint kullanarak dirty page’leri yazmak cold buffer üretmek için faydalı olacaktır.

Use AdventureWorks2022

GO

CHECKPOINT

GO

DBCC DROPCLEANBUFFERS

 

9-) ALTER COMMAND WITH T-SQL

Procedure, view, funtion gibi objetlerin var olan planlarını temizlemek için iligli object’i alter etmemiz yeterli olacaktır.

 

Örnek Procedure;

USE [AdventureWorks2022]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]

@BusinessEntityID [int]

AS

BEGIN

SET NOCOUNT ON;

-- Your query

END;

 

 

Benzer Yazılar

Contained Availibility Group – SQL Server 2022 Feature

SQL Server 6 saniye önce

Contained Availibility Group – SQL Server 2022 Feature Bu Yazımızda SQL Server 2022’nin Feature’larından olan Contained Availablity Group’tan bahsedeceğiz. SQL Server Always on ile birlikte birçok kullanıcı oluşturdukları Agent Job ve User’larda zaman zaman problemler ile karşılaşmaktadır. Özellikle hangi job’un primary’de hangi Job’un secondary’de çalışacağı konusu zaman zaman problemler yaratmaktadır. Tamda bu noktada SQL Server 2022 Contained AG özelliği ile bu probleme çözüm getirmek için hayatımıza girdi. Yazımızda adım adım nasıl Contained AG oluştrulur ve oluştururken nelere dikkat etmeliyiz bunları tek tek ele alacağız. 1.Adım: Yeni bir Availablity Group Wizard oluşturuyoruz. Görsel 1: New Availability Group Wizard   2.Adım: Yeni bir AG oluşturuyoruz. Burada önemli nokta Contained seçeneğinin seçilmesi. Reuse seçeneğini yeni oluşturduğumuz Contained AG’de seçmiyoruz Reuse’un kullanımı ile alakalı yazı içerisinde detaylı olarak paylaşacağız. Görsel 2: New Availability Group as Contained   3.Adım: Hangi database’in Contained AG içerisinde olacağını belirliyoruz. Biz örneğimizde ContainedDB’yi örnek olarak ele alacağız. Görsel 3: […]

SQL Server DMV ve DMF – 7

SQL Server 1 hafta önce

Bu yazıda SQL Server DMV ve DMF Serimizin 7.’sine devam edeceğiz. Bu başlık altında Memory özelinde çokça kullandığımız performans counter’lara göz gezdireceğiz. Ve bu çıktıların ne olduklarına bakacağız. Memory ile ilişkili Performance Counter’lara göz gezdirmek istediğimizde; — Get size of SQL Server Page in bytes DECLARE @pg_size INT, @Instancename varchar(50) SELECT @pg_size = low from master..spt_values where number = 1 and type = ‘E’   — Extract perfmon counters to a temporary table IF OBJECT_ID(‘tempdb..#perfmon_counters’) is not null DROP TABLE #perfmon_counters SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters;   — Get SQL Server instance name as it require for capturing Buffer Cache hit Ratio SELECT  @Instancename = LEFT([object_name], (CHARINDEX(‘:’,[object_name]))) FROM    #perfmon_counters WHERE   counter_name = ‘Buffer cache hit ratio’;     SELECT * FROM ( SELECT  ‘Total Server Memory (GB)’ as Cntr, (cntr_value/1048576.0) AS Value FROM    #perfmon_counters WHERE   counter_name = ‘Total Server Memory (KB)’ UNION ALL SELECT  ‘Target Server Memory (GB)’, (cntr_value/1048576.0) […]

INTEGRATION SERVICES KALDIRMA/KURMA ADIMLARI VE SENARYOLARI

SQL Server 1 hafta önce

Bu makalede SQL Server Integration Services kurulu olan bir ortamdan servisi kaldırma adımlarından ve bunun mevcuttaki ortama olan etkilerinden bahsedeceğim. Bizler genel olarak kurulum ve bu kurulumların adımları ilgileniriz ancak bugün bunun tam tersi mevcutta SQL Server 2019 kurulu olan bir servisi kaldıracağız. Denetim masası (Control Panel) > Programs > Programs and Features Adımları ile mevcut ortamda kurulu olan uygulamaları görüntüledikten sonra “Microsoft SQL Server 2019” tıklayarak “Uninstall/Changes” tıklıyoruz. Görsel – 1 Karşımıza SQL Server ne yapmak istediğimizi soruyor biz bir servis kaldıracağımız için “Remove” diyerek ilerliyoruz. Görsel – 2 Yapmak istediğimiz işlemi hangi Instance üzerinde yapacağımızı soruyor, ben “TEST” Instance da yapacağım seçerek ilerliyorum. Görsel – 3 Şimdide Bu Instance üzerinde neyi kaldırabileceğimi bana gösteriyor, ben Integration Services kaldıracağım için Shared Features menüsü altında bulunan Integration Services seçiyorum burada unutulmaması gereken bir şey var eğer Shared Features altında bulunan bir service kaldırır veya kurarsanız bundan sadece seçili olan Instance […]

0 Yorum

Yorum Yaz

Rastgele