high memory utilisation

  • Hi all,

    I have a problem with sqlservr.exe (version 2005). It use alot of memory. I check on taskbar manager sqlservr.exe usage (CPU 10 - 20%, Mem usage - 1,493,688/2GB Ram). I dont know how can I fix it. Some body could help me please.

  • First of all check the pages life in memory with the help of mentioned query,if this value less than 300 then you have a memory pressure and you have to add memory ,if this value is greater than 300 then you have to check particular queries which those are eating resources

    Select OBJECT_NAME,counter_name,cntr_value from sys.dm_os_performance_counters

    where counter_name = 'Page Life Expectancy'

    Total Server Memory ?

    Total SQL Server Memory setting ?

    OS 32bit or 64bit ?

    is this Server for SQL Server Only ? or other services also running like webserver,dns etc ?

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • I got below output after executing the query sent :

    OBJECT_NAMEcounter_namecntr_value

    SQLServer:Buffer Manager Page life expectancy 172320

    SQLServer:Buffer Node Page life expectancy 172320

  • You say SQL is consuming a lot of memory, but is that causing performance issues on that box. Remember, sql will keep taking memory (up to what you allow in max server memory) and only releases it when the OS requests it back (albeit not very quickly). If it's not causing slowness / performance issues, I wouldn't generally worry about it. Out of interest, are you running 32 or 64 bit

  • 64 bit machine, do i need to enable awe on this..?? any suggestion on this?

  • No, you dont need to enable that on 64bit

  • then how i can overcome this problem?

  • Syed Jahanzaib Bin hassan (7/12/2011)


    First of all check the pages life in memory with the help of mentioned query,if this value less than 300 then you have a memory pressure and you have to add memory ,if this value is greater than 300 then you have to check particular queries which those are eating resources

    http://www.sqlmag.com/sponsoredblog/sql-server-questions-answered-28/sql-server/what-does-page-life-expectancy-mean-137153

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • shilpa.shankar87 (7/12/2011)


    then how i can overcome this problem?

    Why is it a problem?

    SQL uses a lot of memory. That's how it's designed. It uses memory to cache data to avoid the cost of going to disk, it caches plans to avoid the cost of recalculating them.

    If you think it's using too much, you can reduce the max server memory setting (it's almost always a good idea to set that). With 2 GB on the server I'd probably set max memory to 1.5 GB. That is a fairly small amount of memory for a database server. My laptop has more that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • OBJECT_NAME counter_name cntr_value

    SQLServer:Buffer Manager Page life expectancy 172320

    SQLServer:Buffer Node Page life expectancy 172320

    Its around 48 hours,so there is no need to add memory

    select db_name(req.database_id) as DBName,con.num_reads as Num_of_reads_by_Connections

    ,con.num_writes as Num_of_writes_by_Connections,req.reads as ReadsByRequest,req.writes as WritesByRequest

    ,req.logical_reads as LogicalReadsByRequest

    ,ses.session_id,ses.memory_usage,ses.host_name

    ,ses.program_name,ses.login_name,ses.status,ses.cpu_time as sessCpuTime

    ,con.net_packet_size,con.net_transport,req.command,req.wait_time

    ,req.last_wait_type,req.cpu_time as reqCpuTime,st.text

    from sys.dm_exec_sessions ses

    inner join sys.dm_exec_connections con on con.session_id = ses.session_id

    inner join sys.dm_exec_requests req on req.session_id = ses.session_id

    cross apply sys.dm_exec_sql_text(con.most_recent_sql_handle) st

    SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

    WHERE STATUS !='SLEEPING'

    ORDER BY CPU DESC

    these queries will provide the information about the resource eater query,find the query and then provide execution plan of this query here

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • i have enabled awe and now memory consumed by sqlserver.exe is bit reduced, i dont know whether doing this is good idea or not

  • shilpa.shankar87 (7/13/2011)


    i have enabled awe and now memory consumed by sqlserver.exe is bit reduced, i dont know whether doing this is good idea or not

    On 64-bit SQL, the AWE setting is completely and totally ignored. It has no effect.

    Now, again I ask. Is this high memory usage a problem? SQL uses a lot of memory. That's how it's designed. It uses memory to cache data to avoid the cost of going to disk, it caches plans to avoid the cost of recalculating them.

    If you think it's using too much, you can reduce the max server memory setting (it's almost always a good idea to set that). With 2 GB on the server I'd probably set max memory to 1.5 GB. That is a fairly small amount of memory for a database server. My laptop has more that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i reduced the max memory , need to check the functionality of server

Viewing 13 posts - 1 through 12 (of 12 total)

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