locked
High memory usage - sqlsrvr.exe RRS feed

  • Question

  • I've got a 2008 R2 SQL x64 and the sqlsrvr.exe is using 5gb of memory. I've limited the usage according to the info I've seen online: rigt click ->server Properties-> memory-> set the Max memory

    This did NOT lower the memory usage. Before you start saying 0 mem free on a server is the correct way for a server to operate, this server has 16gb of memory and one simple database cannot be allowed to take up 5gb of memory. The SQL server is "supposed" to release memory as required by other programs but it isn't. The server locks up every week because of this memory usage issue. I can turn SQL Express off completely and it has tons of free memory and works fine. I need to find a REAL way to limit the sqlsrvr.exe permanently. Does anyone have any ideas on this?

    Tuesday, March 18, 2014 1:30 PM

Answers

  • Hi,

    >>I've got a 2008 R2 SQL x64 and the sqlsrvr.exe is using 5gb of memory.

    Where do you saw this value using some DMV or task manager.Can you post the output of below

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    >>Before you start saying 0 mem free on a server is the correct way for a server to operate, this server has 16gb of memory and one simple database cannot be allowed to take up 5gb of memory

    The database is not taking 5 G but its whole SQL server instance memory.Out of curiosity if you have 16 G why you just set 5 G for SQL server.Are there multiple instances of SQL server on this box.

    >>The SQL server is "supposed" to release memory as required by other programs but it isn't. The server locks up every week because of this memory usage issue. I can turn SQL Express off completely and it has tons of free memory and works fine.

    Yes correct but only if it founds a request from OS to release it. and believe me it does but does not informs you as a flash message.

    >>I need to find a REAL way to limit the sqlsrvr.exe permanently. Does anyone have any ideas on this?

    Real way is what you have doneby setting max server memory  ,you have already limited memory to 5 G.But remember this is only limit for buffer pool there are memory allocated to SQL server directly ( not from buffer pool) and list depends on version SQL server for SQL version uptill 2008 R2 memory for Linked server,extended stored proc,CLR,Backup ,XML all comes from outside buffer pool are you using any of these features

    BTW what is output of

    select @@Version
    If it is express edition you have limitation of 1 G RAM MAX,no matter how much RAM OS supports

    Please reply


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers





    • Proposed as answer by Sofiya Li Wednesday, March 19, 2014 7:23 AM
    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    • Edited by Shanky_621MVP Monday, March 31, 2014 8:13 AM
    Tuesday, March 18, 2014 1:40 PM
  • I can run the command and post the output later today. I have 3 total databases on this server, one is the large one. I limited the max memory on the big one to 500mb (it was automatically set to 2gb originally) and the other two are limited to 200mb each. The task manager shows the sqlsrvr.exe taking up 4.6gb this morning. 

    Hi,

    Just giving 500MB and 200mB to sql server could be worst you can do.Minimum memory required for SQL server to work is 512 MB but atleast 1 G is required to work in good condition.So seriously you need to give more memory to your SQL Server.

    Please run query against all theree instances including select @@version.

    I recommendation is immediately increase max server memory for other instances.Since multiple instance is installed I guess 4.6G is whatyou saw for one instance to which you gave 5 G memory.

    Again task manager is not correct place to look for memory consumption of sql server


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    • Edited by Shanky_621MVP Monday, March 31, 2014 8:14 AM
    Wednesday, March 19, 2014 1:21 PM
  • The physical size of the databases has nothing to do with memory consumption of SQL Server.  In SQL 2008, the "max server memory" setting only controls the buffer pool size.  There are many other allocations SQL Server uses which you have no control over.

    Where exactly do you see SQL Server using 5GBs of RAM?  What exactly is your "max server memory" setting?

    Please post the results of the query Shanky posted.

     
    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    Wednesday, March 19, 2014 1:29 PM

