11.02.2014

Automatic handling of external memory pressure on a SQL-Server

Technical Value

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:

  1. EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
  2.  
  3. GO
  4.  
  5. EXEC sys.sp_configure N'min server memory (MB)', N'16384'
  6.  
  7. GO
  8.  
  9. RECONFIGURE WITH OVERRIDE
  10.  
  11. GO
  12.  
  13. EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
  14.  
  15. GO

2. Step: Set MaxServerMemory to 30 GB

 

  1. EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
  2. GO
  3. EXEC sys.sp_configure N'max server memory (MB)', N'3720'
  4. GO
  5. RECONFIGURE WITH OVERRIDE
  6. GO
  7. EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
  8. 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:

  1. DECLARE @currentMaxMem int;
  2. DECLARE @currentMinMem int;
  3. SELECT @currentMaxMem = CAST([value] as int) FROM [master].[sys].[configurations]
  4. WHERE NAME IN ('Max server memory (MB)')
  5. SELECT @currentMinMem = CAST([value] as int) FROM [master].[sys].[configurations]
  6. WHERE NAME IN ('Min server memory (MB)')
  7.  
  8. set @currentMaxMem = @currentMaxMem -512
  9.  
  10. if @currentMaxMem < @currentMinMem
  11. begin
  12. set @currentMaxMem = @currentMinMem +1
  13. end
  14.  
  15. EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
  16.  
  17.  
  18. EXEC sys.sp_configure N'max server memory (MB)', @currentMaxMem
  19.  
  20. RECONFIGURE WITH OVERRIDE
  21.  
  22.  
  23. EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OOVERRIDE

 The complete Job looks scripted like that way:

  1. /****** Object: Job [AdjustMaxServerMemory] ******/
  2.  
  3. BEGIN TRANSACTION
  4.  
  5. DECLARE @ReturnCode INT
  6. SELECT @ReturnCode = 0
  7. /****** Object: JobCategory ******/
  8. IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
  9. BEGIN
  10. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  11. GOTO QuitWithRollback
  12. END
  13. DECLARE @jobId BINARY(16)
  14. EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'AdjustMaxServerMemory',
  15. @enabled=1,
  16. @notify_level_eventlog=0,
  17. @notify_level_email=0,
  18. @notify_level_netsend=0,
  19. @notify_level_page=0,
  20. @delete_level=0,
  21. @description=N'',
  22. @category_name=N'[Uncategorized (Local)]',
  23. @owner_login_name=N'sa',
  24. @job_id = @jobId OUTPUT
  25.  
  26. IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  27. GOTO QuitWithRollback
  28. /****** Object: Step [Adjust the memory] ******/
  29. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId,
  30. @step_name=N'Adjust the memory',
  31. @step_id=1,
  32. @cmdexec_success_code=0,
  33. @on_success_action=1,
  34. @on_success_step_id=0,
  35. @on_fail_action=2,
  36. @on_fail_step_id=0,
  37. @retry_attempts=0,
  38. @retry_interval=0,
  39. @os_run_priority=0,
  40. @subsystem=N'TSQL',
  41. @command=N'DECLARE @currentMaxMem int;
  42. DECLARE @currentMinMem int;
  43. SELECT @currentMaxMem = CAST([value] as int) FROM [master].[sys].[configurations] WHERE NAME IN (''Max server memory (MB)'')
  44. SELECT @currentMinMem = CAST([value] as int) FROM [master].[sys].[configurations] WHERE NAME IN (''Min server memory (MB)'')
  45. set @currentMaxMem = @currentMaxMem -512
  46. if @currentMaxMem < @currentMinMem
  47. begin set @currentMaxMem = @currentMinMem +1
  48. end
  49. EXEC sys.sp_configure N''show advanced options'', N''1'' RECONFIGURE WITH OVERRIDE
  50. EXEC sys.sp_configure N''max server memory (MB)'', @currentMaxMem
  51. RECONFIGURE WITH OVERRIDE
  52. EXEC sys.sp_configure N''show advanced options'', N''0'' RECONFIGURE WITH OVERRIDE
  53. ',
  54. @database_name=N'master',
  55. @flags=0
  56. IF (@@ERROR <> 0 OR @ReturnCode<> 0)
  57. GOTO QuitWithRollback
  58. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  59. IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  60. GOTO QuitWithRollback
  61. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
  62. @server_name = N'(local)'
  63. IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  64. GOTO QuitWithRollback
  65. COMMIT TRANSACTION
  66. GOTO EndSave
  67. QuitWithRollback:
  68. IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  69. EndSave:
  70. 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:

  1. SELECT * FROM __InstanceModificationEvent WITHIN 300
  2. WHERE TargetInstance ISA "Win32_PerfFormattedData_PerfOS_Memory" AND TargetInstance.AvailableMBytes < 2048

So, what are we doing? We use the

  1. InstanceModificationEvent

within

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

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden