May 13, 2014 at 11:33 am
I am running SQL 2012 on Windows Server 2008. I have set the maximum allowed memory for SQL Server. Also, I've installed all of the latest SQL 2012 updates.
I have noticed that in task manager that the physical memory in use for the entire server is very slowly growing over time by approximately 1% a week. The SQL Server memory usage in task manager does not appear to be growing but I suspect that this is not accurate. Meaning, I believe that this process is leaking memory that is not able to be tracked by Task Manager.
Has anyone else encountered this issue? Are scheduled reboots an expected practice these days? Is Windows Server 2012 any better?
Any help is much appreciated.
May 13, 2014 at 12:06 pm
I have set the maximum allowed memory for SQL Server. Also, I've installed all of the latest SQL 2012 updates.
I have noticed that in task manager that the physical memory in use for the entire server is very slowly growing over time by approximately 1% a week.
This is by design. A server will eventually consume all the memory in some form or fashion over time. The max memory setting in SQL Server is only for the buffer pool memory usage.
The SQL Server memory usage in task manager does not appear to be growing but I suspect that this is not accurate. Meaning, I believe that this process is leaking memory that is not able to be tracked by Task Manager.
If you want to monitor memory usage for SQL Server try PerfMon and memory DMVs.
Has anyone else encountered this issue?
I don't believe you have an issue with SQL Server, unless your monitoring of memory usage with DMVs or Performance Monitor show otherwise.
Are scheduled reboots an expected practice these days?
No.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 13, 2014 at 12:24 pm
raysteve43 (5/13/2014)
I am running SQL 2012 on Windows Server 2008. I have set the maximum allowed memory for SQL Server. Also, I've installed all of the latest SQL 2012 updates.I have noticed that in task manager that the physical memory in use for the entire server is very slowly growing over time by approximately 1% a week. The SQL Server memory usage in task manager does not appear to be growing but I suspect that this is not accurate. Meaning, I believe that this process is leaking memory that is not able to be tracked by Task Manager.
Has anyone else encountered this issue? Are scheduled reboots an expected practice these days? Is Windows Server 2012 any better?
Any help is much appreciated.
1) If you are definitely on all SQL Server 2012 updates (SP1 CU9 now) then you have fixed the KNOWN memory leaks (and there were several IIRC). That doesn't mean you aren't exposed to an as yet unfixed memory leak.
2) Agree with other poster that you need to use mechanisms other than Task Manager for viewing memory information about SQL Server.
3) Windows Server 2012 is LIGHTYEARS ahead of some older OSs, especially 2003 and before. I no longer prescribe prophylactic reboots of windows servers.
4) Is your windows server patched up too, including all firmware and drivers?
5) Do you have a max memory set in SQL Server? This is a best practice. Also I believe the other poster mis-stated. In SQL 2012 the max memory setting covers a good bit more than just the buffer pool, although it is still not a true max: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 14, 2014 at 4:09 am
raysteve43 (5/13/2014)
I have noticed that in task manager that the physical memory in use for the entire server is very slowly growing over time by approximately 1% a week. The SQL Server memory usage in task manager does not appear to be growing but I suspect that this is not accurate. Meaning, I believe that this process is leaking memory that is not able to be tracked by Task Manager.
Task Manager is a pretty basic tool. It only shows VAS memory allocation and will not show memory allocated to the SQL Server buffer pool with "Lock Pages in Memory" enabled.
http://www.sqlpassion.at/archive/2011/11/04/why-sql-server-is-using-so-less-memory/[/url]
August 13, 2014 at 3:54 am
I suspect sql2012 (SP 2) of still having a memory leak. We have a daily job (on a test system) that runs fine for about a month until it starts failing with 'There is insufficient system memory in resource pool 'default' to run this query. [SQLSTATE 42000] (Error 701). The step failed.'
I pinpointed the failure to the point where there is 'left outer join sys.dm_db_index_usage_stats'.
A restart of sql fixes it.
Edit:
I'm not able to reproduce the problem (while testing a work around).
Maybe the cause was a 'wrong' (initial) execution plan due to a (more or less) empty dmv.
Apologies for the confusement.
August 13, 2014 at 7:20 am
F. van Ruyven (8/13/2014)
I suspect sql2012 (SP 2) of still having a memory leak. We have a daily job (on a test system) that runs fine for about a month until it starts failing with 'There is insufficient system memory in resource pool 'default' to run this query. [SQLSTATE 42000] (Error 701). The step failed.'I pinpointed the failure to the point where there is 'left outer join sys.dm_db_index_usage_stats'.
A restart of sql fixes it.
You need to start a new thread for this because it is a different issue. The error you are receiving does not indicate a memory leak, it is memory resource error.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 13, 2014 at 12:08 pm
Based on this error, I'd suspect
1) The Maximum Memory setting is ridiculously low (the setting in SSMS is in MB)
2) I also see an option to set Index creation memory. I've always left it at the default (0= dynamic memory) but if this was set by mistake to a too-low number that might create a memory issue related to indexes.
3) The Server itself might not have enough RAM for the workload
4) The query itself is buggy and highly inefficient.
I'd check the memory settings first.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply