SQL memory allocation issues with SBS20003

Discussion in 'Windows Small Business Server' started by Jahesh, Mar 24, 2005.

  1. Jahesh

    Jahesh Guest

    Hi all,

    I'm sure this has been covered before, but damned if I can find it!

    We've installed SQL2000 to a standard edition SBS install. Our accounting
    software migrated from Pervasive SQL to Microsoft, so SBS Premium couldn't
    have happened when we ordered the server.

    We're running 4GB, and 2 Xeon 2.8GHz processors. I'm running the /3GB
    switch in boot.ini, and a minimum-maximum range of 768MB to 2GB in SQL.

    I'm getting occasional memory allocation warnings. Should I be happy that
    they're only occasional, and ignore the warnings, or is there something
    that's set out of whack? I know our accounting software is a very hungry
    application, so I don't want it to starve.

    TIA.

    Jahesh
     
    Jahesh, Mar 24, 2005
    #1
    1. Advertisements

  2. Hi Jahesh,

    Glad to hear from you in the SBS newsgroup.

    According to your description, I understand that you encountered SQL memory
    allocation warning. If I am off base, please let me know.

    Before we go any further can I assume that you receive a alert for the SQL
    service, or your account software?
    The alert should look like:

    A large amount of memory is committed to applications and processes.
    Consistently high memory usage can cause performance problems.

    To determine which processes and applications are using the most memory,
    use Task Manager. Monitor the activity of these resources over a few days.
    If they continue to use a high level of memory and are less critical
    processes or services, try stopping and then restarting them.

    You can disable this alert or change its threshold by using the Change
    Alert Notifications task in the Server Management Monitoring and Reporting
    taskpad.

    The alert is not triggered for a paticular process.

    The counter that is being triggered in Health Monitor is for Allocated
    Memory > 2,147,483,648 bytes; this number is static and is configured
    similarly on all SBS 2003 server installations, this might mean that that
    number may not be representing the true baseline for that particular server
    installation.

    If it is for the SQL service, In most cases, this is a normal behavior.
    Since SBS 2003 server is an integrated product, some services such as
    Exchange Information store, or SQL Server will normally use large memory.


    If you can confirm that everything is working as expected, that there are
    no problems/errors with the system, then you can actually check the average
    count and change it to match that system.

    You can do it from the Monitoring and Reporting snap-in node in Server
    Management, navigate to the Change Alert Notifications, then select
    Performance Counters, and then check the counter average for Allocated
    Memory. You can then Edit that entry and replace the default threshold with
    the average for that system.

    As I know SQL Server is designed to use memory. It caches data and query
    plans to memory so that it will be able to quickly access data. All
    relational database management systems do this. Unless you are experiencing
    problems because of the high memory usage from SQL Server, I would not
    worry.

    When you start SQL Server, SQL Server memory usage may continue to steadily
    increase and not decrease, even when activity on the server is low.
    Additionally, the Task Manager and Performance Monitor may show that the
    physical memory available on the computer is steadily decreasing until the
    available memory is between 4 to 10 MB.

    This behavior alone does not indicate a memory leak. This behavior is
    normal and is an intended behavior of the SQL Server buffer pool.

    By default, SQL Server dynamically grows and shrinks the size of its buffer
    pool (cache) depending on the physical memory load reported by the
    operating system. As long as enough memory is available to prevent paging
    the SQL Server buffer pool will continue to grow. As other processes on the
    same computer as SQL Server allocate memory, the SQL Server buffer manager
    will release memory as needed. SQL Server can free and acquire several
    megabytes of memory each second, allowing it to quickly adjust to memory
    allocation changes.

    Please refer to the following KB for more information:

    321363 INF: SQL Server Memory Usage
    http://support.microsoft.com/?id=321363

    Hope the above information helpful, if you have any further questions
    please let me know. I am standing by to be any help.


    Best regards,

    Charles Yang (MSFT)

    Microsoft CSS Online Newsgroup Support

    Get Secure! - www.microsoft.com/security

    =====================================================
    When responding to posts, please "Reply to Group" via your newsreader so
    that others may learn and benefit from your issue.
    =====================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Charles Yang [MSFT], Mar 25, 2005
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.