SQL Server Performance

Usage of Phisical Memory

Discussion in 'Performance Tuning for DBAs' started by oryand, Aug 5, 2003.

  1. oryand New Member

    Hello,

    I have Advance Server with 4 GB of Phisical Memory,
    How much (max) memory I need to allocate to the sql server (Enterprise Edition) ?
    and How can I do that ?

    The Advance Server is dedicated for the Database.

    Thanks,
    Oryan
  2. Luis Martin Moderator

    Use Enterprise Manager, properties.
    In Memory Folder you can set minimum and maxinum memory.

    I suggest you to keep SQL to administrate memory in dinamyc way. This is the best for perfomance.

    Luis Martin
  3. gaurav_bindlish New Member

  4. satya Moderator

  5. RoyalSher New Member

    Hi,

    Its true to keep the configuration of Sql Server as Dynamic for best performance. But, we have a Production Server wherein Memory Available falls to 5MB. Why does this happen ? , Can it be handled in a better way ?

    HTH

    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  6. satya Moderator

    RoyalSher,

    Use PERFMON to capture counters for Physical disk, memory, process, processor, SQL server, SQL Server locks and see the activity.

    Are there any other applications running parallelly?
    What is the level of service pack on SQL & OS?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. RoyalSher New Member

    Hi Satya,

    I have checked up all the counters and take a regular log of Counters. After verifying with the optimal values, there were few exceptions once in a while.

    Yet after these verifications, I feel the OS is being suffocated of memory.

    Ne Suggestions.

    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  8. satya Moderator

    Any applications or process running during this slow performance?

    You will get performance if you add more physical memory to the server, BTW what is the current setting?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. RoyalSher New Member

    Hi Satya,

    No other applications competing with Sql Server. The present system configuration is 1.2 GB RAM, Quad Proc (400 MHz)and Dynamically Configured Memory, Sql Server 7, Sp3 & OS - Windows 2000.

    > You will get performance if you add more physical memory to the server

    When I find the counters for Physical Memory are keeping to optimal values, do you think additional memory would be required ? And why shd. it require ?



    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  10. satya Moderator

  11. RoyalSher New Member

    Hi Satya,

    Thanks for your input.

    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  12. gaurav_bindlish New Member

    RayalSher, This has been observed intermitently for some of the SQK Server boxes that the SQL Server takes lot of memory and (probabbly it releases slowly) the OS is deprived of the memory needed. In these kind of situations, I recommend setting maximum value of memory that can be used by SQL Server appropriately so as to leave some memory for the system. However keep the memory setting dynamic only.

    Here note that from experimentation I have observed that SQL Server will take appx. 10% more memory than what is allocated to the same. So keep this in mind when you are calculating the maximum memory limit.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. SQL_Guess New Member

    @oryand : have you set to use AWE ? Are you getting 2.8GB allocated to SQL server after it has been running for a while ? We have several 4GB servers, with the /3GB switch implemented, and have never seen SQL actually get more than 2.8 GB.

  14. RoyalSher New Member

    Hi Gaurav,

    > setting maximum value of memory that can be used by SQL Server appropriately so as to leave some memory for the system

    Can you kindly tell me the recommended standard value ? And how did u derive at the figure ?
    Say I have 1 GB RAM, in one of the boxes having the problem.



    RoyalSher.
    *********
    The world is the great gymnasium where we come to make ourselves strong.
  15. satya Moderator

    As you use dynamic memory settings, and if you want to define min and max memory settings then assing 70% of memory to SQL and leave 30% to OS. As some of our services were configured in similar way.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. gaurav_bindlish New Member

    The figure of 10% overshooting was observed during few experiments with bulk load operations like DTS.

    Well I would go for 850 MB of memory and then considering am overshooting limit of 10%, 65 MB of memory will be still left for the OS.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page