SQL Server Performance

SQL Server using all available ram

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by JonMny, Dec 5, 2008.

  1. JonMny New Member

    I am using 64bit version of sql server 2005, 20 gig RAM. server 2003, default settings for memory.
    we recently enabled lock pages in memory option for the service account. Ever since then sql server is using almost all of the available memory, we have 20 gigs and it uses almost all of it all of the time, any suggestions on how to change this.
  2. MohammedU New Member

    If you want to limit the SQL server memory then..use MAX SERVER MEMORY setting...
    EXEC sp_configure 'max server memory (MB)', <Memory In MB>
    GO
    RECONFIGURE WITH OVERRIDE
    GO
  3. satya Moderator

    Welcome to the forums.
    That is a default nature of SQL about using the available memory, as per the dynamic memory settings and unless you have tested the results of limiting the min and max memory on the SQL Server as it may hamper the performance adding more fuel to problem.
    So I would like to ask what kind of performance problem you are having at the moment?
    Have you collected any PERFMON counters for memory & disk?
    Any changes to the enviroment recently such as hotfix or any hardware changes?
    Do you see the following error on eventviewer:
    “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 13128, committed (KB): 6239692, memory utilization: 0%%
  4. JonMny New Member

    <P mce_keep="true">We were getting that error about paging, I enabled the lock pages in memory and now it's using all the available memory, that's causing an issue because the server stops responding.&nbsp; When I run perfmon <IMG title=Perfm style="WIDTH: 800px; HEIGHT: 484px" height=484 alt=Perfm src="http://img.photobucket.com/albums/v168/bethmny/perfmon.jpg" width=800 mce_src="http://img.photobucket.com/albums/v168/bethmny/perfmon.jpg"></P>

Share This Page