SQL Server - Free Cache

Conner_Wood

Long story short, SCSM was really really slow.  While a restart of the SQL Server or failover will fix the issue if it's a memory leak type of deal (like cache not being freed), it was time consuming and I don't want to have to mess around with that.

Therefore, I came up with an SQL script to meet the business requirements of keeping SCSM responsive by ensuring the SQL server hosting the LIVE ServiceManager database would free up memory being used or data cached when it no longer needs to be.

I decided to free the SQL cache once a day during off-hours as to not impact busy times.  Feel free to run the SQL query on each of your servers hosting your SCSM Live Database.  It will create a SQL Server Agent Job called "Clear SQL Cache" which you can then easily dig into.  Note if you named your Live SCSM database something besides ServiceManager, you will need to correctly update the SQL being executed.

USE [msdb]
GO

/****** Object:  Job [Clear SQL Cache]    Script Date: 12/5/2017 11:48:47 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 12/5/2017 11:48:47 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Clear SQL Cache', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Clears SQL Cache on the Primary Server hosting ServiceManager database, Preventative Maintenance to not slow down SCSM Tickets being saved in the Console as that should not have to take 30 seconds.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'NT AUTHORITY\SYSTEM', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Free Cache SQL]    Script Date: 12/5/2017 11:48:48 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Free Cache SQL', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @DatabaseName nvarchar(255) = ''ServiceManager'';
DECLARE @DatabaseUpdateability nvarchar(128) = CONVERT(nvarchar(128),DATABASEPROPERTYEX(@DatabaseName, ''Updateability''));

--If database property IS NULL, END SCRIPT
IF (@DatabaseUpdateability IS NULL)
BEGIN
	PRINT ''@DatabaseUpdateability IS NULL. Database "'' + @DatabaseName + ''" not Found or is Unavailable to Query on Server. No step run''
	RETURN
END

IF (@DatabaseUpdateability = ''READ_WRITE'')
BEGIN
	--READ_WRITE = Data can be read and modified.
	--Database exists and is READ_WRITE, however ensure database is not a secondary on this server if using AlwaysOn
	DECLARE @IsDatabaseOnServerSecondary bit;
	SET @IsDatabaseOnServerSecondary = 
	(
		SELECT (CASE WHEN COUNT(R.[replica_id]) = 0 THEN 0 ELSE 1 END) 
		FROM [sys].[dm_hadr_availability_replica_states] R (nolock)

		JOIN [sys].[dm_hadr_database_replica_cluster_states] C (nolock)
		ON  C.[replica_id] = R.[replica_id]
		AND C.[database_name] LIKE @DatabaseName

		WHERE [is_local] = 1 AND [role] = 2
	);
	--If Database is secondary
	IF (@IsDatabaseOnServerSecondary = 1)
	BEGIN
		PRINT ''@DatabaseUpdateability == '' + @DatabaseUpdateability + '' BUT DATABASE "'' + @DatabaseName + ''" IS CURRENTLY SECONDARY. No step run''
	END
	ELSE BEGIN
		--We are on primary server, free SQL Cache
		BEGIN
		CHECKPOINT;
		END
		BEGIN
		DBCC DROPCLEANBUFFERS;
		END
		DBCC FREESYSTEMCACHE (''ALL'') 
		BEGIN
		DBCC FREESESSIONCACHE
		END	
	END
END
ELSE BEGIN
	--READ_ONLY = Data can be read but not modified.
	PRINT ''@DatabaseUpdateability == '' + @DatabaseUpdateability + '' on Database "'' + @DatabaseName + ''". No step run''
END
GO', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ClearSQLCache_Schedule', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20171205, 
		@active_end_date=99991231, 
		@active_start_time=180000, 
		@active_end_time=235959, 
		@schedule_uid=N'65398455-c452-452d-8bb6-399b0be59e81'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

This was an interesting experience, note it's important the account running the jobs is "sysadmin" for server roles to execute the free cache commands.  By default this job will use the default sql agent account.  You can verify which account will be running by going onto the SQL servers, open Services.msc and looking in the SQL Server Agent (MSSQLSERVER) service Properties under the Log On tab.


So far SCSM is responding normally once more after it ran the big cache freeing commands:

DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE

If anyone has any better suggestions that keeps SCSM up and running while performing preventative maintenance on the SQL server or any comments, feel free to share them.