Stefan Grigat

Imagine the following, we have a SQL-Server. It is neatly equipped with 32 GB of memory, a few cores and enough disc space. There is also the SSIS-Service running on the system with some small loader packages which update the database on a daily basis during the night. At the beginning of production lifecycle the database is small, just up to a few GB. Everything is fine. Time goes by…
After a few month the database is larger than the available memory. The SSIS-Load package needs twice the time due to data growth and suddenly our monitoring tool reports low memory during night loads or even worse the SSIS-Load fails with out of memory errors.
What if we could automatically react on low memory issues and inform the admin by mail so she could react on this issue relaxed the next morning instead of panic reaction during the night because of the failed load.

In this blog I’ll show you how setup an automatic reaction on low available memory and giving the SSIS-Service enough memory to run the load.

To achieve this goal we just have to setup 4 simple steps:

  1. Step: We set MinServerMemory of the SQL-Server to half of the system memory
  2. Step: We set MaxServerMemory to (system memory – 2 GB) here 30 GB (keeping the operating system alive)
  3. Step: We set up a SQL-Server-Agent job to reduce the MaxServerMemory, which gives more memory to the OS and other services
  4. Step: We implement an alert with the reaction to run the SQL-Server-Agent-Job and e-mail the admin

The 4 steps in detail:

1. Step: In this case half server memory is 16 GB. This is the script to set this up:

EXEC sys.sp_configure N’show advanced options‘, N’1′ RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’min server memory (MB)‘, N’16384′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options‘, N’0′ RECONFIGURE WITH OVERRIDE
GO

2. Step: Set MaxServerMemory to 30 GB

EXEC sys.sp_configure N’show advanced options‘, N’1′ RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’max server memory (MB)‘, N’3720′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options‘, N’0′ RECONFIGURE WITH OVERRIDE
GO

 In both steps the third line needs to adjusted to the correct value (in MB) if your server is equipped with more or less memory.

3. Step: Now let’s setup the SQL-Server-Agent-Job to adjust the MaxServerMemory.

It’s a simple job with only one step. The Step itself does the following: It gets the current Min- and MaxServerMemory, it reduces the MaxServerMemory by 512 MB, checks if we would fall below the MinServerMemory setting (if yes, set 1 MB above) and uses the script of Step 2 to adjust the setting:

DECLARE @currentMaxMem int;
DECLARE
@currentMinMem int;
SELECT
@currentMaxMem = CAST([value] as int) FROM [master].[sys].[configurations]
WHERE NAME IN (‚Max server memory (MB)‘)

SELECT
@currentMinMem = CAST([value] as int) FROM [master].[sys].[configurations]
WHERE NAME IN (‚Min server memory (MB)‘)

set @currentMaxMem = @currentMaxMem 512

if @currentMaxMem < @currentMinMem
begin

set @currentMaxMem = @currentMinMem +1
end

EXEC sys.sp_configure N’show advanced options‘, N’1′ RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N’max server memory (MB)‘, @currentMaxMem RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N’show advanced options‘, N’0′ RECONFIGURE WITH OOVERRIDE

 The complete Job looks scripted like that way:

/****** Object: Job [AdjustMaxServerMemory] ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]‘ AND category_class=1)
BEGIN
EXEC
@ReturnCode = msdb.dbo.sp_add_category @class=N’JOB‘, @type=N’LOCAL‘, @name=N'[Uncategorized (Local)]‘ IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO
QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC
@ReturnCode = msdb.dbo.sp_add_job @job_name=N’AdjustMaxServerMemory‘,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N“,
@category_name=N'[Uncategorized (Local)]‘,
@owner_login_name=N’sa‘,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO
QuitWithRollback
/****** Object: Step [Adjust the memory] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
@step_name=N’Adjust the memory‘,

@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N’TSQL‘,
@command=N’DECLARE @currentMaxMem int;
DECLARE @currentMinMem int;
SELECT @currentMaxMem = CAST([value] as int) FROM [master].[sys].[configurations] WHERE NAME IN (“Max server memory (MB)“)
SELECT @currentMinMem = CAST([value] as int) FROM [master].[sys].[configurations] WHERE NAME IN (“Min server memory (MB)“)
set @currentMaxMem = @currentMaxMem -512
if @currentMaxMem < @currentMinMem
begin set @currentMaxMem = @currentMinMem +1
end
EXEC sys.sp_configure N“show advanced options“, N“1“ RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N“max server memory (MB)“, @currentMaxMem
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N“show advanced options“, N“0“ RECONFIGURE WITH OVERRIDE
,
@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_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

4.Step: Now here is the main attraction, monitoring the available memory with WMI and the SQL-Server-Agent via Alerts

SQL-Server-Agent has a build-in feature called „Alerts“. With it, we can setup some kind of monitoring for Performance-Counter thresholds or WMI-Alerts. You can find the Alerts here:

Right click on Alerts and „New Alert“. Let’s give it a useful name like „MaxServerMemory“. What we want be alerted on is „Available memory less than 2 GB“ as this would state memory pressure on Windows and other services. So we use a WMI-event alert with the following query:

SELECT * FROM __InstanceModificationEvent WITHIN 300
WHERE TargetInstance ISA „Win32_PerfFormattedData_PerfOS_Memory“ AND TargetInstance.AvailableMBytes < 2048

So, what are we doing? We use the InstanceModificationEvent within 300 seconds to check the Available MB.
When the AvailableMBytes-Value falls below 2 GB the event is fired.
Now we need to configure the response of this alert and what should it be? Correct we start the job created in step 3:

 

And that’s it.

The Alert checks every 300 seconds (5 min, feel free to adjust whatever you need) for the available memory keeping in mind windows needs some memory and the SSIS-Load, too. If we fall below that value we start the job to reduce the value of MaxServerMemory and the SQL-Server will free up some space for other services.

Additionally we could e-mail someone to let him know the alert was fired and the next morning she can react on this issue. But anyway we make sure the SSIS-Load can run successfully.

By the way, setting up this reaction is independent of any monitoring tool as it just makes sure the SQL-Server gives some memory to the other services.