High Memory usage on SQL Server

Question

High Memory usage on SQL Server

Robin35

SSCertifiable

Points: 6471
More actions
October 16, 2013 at 8:31 am

#395675

Hi,
Past few days we are receiving memory high alerts 99.65% all the time from our monitoring tool for one of our cluster sql server.
Details:
SQL Server 2008 SP2 standard
Windows 2008 R2 Enterprise
Memory - 16 GB
SQL Server assigned memory : 12 GB ( initially ) but i changed it to 10 GB.
In our idera monitoring tool we saw some free memory in sql server area so reduced the sql server max memory from 12 to 10 GB but i still see the memory high alerts. I don't see any suspicious activity on the instance like large queries , blocking, deadlocks.
I believe this is from OS processes, when i look at the task manager i see so many processes but no single process is using high amount of memory.
Can any one please help me on this and prove the issue is not with SQL server ?
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Robin35 (10/16/2013)
I believe this is from OS processes, when i look at the task manager i see so many processes but no single process is using high amount of memory.

First thing Task manager doesnt provide you exact status of memory comsumption instead use the memory DMVs to capture the details

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Robin35 SSCertifiable Points: 6471 More actions · Answer 2

Thanks Bhuvnesh.

I'm aware of this, i tried couple of DMV's and DBCC memorystatus commands as well. but i'm not sure where to start and how to decide where the issue lies.

Please let me know your suggestions.

e4d4 SSCertifiable Points: 5777 More actions · Answer 3

You could try a Process : Working Set performance counter for all process that you suspect or for all process.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Robin35 (10/16/2013)
but i'm not sure where to start and how to decide where the issue lies.

see http://blogs.msdn.com/b/karthick_pk/archive/2012/06/15/troubleshooting-sql-server-memory.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

dan-572483 SSCoach Points: 15870 More actions · Answer 5

Is the server used for something other than a SQL Server Engine Host? Is 3rd party vendor software also running? If the server has 16GB and SQL is capped at 10GB and you're still showing 99 percent memory usage, then some other memory intensive processes are going on. In Task Manager, make sure "Show processes from all users" is selected.

Robin35 SSCertifiable Points: 6471 More actions · Answer 6

Thanks Dan..I have already checked the show process tab.

Robin35 SSCertifiable Points: 6471 More actions · Answer 7

Thanks bhuvnesh...very informative..on looking at the task manager again...I can see a NonPaged memory helding almost 8.5 GB.( Under Kernel memory left bottom of performance tab of task manager )

Did any one faced this kind of issue, please let me know.

thanks in advance..

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

First see the error log if some is there like error mentioend in link i pasted above

another run this too

select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb,locked_page_allocations_kb/(1024) as awe_memory_mb,total_virtual_address_space_kb/(1024) as max_vas_mb,virtual_address_space_committed_kb/(1024) as sql_committed_mb,memory_utilization_percentage as working_set_percentage,virtual_address_space_available_kb/(1024) as vas_available_mb,process_physical_memory_low as is_there_external_pressure,process_virtual_memory_low as is_there_vas_pressurefrom sys.dm_os_process_memorygo

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Robin35 SSCertifiable Points: 6471 More actions · Answer 9

Thanks Bhuvnesh...i tried all scripts, error log and windows log ..no errors were reported.

I just got a response from SA's saying that this issue might be related to opening files, programs on the server and not closing them correctly ...it will run up the memory.

Rebooting the server will not solve the issue. I have closed all the programs on the box and now i see memory usage has been reduced.

But we are not sure what is the exact reason...

Thanks everyone for your help.