February 15, 2016 at 9:16 am
Hi Folks,
I have a single sql instance running on a server with 128GB memory. Max memory allocated to this instance is 90GB. But the available server memory (on the windows box) is getting lower day by day and now its 7GB left on the server.
When I monitored the SQL server memory usage on task manager, it started with less than a GB in the first few days after SQL server restart then its climbing day by day and now its showing 31GB used. Locked pages on memory is enabled.
Here's the breakdown:
SQL Server version: 2014 Enterprise
Total on Windows Server 2012 R2: 128GB
SQL Max memory: 90GB
SQL usage on task manager: 31 GB
Total memory left on windows server: 128 - (90 + 31) = 7GB
What/why is the extra 31 GB used by SQL?
No other applications are installed except netbackup client. How do I troubleshoot this?
Many thanks!
February 15, 2016 at 9:22 am
Don't use Task Manager to track SQL's memory. It lies!
http://sqlinthewild.co.za/index.php/2016/01/19/stop-using-task-manager-to-check-sqls-memory-usage/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 9:30 am
Hi Gail,
I know the task manager lies. But in this case the locked pages in memory is enabled. And the available memory on "Windows Server" is 7 GB as SQL server process is excessively used extra 31 GB on top of MAX memory allocated to SQL (90GB). SCOM is alerting for low memory in Windows Server.
Thanks.
February 15, 2016 at 9:32 am
SQL!$@w$0ME (2/15/2016)
What/why is the extra 31 GB used by SQL?
Gail is right about Task Manager, of course. But even if we assume Task Manager is telling the truth, what makes you think the 31GB is "extra", and not part of the 90GB? SQL Server will keep using memory until either it reaches the max server memory or the operating system asks for it back. That's normal behaviour.
John
February 15, 2016 at 9:35 am
Locked pages on memory is enabled.
Here's the breakdown:
SQL Server version: 2014 Enterprise
Total on Windows Server 2012 R2: 128GB
SQL Max memory: 90GB
SQL usage on task manager: 31 GB
Total memory left on windows server: 128 - (90 + 31) = 7GB
What/why is the extra 31 GB used by SQL?
In this case its 'abnormal', as SCOM is alerting for low windows server memory, lower than 10% threshold.
MAX and MIN SQL server memory is set to same which is 90GB and it has used all 90 GB allocated.
February 15, 2016 at 9:36 am
SQL!$@w$0ME (2/15/2016)
But in this case the locked pages in memory is enabled.
Yes, exactly. And because it's enabled Task Manager is reading far, far lower than it should be and giving you false readings. Hence why I wrote the blog post, because this 'problem' is not a problem. The tools you're using are lying to you and hence you have an incorrect assumption as to what's going on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 9:37 am
SQL!$@w$0ME (2/15/2016)
What/why is the extra 31 GB used by SQL?
There's no 'extra' here. If you didn't have Locked Pages enabled, Task Manager would be showing the full 90GB for SQL's memory. It is enabled, hence Task Manager is showing a falsely low value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 9:38 am
Thanks, If that is the case, why is the available memory on Windows server less than 7GB and going down further day by day?
February 15, 2016 at 10:08 am
SQL!$@w$0ME (2/15/2016)
MAX and MIN SQL server memory is set to same which is 90GB and it has used all 90 GB allocated.
No, please don't do that. Especially not in combination with locked pages.
Setting Min to Max means that if Windows comes under memory pressure, it can't ask SQL to reduce it's memory usage and, because of the locked pages, it can't swap SQL's memory out either. This could result in a crash.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 10:58 am
Thanks Gail. But there is a reasonable amount of memory allocated to OS/Other apps which is over 35GB. Still be an issue?
February 15, 2016 at 11:42 am
Yes.
You're putting the server into a state where should something consume lots of memory (remote desktop with SSMS, large file copies), you could starve the OS of memory, and for no good reason.
As for debugging where the memory is going, first you need to get an accurate view of exactly what SQL Server has. Once you have that, you can decide whether it's SQL or if you need to look elsewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2016 at 2:04 pm
Thanks Gail. The article was much helpful.
Regarding the performance counters to troubleshot sql server memory usage, can you suggest the counters/DMVs/scripts I should use to monitor sql memory other than buffer manager.
Target/Total server memory are same: 90GB
February 15, 2016 at 2:09 pm
Google: Glenn Berry diagnostic scripts
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2016 at 9:47 am
Thanks!
February 20, 2016 at 8:24 am
I was not able to find a script to troubleshoot this particular issue.
http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-january-2016/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy