none
sqlserver.exe is consuming lot of memory

    Question

  •  

    Hello,

     

    I have sql server 2005(SP1) standard edition 64 bit installed on 64 bit Windows 2003 SP1.

    The total memory we have is 4GB.

    But the sqlserver.exe is consuming 2.1 GB of memory, could not understand why?

    I have set the min and max server memory option , 15MB and 2048MB respectively.

    Also enaled AWE, and read from the documentation that I did not need to restart the service to implement it

    done with RECONFIGURE option.

     

    Can you help me solve this issue.

    It is really urgent.

     

    thanks,

     

    Monday, April 7, 2008 12:49 PM

Answers

  • This is not a problem, it is how SQL Server was designed to run.  You don't want to limit the memory consumption of SQL Server or you will have detrimental impacts on your SQL Performance.  You don't need AWE Enabled on your server.  That is for scaling memory past 4GB and is not necessary on the 64 bit architectures.  SQL Server should be run on a dedicated server.  If it is not on a dedicated server, then you can set a max memory setting, but expect that SQL will use the max memory it is allowed to.

     

    SQL Server maintains a memory cache of the most commonly requested data from the database.  Retrieving data from memory is much faster than pulling it from disk.  SQL will age old data out as new data is required from disk.  What you are experiencing is normal behavior.  I have a 64 bit SQL 2005 server that uses 8GB of RAM all day long.  It is how it is designed.

    Monday, April 7, 2008 1:11 PM
    Moderator
  • Have a look at this KB:

     

    http://support.microsoft.com/kb/321363

     

    If they are experiencing performance problems, then restricting memory is only going to compound the issues.  What else is running on this server aside from SQL?

     

    Have a look at this screencast series by Kevin Kline on monitoring performance of a SQL Server:

     

    http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1302603,00.html#

     

    Have a look at the PerfMon and see how the values of the below counters measure up against the values listed below:

     

    Memory: Pages/sec - Should be under 20% on Average

    Memory: Available Bytes  - Should maintain at least 5-10MB free

    Physical Disk: % Disk Time - Should remain under 55%

    Processor: % Processor Time - Should be less than 80%

    SQL Server Buffer: Buffer Cache Hit Ratio - Should be as close to 100% as possible.  Anything under 90% for sustained periods is potentially problematic

     

    This will start to give you an idea of what is happening on the server?  How are the disks configured on the Server?  It could be IO bound by a lack of proper disk configuration for a SQL Server.  There are lots of variables that will affect how a given installation of SQL Server will perform.  Just reducing memory thinking it is the problem is not accurate.
    Monday, April 7, 2008 1:34 PM
    Moderator
  • it is exactly as Jonathan said , i saw SQL server goes up to 16 GB on a server.

     

    But what is the case on other server if you say it is differnet ?

     

    take a look here too

     

    http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

    Monday, April 7, 2008 3:22 PM

All replies

  • This is not a problem, it is how SQL Server was designed to run.  You don't want to limit the memory consumption of SQL Server or you will have detrimental impacts on your SQL Performance.  You don't need AWE Enabled on your server.  That is for scaling memory past 4GB and is not necessary on the 64 bit architectures.  SQL Server should be run on a dedicated server.  If it is not on a dedicated server, then you can set a max memory setting, but expect that SQL will use the max memory it is allowed to.

     

    SQL Server maintains a memory cache of the most commonly requested data from the database.  Retrieving data from memory is much faster than pulling it from disk.  SQL will age old data out as new data is required from disk.  What you are experiencing is normal behavior.  I have a 64 bit SQL 2005 server that uses 8GB of RAM all day long.  It is how it is designed.

    Monday, April 7, 2008 1:11 PM
    Moderator
  • thanks for the reply, but this is not the case on all the server which ran on 64 bit on same platform as I mentioned.

    As I need to tell it to the customer, and to give the valid reason for the this.

     

    Can you send me some official link as you mentioned that "it is how SQL Server was designed to run"

    It also make the server slow.

     

    help me to understand this.

     

     

    Monday, April 7, 2008 1:30 PM
  • Have a look at this KB:

     

    http://support.microsoft.com/kb/321363

     

    If they are experiencing performance problems, then restricting memory is only going to compound the issues.  What else is running on this server aside from SQL?

     

    Have a look at this screencast series by Kevin Kline on monitoring performance of a SQL Server:

     

    http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1302603,00.html#

     

    Have a look at the PerfMon and see how the values of the below counters measure up against the values listed below:

     

    Memory: Pages/sec - Should be under 20% on Average

    Memory: Available Bytes  - Should maintain at least 5-10MB free

    Physical Disk: % Disk Time - Should remain under 55%

    Processor: % Processor Time - Should be less than 80%

    SQL Server Buffer: Buffer Cache Hit Ratio - Should be as close to 100% as possible.  Anything under 90% for sustained periods is potentially problematic

     

    This will start to give you an idea of what is happening on the server?  How are the disks configured on the Server?  It could be IO bound by a lack of proper disk configuration for a SQL Server.  There are lots of variables that will affect how a given installation of SQL Server will perform.  Just reducing memory thinking it is the problem is not accurate.
    Monday, April 7, 2008 1:34 PM
    Moderator
  • it is exactly as Jonathan said , i saw SQL server goes up to 16 GB on a server.

     

    But what is the case on other server if you say it is differnet ?

     

    take a look here too

     

    http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx

    Monday, April 7, 2008 3:22 PM
  • thanks Jonathan and Mawla, it was indeed a good tips.

     

    Tuesday, April 8, 2008 9:07 PM