Solved

SQl Server Max Memory usage

Posted on 2014-09-14
5
212 Views
Last Modified: 2014-09-16
Hello experts,

I am looking into a SQL Server's performance outputs and I see an inconsistency with the following metrics.

1. When I query the physical memory in use by SQL with the following query, I get '8342' in MB:
SELECT
(physical_memory_in_use_kb/1024) AS Memory_usedby_MSSQL_MB,
FROM sys.dm_os_process_memory;

2. The SQL Server memory usage max and min values are set to 8000 and 0 respectively. I verify that both from UI and following SQL query:
SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

3. I am not sure if the SQL Server Services are restarted after the memory max and min is set. I believe they are but I cannot be 100% sure.

My question is, how come the physical memory used is higher than the max value. (Usage 8342 and max set is only 8000) What will be my next steps to troubleshoot the issue?

Thank you in advance
0
Comment
Question by:bozer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40322553
That's strange.
Can you check how much percentage of the MAX memory are in use?
select memory_utilization_percentage
from sys.dm_os_process_memory

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40323419
Max memory applies only to SQL buffers.  That is the vast majority of the RAM SQL uses, but not all of it.  There will be some modest RAM usage outside the buffers that will not be affected by the max memory setting.

Btw, you don't need to restart SQL Server instance to apply new memory settings, just RECONFIGURE.  If you lower the max setting, SQL may take some time to release the RAM, it doesn't even try to immediately reduce its RAM usage.
0
 

Author Comment

by:bozer
ID: 40324519
Hello Vitor, your query returns 100%

Scott, based on your explanation, is it safe to say it will not 'in theory' go too much beyond those numbers (8342) assuming 342 is the other SQL software usage type? I appreciate if you have an online article or something similar for this.

Also, I highly suspect this configuration is completed over UI. I know 'RECONFIGURE' is a part of the TSQL way of making this configuration. Is it still required to run the query on top of UI changes or does UI takes care of it?

Thanks
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40325602
Check Books Online under "max server memory option".  Yes, RAM typically won't go much beyond that.  Certain special cases can drive it up, for example, if you have *lots* of linked servers and "Allow inprocess" is left unchecked.

Correct, the UI automatically applies the "RECONFIGURE" for you, so you do not need to run it separately.
0
 

Author Closing Comment

by:bozer
ID: 40325729
Thanks, I was able to understand the difference with your explanation.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question