Troubleshoot high SQL Server Memory usage

  • 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!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks, If that is the case, why is the available memory on Windows server less than 7GB and going down further day by day?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail. But there is a reasonable amount of memory allocated to OS/Other apps which is over 35GB. Still be an issue?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks!

  • 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