All replies

  • Hi,

    >>I've got a 2008 R2 SQL x64 and the sqlsrvr.exe is using 5gb of memory.

    Where do you saw this value using some DMV or task manager.Can you post the output of below

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    >>Before you start saying 0 mem free on a server is the correct way for a server to operate, this server has 16gb of memory and one simple database cannot be allowed to take up 5gb of memory

    The database is not taking 5 G but its whole SQL server instance memory.Out of curiosity if you have 16 G why you just set 5 G for SQL server.Are there multiple instances of SQL server on this box.

    >>The SQL server is "supposed" to release memory as required by other programs but it isn't. The server locks up every week because of this memory usage issue. I can turn SQL Express off completely and it has tons of free memory and works fine.

    Yes correct but only if it founds a request from OS to release it. and believe me it does but does not informs you as a flash message.

    >>I need to find a REAL way to limit the sqlsrvr.exe permanently. Does anyone have any ideas on this?

    Real way is what you have doneby setting max server memory  ,you have already limited memory to 5 G.But remember this is only limit for buffer pool there are memory allocated to SQL server directly ( not from buffer pool) and list depends on version SQL server for SQL version uptill 2008 R2 memory for Linked server,extended stored proc,CLR,Backup ,XML all comes from outside buffer pool are you using any of these features

    BTW what is output of

    select @@Version
    If it is express edition you have limitation of 1 G RAM MAX,no matter how much RAM OS supports

    Please reply


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers





    • Proposed as answer by Sofiya Li Wednesday, March 19, 2014 7:23 AM
    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    • Edited by Shanky_621MVP Monday, March 31, 2014 8:13 AM
    Tuesday, March 18, 2014 1:40 PM
  • you have to analyze first how much memory your OS needed??? on daily basis.

    after that you need to maximise the limit of memory to sql server.

    second, check for the program which is taking all memory, take help of task manager and sids, pids to relate what is running on sql server side. there surely some DTS/sql code which is causing this you need to correct it.

    right now your research in multiple direction i recommend you to limit your research in correct direction first and find the root cause.

    if it dedicated database server then i guess 4-6 GB would be sufficient for your OS.
    • Edited by skc_chat Wednesday, March 19, 2014 9:33 AM
    Wednesday, March 19, 2014 9:32 AM
  • I can run the command and post the output later today. I have 3 total databases on this server, one is the large one. I limited the max memory on the big one to 500mb (it was automatically set to 2gb originally) and the other two are limited to 200mb each. The task manager shows the sqlsrvr.exe taking up 4.6gb this morning. 

    I understand why skc_chat says I need to focus on the server as a whole but I don't want to be that general. I want to focus strictly on the sqlsrvr.exe since that's where my memory is going. I'm just not sure why the max memory settings are being ignored and the program is taking up 4.6gb.


    Wednesday, March 19, 2014 12:56 PM
  • I can run the command and post the output later today. I have 3 total databases on this server, one is the large one. I limited the max memory on the big one to 500mb (it was automatically set to 2gb originally) and the other two are limited to 200mb each. The task manager shows the sqlsrvr.exe taking up 4.6gb this morning. 

    Hi,

    Just giving 500MB and 200mB to sql server could be worst you can do.Minimum memory required for SQL server to work is 512 MB but atleast 1 G is required to work in good condition.So seriously you need to give more memory to your SQL Server.

    Please run query against all theree instances including select @@version.

    I recommendation is immediately increase max server memory for other instances.Since multiple instance is installed I guess 4.6G is whatyou saw for one instance to which you gave 5 G memory.

    Again task manager is not correct place to look for memory consumption of sql server


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    • Edited by Shanky_621MVP Monday, March 31, 2014 8:14 AM
    Wednesday, March 19, 2014 1:21 PM
  • The physical size of the databases has nothing to do with memory consumption of SQL Server.  In SQL 2008, the "max server memory" setting only controls the buffer pool size.  There are many other allocations SQL Server uses which you have no control over.

    Where exactly do you see SQL Server using 5GBs of RAM?  What exactly is your "max server memory" setting?

    Please post the results of the query Shanky posted.

     
    • Marked as answer by Sofiya Li Monday, March 31, 2014 2:09 AM
    Wednesday, March 19, 2014 1:29 PM
  • i am not understanding if you have 16 GB. why you are giving 11 GB to OS..????

    there might be requirement of 8-12 GB for sql server depending on your transaction, size etc factors......

    if you keep on limiting the memory you will be in big trouble soon.

    Thursday, March 20, 2014 8:39 AM