Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, April 25, 2008 8:54 PM
Hi Guys,
I have done enough research on this topic and understood that SQL Server loves to cache as much memory as it can get hold of , but did not find a straight forward answer to my problem.
To explain things in detail,
We are currently using SQL Server 2005 (64 Bit) on Windows 2003 (64 Bit) with 32 GB of RAM. This morning when I checked, it was showing the memory as 1GB RAM. As soon as I stopped and restarted SQL Sever, I got back all 31GB of it! This is a live mirrored server, and the data is exposed through a .NET Win Application. Also, this is a dedicated SQL Server.
The min and max memory of the SQL Server is set at 16 and 2147483647 respectively.
My questions:
1. What can be done to make SQL Server release the memory back once the process is complete?
2. If this involves setting a limit to the max memory of the SQL Server, what can it be set to? And does setting the max memory low affect the performance of server?
3. I have other servers that are not dedicated SQL Servers which are showing the same 'memory' related problems, what should be the max memory setting on these ?
Any help in this regard is deeply appreciated.
Little_Birdie.
Friday, April 25, 2008 9:28 PM ✅Answered
1. As your research will have confirmed, this is normal behaviour.
SQL will continue to use as much memory as it is allowed to access if there is sufficient workload, and as you haven't restricted your max server memory settings this is what has happened. SQL will then only release memory back to the OS if the OS is under pressure.
2. As this is a single instance of SQL Server running on a dedicated box, a max server memory setting of 28GB should be ok.
However,as you know your system and what else is running on it, you are ultimately best placed to fine-tune the max server memory setting.
One thing you need to be aware of on 64-bit is to set the lock pages in memory permission - assuming this is the Enterprise Edition of SQL 2005: http://msdn2.microsoft.com/en-us/library/ms190730.aspx
3. As I said, you know what other apps are running on your system and what their memory needs are, so you need to balance their requirements with SQL. SQL's greedy so it will grab as much memory as it can, so you will need to set a max server memory setting that gives you adequate performance and leaves enough for whatever else it's sharing the server with.
Ideally, move SQL to a dedicated server as it doesn't like sharing.
Friday, April 25, 2008 9:30 PM ✅Answered
Can you run SELECT @@VERSION to tell us exactly what version and edition of SQL Server 2005 you have, (i.e. Enterprise Edition, Build 3186)?
If you have Enterprise Edition, do you have "Lock Pages in Memory" set for the SQL Server Service Account? If not, you should turn it on.
Unfortunately, the figure shown in Task Manager for sqlsvr.exe for mem usage is not accurate. It is a very good idea to set the Max Memory setting for SQL Server in order to leave enough for the OS and other SQL related tasks (beside the buffer pool). For a 32GB server, I would try setting Max Memory at 29000 (29GB).
1. SQL Server will not trim its working set unless it is under pretty extreme external memory pressure.
2. I would set it at 29000 for a 32GB dedicated SQL box. Setting it too low would cause extra I/O pressure.
3. Depending on what else is running on them, you would set Max Memory to a lower value.
Friday, April 25, 2008 11:02 PM ✅Answered
Memory pressure should not cause DB mirroring to automatically fail-over. It is more likely that connectivity problems between the principal, mirror and witness server would cause that.
It is not a good idea to try to programatically, externally manage SQL Server's memory usage. The SQL Server OS does a pretty good job of managing its own memory very well. Trying to interfere with what it is doing would not be helpful.
Have you looked in the SQL error logs and Windows Application Event logs for any clues as to why you are getting fail-overs? I just don't think memory pressure would be involved.
Friday, April 25, 2008 10:28 PM
Ajmer / Glenn,
Thanks for the input.
The version of SQL SERVER is
Microsoft SQL Server 2005 - 9.00.3175.00 (X64) Jun 14 2007 11:45:39 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Also, 'Lock Pages in Memory' option is already enabled.
By what you say, I figure, SQL Server will not release memory on its own. So, my next qn is
1. Is there is a way to make it release the memory, programmatically, without restarting the server engine?
2. If yes, then will doing so on the server, which is live, is recommended?
3. As mentioned earlier, this is a mirrored server. The databases on this server keep falling into mirror mode randomly without any apparent reason (FYI, the mirror server has same configuration as this one). I haven’t figured out the reason as to why this is happening, but, does memory retention of SQL Server have anything to do with this? Because, as the memory is being withheld, causing the server is slow down drastically - which could be causing the databases to failover - what say?
Awaiting your reply,
Little_Birdie.
Friday, April 25, 2008 11:21 PM
Got it! Thanks for the invaluable suggestions.
Little_Birdie
Wednesday, September 8, 2010 10:33 AM
Hi All, Please respond me quickly because mine is production server my system ram is 8 GB,sql server 2008 R2,windows server 2008 X64 bit, actually I set maximum per sql server 6 gb, but if running any quries,memory gradually increasing like 2.0,2.4,2.6,3.5,4.6,5.6,....7.6Gb and steadily over there, after executing the query its not releasing the memory so how can i solve this issue. please provide the valuable information i can solve quickly lock pages done.
Tuesday, January 10, 2012 10:12 AM
SQL Server's memory manager is designed to keep as much data cached in memory as it can in order to speed up access for incoming queries. To refer this please check the below links
- http://msdn.microsoft.com/en-us/library/ms176018.aspx
- http://support.microsoft.com/kb/321363
In order to play nicely with other processes on the machine SQL Server will release memory when a low memory notification is sent by windows. The memory may be committed by the sql server process, but it will be released if another application requests enough to trip the low memory notification.
"When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB.
This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool". --> from the KB article 321363
If you really need to drop memory consumption of SQL Server without there being any external pressure you can do so by changing the max server memory configuration. To resove the memory utilization alarms.
From a client connection issue this command:
sp_configure 'max server memory', <mb setting>
reconfigure
You should see a rapid decline in the amount of memory used by the sql server process.
sp_configure 'max server memory', <mb setting> -- map it again to actual utilization
reconfigure
OR we can manage the above using GUI…
Please let me know if in case of any concerns.
Thanks, Satish Kumar.
Tuesday, February 9, 2016 11:04 AM
This seems to be known issue, even for later versions such as SQL Server 2014.
To "force" a release I implemented a scheduled job that also executes the below listed SP:
- it sets the max server memory to a "low" threshold
- then, wait for 30 secs to allow SQL Server to actually release the committed (virtual) memory
- then, sets the max server memory back to the "original" threshold
I hope this helps
ALTER PROCEDURE [dbo].[Release_Committed_Memory]
AS
BEGIN
SET NOCOUNT ON
BEGIN
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'max server memory (MB)', N'2000'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
END
BEGIN
WAITFOR DELAY '00:00:30'
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'max server memory (MB)', N'25600'
RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
END
END
Frank van Zuilen
Monday, July 23, 2018 10:42 AM
I found one nice solution at: https://note.robinks.net/2016/02/sql-server-release-memory.html
It's working fine for realising sql server memory allocated.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[SQL_Server_Release_Memory] Script Date: 2/24/2016 12:42:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
description: SQL Server Release Memory
author: Robin
date: 2016/02/24
testing code:
EXEC SQL_Server_Release_Memory
*/
CREATE PROCEDURE [dbo].[SQL_Server_Release_Memory]
AS
BEGIN
/*Query physical memory in use*/
SELECT [description], value_in_use
, (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';
DECLARE @MaxMemorySize int;
SELECT @MaxMemorySize = convert(int, value)
FROM sys.configurations
WHERE name ='max server memory (MB)';
/*1.Set Maximum server memory to 1.5GB */
DECLARE @TempMemorySize int
SET @TempMemorySize = 1024 * 1.5;
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'max server memory (MB)', @TempMemorySize RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;
DECLARE @WaitAndTry int = 0
WHILE (@WaitAndTry < 5)
BEGIN
/*Check has already released ? */
WAITFOR DELAY '00:00:10';
IF EXISTS(
SELECT (physical_memory_in_use_kb/1024)
FROM sys.dm_os_process_memory
WHERE (physical_memory_in_use_kb/1024)<2000
)
BEGIN
/*2.Set Maximum server memory to original setting */
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'max server memory (MB)', @MaxMemorySize RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;
SET @WaitAndTry = 10;
PRINT N'Finish.';
END
ELSE
BEGIN
SET @WaitAndTry = @WaitAndTry + 1;
IF @WaitAndTry = 5
BEGIN
PRINT N'Fail: have to check and setting [Maximum server memory].';
END
END
END
/*Query physical memory in use*/
SELECT [description], value_in_use
, (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';
/*
reference
http://serverfault.com/questions/251832/sql-server-bulk-insert-physical-memory-issue
http://serverfault.com/questions/408919/how-do-you-get-the-minimum-and-maximum-memory-allocation-of-a-sql-instance-using
*/
END
Monday, July 23, 2018 12:13 PM
It is completely normal, expected and desired for SQL Server to hold RAM once it is allocated. This is not an indication of any problem and should not attempted to be changed.
Releasing RAM this way is only hurting your performance.
https://thomaslarock.com/2017/08/killing-sql-server-memory-hog-myth/