Problem
The current environment is rather unique, with a unique workload that requires writing things like this. One of those being having the transactions cache on the server take up nearly 10 GBs of our memory. Being that we would like that 10 GBs of memory go towards the Buffer Pool I wrote an automated process to clear the cache when it reaches a certain size. Also being the automation junkie I made it where we could use it for other caches that could be using the same command and of course log to a table just in case we need to track down issues. We eventually added SQL Plans and Lock Manager to the solution.
Step 1
First, we need to create a table to store our information on the caches we would like to clear on an automated basis and populate it with values.
For example, we clear SQL Plans, if we 10,000 plans are Adhoc or Prepared plans that take up 5GBs of memory or Single Used Plans is greater than 10,000 or the memory used for Adhoc or Prepared plans if more than 50% of memory. We clear Transactions cache if is more than 2 GBs and Lock Manager : Node 0 if it is more than 2 GBs.
CREATE TABLE dbo.CacheManagementConfig( CacheManagementConfigID INT IDENTITY(1,1) NOT NULL, CacheName NVARCHAR(50) NULL, [Percentage] TINYINT NULL, PlanCount INT NULL, Size INT NULL, CONSTRAINT PK_CacheManagementConfig_RowID PRIMARY KEY CLUSTERED ( CacheManagementConfigID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ) GO --Possible values that could be inserted INSERT INTO CacheManagementConfig] VALUES ( N'SQL Plans', 50, 10000, 5120 ), ( N'Transactions', NULL, NULL, 2048 ), ( N'Lock Manager : Node 0', NULL, NULL, 2048 )
Step 2
Now we create the stored procedure to handle the logic and a table to store every time it clears one of the things we add it to the table. The procedure in the end calls DBCC FREESYSTEMCACHE. There is a @debug parameter if you pass in 1 that will tell what it cache it will clear on your system if you were to run it without the debug parameter. Then we create a table to log when we clear any cache.
CREATE PROCEDURE dbo.CacheManagement_ClearCache ( @Debug BIT = 0 ) AS SET NOCOUNT ON; DECLARE @CacheName NVARCHAR(50), @Percentage TINYINT, @PlanCount INT, @Size INT, @Clear BIT, @MemoryInUse DECIMAL(19, 3), @SingleUsePlanCount BIGINT, @Percent DECIMAL(6, 3), @WastedMB DECIMAL(19, 3); DECLARE CacheManagement_Cursor CURSOR READ_ONLY FOR SELECT CacheName, Percentage, PlanCount, Size FROM dbo.CacheManagementConfig; OPEN CacheManagement_Cursor FETCH NEXT FROM CacheManagement_Cursor INTO @CacheName, @Percentage, @PlanCount, @Size WHILE ( @@fetch_status <> -1 ) BEGIN IF ( @@fetch_status <> -2 ) BEGIN SET @Clear = 0 IF @CacheName = 'SQL Plans' BEGIN SELECT @MemoryInUse = physical_memory_in_use_kb / 1024 FROM sys.dm_os_process_memory SELECT @WastedMB = SUM(CAST(( CASE WHEN usecounts = 1 AND objtype IN ( 'Adhoc', 'Prepared' ) THEN size_in_bytes ELSE 0 END ) AS DECIMAL(12, 2))) / 1024 / 1024, @SingleUsePlanCount = SUM(CASE WHEN usecounts = 1 AND objtype IN ( 'Adhoc', 'Prepared' ) THEN 1 ELSE 0 END), @Percent = @WastedMB / @MemoryInUse * 100 FROM sys.dm_exec_cached_plans IF @WastedMB >= @Size OR @SingleUsePlanCount >= @PlanCount OR @Percent >= @Percentage BEGIN SET @Clear = 1 IF @Debug = 1 BEGIN SELECT @CacheName CacheName, @WastedMB WastedMB, @Size ConfigSize, @SingleUsePlanCount SingleUsePlanCount, @PlanCount ConfigPlanCount, @Percent [Percent], @Percentage ConfigPercent END IF @Clear = 1 AND @Debug = 0 BEGIN INSERT INTO dbo.CacheManagementLog ( CacheName, SingleUsePlanCount, [Percent], WastedMB, LogDate ) SELECT @CacheName, @SingleUsePlanCount, @Percent, @WastedMB, GETDATE() END END END IF @CacheName = 'Transactions' BEGIN SELECT @MemoryInUse = SUM(pages_kb) / 1024 FROM sys.dm_os_memory_clerks WITH ( NOLOCK ) WHERE name = @CacheName GROUP BY [type], name ORDER BY SUM(pages_kb) DESC OPTION ( RECOMPILE ); IF @MemoryInUse >= @Size BEGIN SET @Clear = 1 END IF @Clear = 1 AND @Debug = 0 BEGIN INSERT INTO dbo.CacheManagementLog ( CacheName, MemoryUsed, LogDate ) SELECT @CacheName, @MemoryInUse, GETDATE() END IF @Debug = 1 BEGIN SELECT @CacheName CacheName, @MemoryInUse MemoryInUse, @Size MaxSize END END IF @CacheName = 'Lock Manager : Node 0' BEGIN SELECT @MemoryInUse = pages_kb / 1024 FROM sys.dm_os_memory_clerks WHERE name = @CacheName IF @MemoryInUse >= @Size BEGIN SET @Clear = 1 END IF @Clear = 1 AND @Debug = 0 BEGIN INSERT INTO dbo.CacheManagementLog ( CacheName, MemoryUsed, LogDate ) SELECT @CacheName, @MemoryInUse, GETDATE() END IF @Debug = 1 BEGIN SELECT @CacheName CacheName, @MemoryInUse MemoryInUse, @Size MaxSize END END IF @Clear = 1 AND @Debug = 0 BEGIN DBCC FREESYSTEMCACHE(@CacheName) END IF @Clear = 1 AND @Debug = 1 BEGIN PRINT 'Clear Cache ' + @CacheName END END FETCH NEXT FROM CacheManagement_Cursor INTO @CacheName, @Percentage, @PlanCount, @Size END CLOSE CacheManagement_Cursor DEALLOCATE CacheManagement_Cursor GO
CREATE TABLE dbo.CacheManagementLog( CacheManagementLogID INT IDENTITY(1,1) NOT NULL, CacheName NVARCHAR(50) NULL, SingleUsePlanCount BIGINT NULL, Percent DECIMAL(6, 3) NULL, WastedMB DECIMAL(19, 3) NULL, MemoryUsed DECIMAL(19, 3) NULL, LogDate SMALLDATETIME NOT NULL, CONSTRAINT PK_CacheManagementLog_RowID PRIMARY KEY CLUSTERED ( CacheManagementLogID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ) GO
Step 3
Now you schedule a SQL Agent job to call the stored procedure CacheManagement_ClearCache. We run ours every hour and this process has been in production for over 3 years without any issues.
Conclusion
I would be curious if anybody finds this useful and has any other use cases for it that we haven’t run across. So please leave comments below.
Hi Tracey
There is a couple of typo’s
In Step1
Line 2 – CacheManagementConfigD –should be CacheManagementConfigID
In Step2 should that be single_pages_kb or multiple_pages_kb? (i used single_pages_kb)
also removed “on Tables” to get mine working.
regards,
Malcolm
I forget not everyone puts their tables outside of PRIMARY so I removed Tables. In Step 2, it should be ran as coded. This a process I wrote 3 years ago with no code modifications.
Hi Tracy,
I see that your using Lock Manager : Node 0. But what about Lock Manager : Node 1? Are there any other caches that your using to flush in addition to the ones above.
Thanks
Steve
I don’t have any others but the way the process is written you could add whatever you need.