Sqlservr.exe memory usage issues

Posted on 2009-02-10
Last Modified: 2012-08-13
I am running SQL Server 2005 on windows 2000 server machine with 16gb of ram. What would be my most efficient memory settings because I am getting serious sqlservr.exe memory spikes. Dont know if this will resolve the problem but I want to make sure I cover my back as far as my settings...
Question by:healthcheckinc
    LVL 20

    Expert Comment

    by:Marten Rune
    'With Windows 2000 Professional and Server, the maximum amount of memory that can be supported is 4 GB'
    'Windows 2000 Advanced Server supports 8 GB of physical RAM and Windows 2000 Datacenter Server supports 32 GB of physical RAM'

    These are OS limitations. I guess you need to upgrade the OS to W2k3 R2 to use all unless you have the w2000 datacenter edition (not sold seperately).

    LVL 20

    Expert Comment

    by:Marten Rune
    When upgrading OS, choose the enterprice ed. Quote from link above:
    'Windows Server 2003, Enterprise Edition supports 32 GB of physical Memory'


    Author Comment

    I guess I should have included that it is a Windows 2003 R2 Enterprise Edition SP2....Sorry about that...Knowing that what would be your recommendations on Max - Min memory settings
    LVL 20

    Accepted Solution

    Still not all the information about the OS. But ok.
    32bit system
    If you have less than 4GB, use the /3GB switch (boot.ini).
    Between 4GB and up to (including) 16GB, use /3GB switch and /PAE switch (boot.ini) and the AWE (SQL Server) in SQL Server.
    More than 16GB, use /PAE switch (boot.ini) and the AWE (SQL Server) in SQL.

    64 bit system
    No special configuration required.

    If you are on a 64 bit system, congrats, otherwise consider a upgrade. The SQL handles the memory dynamically. It's recommended to begin with default settings. You can set a minimum and maximum threshold. The minimum I would recommend you to set from the beginning.

    If your system is only for sql I would consider setting the max to 14GB, always leaving 2GB for the OS. It's a matter of opinion.

    If you use the enterprise edition, be sure to configure the 'lock pages in memory' option. This prevents the OS from stealing back memory for OS operations. If SQL is your sole purpose with this server, you will be better off with pages locked to the SQL process. The OS is ok with 2GB.

    Then monitor your server, if you have huge amounts of connections, maybe the OS needs a small boost, then leave 3 or 4 GB to the OS. This you will find out by tuning and monitoring performance.


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now