SQL memory

Question

We have a customer that is running SQL 2014 on windows server 2012R2.
The server has 128GB of RAM.

I have within SSMS setup the MIN RAM as 58982 and the MAX RAM as 117964.

but. when i run
SELECT object_name ,counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)'

It is showing only 25072480.

Since i forced the MIN RAM shouldn't that value reflect the MIN RAM i have set?

Answer 1

Your problem likely has nothing to do with RAM. You need to look at why some things are slow. It could be any number of problems, blocking, disk IO, etc.

Task manager does not display all RAM associated with SQL Server.

Answer 2

Hi @Darin Horton ,

Is the reply helpful?

BR
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Darin Horton ,

Try code as next to get your Max and Min Server Memory:

Use Master  
GO  

SELECT name, value, value_in_use, [description]   
FROM sys.configurations  
WHERE name like '%server memory%'  
ORDER BY name OPTION (RECOMPILE);  

First, let us clarify some concepts, Min Server Memory, Max Server Memory, Total Server Memory

Min Server Memory is a logical concept that controls the size of SQL Server Total Server Memory. As for whether the data is placed in physical memory or buffer files, it is determined by Windows. So this value cannot guarantee the minimum amount of physical memory used by SQL Server.Min Server Memory means that after the address space of SQL Server grows to this value, it will no longer be smaller than this value. When SQL Server just starts, it only applies for the memory needed for startup. Therefore, it is also a normal phenomenon that the memory usage is less than Min Server Memory at this time.

Max Server Memory is also a logical concept that controls the size of SQL Server Total Server Memory. As for whether the data is placed in physical memory or buffer files, it is determined by Windows.Max Server Memory can only control a part of SQL Server's memory usage. It is normal that the Virtual Bytes or Working Set of SQL Server is larger than the Max Server Memory you set, and it does not mean that SQL Server has a memory leak.

Total Server Memory: Specifies the amount of memory the server has committed using the memory manager.

Then the principles of setting your SQLSERVER memory are as follows:
1.Try to make the server dedicated to the database, and do not install other services (such as IIS, middle-tier application services, etc.) on the same machine.

2.The Windows system and other key application services must have enough memory to prevent them from grabbing the memory that SQL Server has applied for due to insufficient memory during operation.

3.Under the premise of meeting point 2, SQL Server uses as much memory as possible, and to ensure the stability of the amount of memory used.

4.It is recommended to set SQL Server Max Server Memory to ensure that Windows has enough memory for the system itself. Generally, setting Min Server Memory does not make much sense.

You can reference this to do the settings: server-memory-server-configuration-options

And more information: how-to-troubleshoot-sql-server-performance-issues

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Since min doesn't force it, you would have to have some startup procedure that reads lots and lots of data so SQL Server climbs over the min in the first place.

But it seems you assume the problem is memory related. Perhaps it isn't?

Or perhaps something external to SQL Server (like a balloon driver) uses so much memory so that SQL Server trims it memory usage?

Anyhow, you have the answer regarding the min setting. As for your problem at hand, we have way too little info to say anything. Wait stats is one piece of the puzzle, but not the only one.

Answer 5

It is showing only 25072480. Since i forced the MIN RAM shouldn't that value reflect the MIN RAM i have set?

No, not necessarily. The min server memory counter means the minimum memory which if SQL Server attains would not trim down its memory consumption below that value. A SQL Server will not try to immediately gain memory value mentioned in min server memory parameter, if it can work with value lower than min server memory it will NOT try to attain that value. But, once the min server memory value is attained SQL Server will not trim down its memory consumption below this value in case of memory pressure. Of course in drastic case if paging happens and force SQL Server to trim down severely, it will but that is not a normal scenario.