Post reply

High Memory usage on SQL Server

  • 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

  • October 16, 2013 at 8:34 am

    #1658945

    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;-)

  • October 16, 2013 at 9:42 am

    #1658984

    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.

  • October 16, 2013 at 1:19 pm

    #1659056

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

  • October 17, 2013 at 2:50 am

    #1659170

    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;-)

  • October 17, 2013 at 10:57 am

    #1659419

    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.

  • October 17, 2013 at 12:16 pm

    #1659448

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

  • October 17, 2013 at 12:18 pm

    #1659449

    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..

  • October 18, 2013 at 8:08 am

    #1659726

    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;-)

  • October 18, 2013 at 9:48 am

    #1659781

    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.

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

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