question

ZahidNadeem-8256 avatar image
0 Votes"
ZahidNadeem-8256 asked ZahidNadeem-8256 commented

DB server with MSSQL showing high memory

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.

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ZahidNadeem-8256,
Was your issue resolved?
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·

No its not resolved. I am still working on it. I guess there is something other than SQL.

0 Votes 0 ·
AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered ErlandSommarskog commented

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


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft sever is showing 95% memory right now. Here is output of query referred by you:

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


As I think SQL server is taking less memory right now.

Any thoughts pls.

0 Votes 0 ·

So here SQL Server is grabbing 28 GB of RAM, which is below the 35 GB which is set as the max, and well below 95% of 48 GB.

Where do you get this number of 95% from?

0 Votes 0 ·

I get 95% from task manager - Performance tab & secondly from our NetMon (Solarwinds).
This is actually 95% of total memory , not only used by SQL. Thanks

0 Votes 0 ·
Show more comments

Hi ZahidNadeem-8256,

Currently, the total physical memory used by SQL Server is about 28 GB as Erland mentioned and it is normal. You may need to keep eye on it until the issue of 95% memory used by SQL Server reappears.

Best Regards,
Amelia

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ZahidNadeem-8256 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erland its 95% of total physical memory (48gb).

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered ZahidNadeem-8256 commented

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 ?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

That is not dedicated :). You should see if SSIS is using most of the remaining memory and I believe it would be using. Memory for SSIS does not comes from max server memory you have set, it comes from what you have left for OS

0 Votes 0 ·

Thanks.
SSIS service is running but there is nothing in integration services catalogs.
So I wonder even then it takes memory ?

0 Votes 0 ·