DB server with MSSQL showing high memory

Question

Hi,

I am using SQL server 2014 (dedicated server) with Windows server 2012 R2.

Total memory =48 gb
Minimum server memory =0
Maximum server memory = 35 gb.

Problem is that memory often goes up to 95% without any performance issue.
No query is stuck in there.
I have observed many times , during the time if I execute following query:

( SELECT
physical_memory_kb/1048576.0 physical_memory_GB,
virtual_memory_kb/1048576.0 virtual_memory_GB,
committed_kb/1048576.0 committed_GB,
committed_target_kb/1048576.0 committed_target_GB
FROM sys.dm_os_sys_info; )

It shows committed_GB, committed_target_GB less than 35 GB. Which means some other process is grabbing memory.
Task manager shows SQL server process taking more memory but its in mbs (most of time less than 500 mbs).

Tried to get it through reading different articles & different forums but no satisfaction.

Any help please.

Thanks in advance.

Answer 1

95% of what? 48GB? Or 95% of 35 GB?

SQL Server will grab as much memory it needs within what is set with max server memory. And as long as Windows does not signal memory pressure, SQL Server will cling to that memory. The memory is used for the buffer cache, so that users can be served the results of queries faster.

Task Manager is not a good place to looking if you have granted SQL Server "lock pages in memory". sys.dm_os_process_memory is a better place.

Answer 2

Hi ZahidNadeem-8256,

As Erland mentioned, by default, a SQL Server instance may over time consume most of the available memory that SQL Server is allowed to acquire in the server. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If there is low free memory for the OS, SQL Server will release memory back to the operating system until the low memory condition is alleviated, or until SQL Server reaches the min server memory limit. This is by design and does not indicate a memory leak in the SQL Server process.
Here is a query which might help:

SELECT    
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,    
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,    
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,    
process_virtual_memory_low    
FROM sys.dm_os_process_memory;   

 

Please refer to Monitor memory usage which might help.

Best Regards,
Amelia

---

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

Memory_used_by_Sqlserver_MB Locked_pages_used_by_Sqlserver_MB Total_VAS_in_MB
28275 27862 134217727
process_physical_memory_low process_virtual_memory_low
0 0

All I can say is SQL Server does not seems like culprit here, you have given it 35 GB it is using 28 GB which is normal, you have LPIM so hard trimming is also highly unlikely. Can you check for other processes running on the Windows server they must be using more memory. Is this dedicated SQL Server box . SSIS/AS/RS running ?

Answer 4

Hi ZahidNadeem-8256,

dedicated SQL server box (hypervisor machine) & SSIS running.

SSIS memory is completely separate from SQL Server memory. The maximum amount of memory is constantly used by SQL Server database engine. And there is no way to configure a cap on the amount of memory the SSIS execution engine will use. You can sometimes make SSIS performance better by reducing the maximum memory available to SQL Server. Please refer to this article which might help.
Best Regards,
Amelia