SQL Server Performance

RAM ratio for SQL Server and OS

Discussion in 'Performance Tuning for Hardware Configurations' started by holyterror_1, Mar 21, 2004.

  1. holyterror_1 New Member

    Hi,

    Is there any recommendation for the memory ratio for sql server and the os?
    I currently have one running on windows 2000 adv server with 8GB of ram.
    I've set the max memory for sql server at 7.5 GB.
    It's a high volume database, and i get intermittent performance problems, so i'm wondering if the ratio for the memory could be the cause of these performance problems, among others...

    recommendation, anyone?
    thanks!

    Raymond
  2. satya Moderator

    The following equation provides a way to estimate what the initial SQL Server memory setting should be on a computer that is dedicated solely to SQL Server.

    y=(.95x-22)*512


    In the equation above, 'y' is the sp_configure memory setting in 2-KB pages and 'x' is the amount of RAM on the computer in MB. You can effectively use this equation on computers that have an amount of RAM ranging from 64 MB to 2 GB.

    What is the current SQL edition installed?
    Its better to leave memory settings as dynamic if there are no other applications sharing the memory & h/w resources.

    Also enable PERFMON to collect counters of memory by looking at two counters,
    Memory: Available Bytes
    Memory: Pages Faults/sec

    For information review thishttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp article.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. holyterror_1 New Member

    Hi Satya,

    based on the equation you gave me and with the 8GB ram i have installed,
    the value i get is:
    y = ((0.95 x 8000)-22) * 512
    = 3879936

    So the amount of RAM i should reserve for sql server is about 4 GB?


    I've also done a perfmon, the working set for the process sql server is 121344000 bytes, which is essentially 121 MBs.
    The sql server:memory manager otal server memory counter however, shows 7.5 GB.
    How do i relate the working set counter with the total server memory counter? Does it mean that my sql server is not using all the memory that is allocated to it, since the working set value is so low?


    Raymond
  4. holyterror_1 New Member

    Oh, i'm using sql server enterprise edition with service pack 3a.

    it is running on a dedicated server. i remember reading somewhere that the server will not be able to dynamically manage memory once AWE is turned on? is this true?

    Thanks!

    Raymond
  5. satya Moderator

Share This Page