Cleaning up Troublesome SQL Server Caches Using Up Memory

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.

Related Posts

5 thoughts on “Cleaning up Troublesome SQL Server Caches Using Up Memory

  1. 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

  2. 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.

  3. 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

Leave a Reply to Tracy BoggianoCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.