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;
0 Yorum