none
sqlserv.exe takes a lot of memory (growing) SQL Server 2008 R2

    Question

  • I had another machine that i was turning off. 
    So the new one a windows 2008 r2 machine was the new host. 

    So all data from the sql server, turned over to the new machine.
    For the details the old machine had 2GB ram, the new one 4GB, but it was turning out of memory!
    So that is not right.

    The Activity monitor is looking well, the max use of memory is 16kb per database. (witch are +/- 30)
    But why is the sqlserver.exe in 12 days over the 850mb, started with 150mb.... , so its growing until the machine stucks with it the remote desktop isn't even available.

    I did run this query:

    SELECT 
    LEFT([name], 20) as [name],
    LEFT([type], 20) as [type],
    [single_pages_kb] + [multi_pages_kb] AS cache_kb,
    [entries_count]
    FROM sys.dm_os_memory_cache_counters 
    order by single_pages_kb + multi_pages_kb DESC

    The result of this was:



    Also i run this command:


    -- Turn on advanced options
    EXEC sp_configure 'Show Advanced Options', 1;
    GO
    RECONFIGURE;
    GO
    -- See what the current values are
    EXEC sp_configure;
    -- Set MAXDOP = 1 for the server (For OLTP workloads)
    EXEC sp_configure 'max degree of parallelism', 1;
    GO
    RECONFIGURE;
    GO

    The output of it was:
    (i changed the max server memory to 750mb... (in the old machine ther was no maximum)
    and the max worker threads to 250 the rest is default?)

     


    Can you help me?
    I've got stuck...  

    • Edited by Jelmer330i Thursday, January 26, 2012 10:19 AM
    Thursday, January 26, 2012 10:15 AM

Answers

  • Hi Jelmer330i,

    What is the version of SQL Server, 32-bit or 64-bit? For a 32-bit SQL Server instance, it will take no more than 2G memory if the AWE is not enabled. It depends on the SQL Server version (32-bit or 64-bit), physical memory and the max memory, even with other configurations, such as AWE, /3GB boot.ini switch.

    Are you experiencing any memory error? If not, you may need not to worry about it. It is the behavior of SQL Server to occupy as much as possible memory to improve its performance. If the server has idle memory not used by the operating system and other applications, SQL Server may ask for more memory if the total number of memory does not cap at the max. It is reasonable to feed much more memory to SQL Server if the memory is sufficient on the system.

    To restrain SQL Server to consume the memory, you can configure the Maximum Server memory. For more information: Troubleshooting the SQL Server Memory Leak (or Understanding SQL Server Memory Usage)  


    Stephanie Lv

    TechNet Community Support


    • Edited by Stephanie Lv Thursday, February 02, 2012 9:27 AM
    • Marked as answer by Stephanie Lv Monday, February 06, 2012 8:33 AM
    Thursday, February 02, 2012 9:27 AM

All replies

  • What does the below return?

    --sql server uses the memory

    select 

    CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],

    bpool_committed,bpool_commit_target 

      from 

    sys.dm_os_sys_info


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 26, 2012 10:24 AM
    Answerer
  • Hi,

    it is normal that SQL Server is taking more and more memory if the memory is not required by some other process. Microsoft optimized the SQL Server that it is able to release the memory directly when other applications need it.

    The core reason for this is simple: SQL Server can cache data in memory to speed up operations.

    So it is completly normal to see SQL Server using a lot of memory. (So we have multiple database servers and on a system with 24GB RAM SQL Server takes about 20GB and so far there was never problems when some other process required multiple GB of RAM.

    We never got an out of memory error on our systems. Can you tell us more about your configuration? Do you have a swap file configured? (AFAIK just a small one is suitable just to activate all possibilities of the windows).

    With kind regards,

    Konrad

    Thursday, January 26, 2012 10:24 AM
  • Size: 768,000.00
    bpool_committed: 96000
    bpool_commit_target: 96000 
    Friday, January 27, 2012 2:38 PM
  • Where can i set a SWAP file? 
    I've used SQLExpress 
    Friday, January 27, 2012 2:46 PM
  • That is a computer setting. Not a SQL Server setting.

    On WIndows 7 you could right-click computer (either on your desktop or in your start menu - depending what setting you have.) then choose Properties. The window that pops up has advanced settings (or properties? Sorry, have a german system only). Another window pops up where you see an Advanced tab where the top button also has the settings for the virtual memory.

    With kind regards,

    Konrad

    Friday, January 27, 2012 2:58 PM
  • Alright that one, i was confused by MS SQL.
    I've got a windows 2008 r2 machine. So its a little bit different:

    But should it change if i set the SWAP file to +/- 6000 GB... 



    Saturday, January 28, 2012 10:25 AM
  • Hi Jelmer330i,

    What is the version of SQL Server, 32-bit or 64-bit? For a 32-bit SQL Server instance, it will take no more than 2G memory if the AWE is not enabled. It depends on the SQL Server version (32-bit or 64-bit), physical memory and the max memory, even with other configurations, such as AWE, /3GB boot.ini switch.

    Are you experiencing any memory error? If not, you may need not to worry about it. It is the behavior of SQL Server to occupy as much as possible memory to improve its performance. If the server has idle memory not used by the operating system and other applications, SQL Server may ask for more memory if the total number of memory does not cap at the max. It is reasonable to feed much more memory to SQL Server if the memory is sufficient on the system.

    To restrain SQL Server to consume the memory, you can configure the Maximum Server memory. For more information: Troubleshooting the SQL Server Memory Leak (or Understanding SQL Server Memory Usage)  


    Stephanie Lv

    TechNet Community Support


    • Edited by Stephanie Lv Thursday, February 02, 2012 9:27 AM
    • Marked as answer by Stephanie Lv Monday, February 06, 2012 8:33 AM
    Thursday, February 02, 2012 9:27 AM