SQL Server Performance

Memory estimation

Discussion in 'Performance Tuning for Hardware Configurations' started by coclol, Apr 27, 2004.

  1. coclol New Member

    Hey there!<br /><br />Can anyone explain in an easy way how memory is used in the sql server?<br />I'm currently arguing with a programmer that means that the available physical memory in the computer just has to be as big as the database.<br /><br />I assume that this can't be correct.<br />My application is a not very well optimized (yet) three tier app with lots of stored procedures. With approx 5-10 users I mean that server side cursors takes memory, temporary sorting areas for queries etc takes memory, cache, logs etc.<br /><br />Can anyone tell me the truth <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  2. derrickleggett New Member

    Your programmer doesn't know what he's talking about. The memory is used for several purposes in SQL Server. Procedures plans are stored in the procedure cache which uses memory. Logical reads and writes are done in the memory area which uses memory. Everything in SQL Server uses memory and is more efficient with a larger amount of memory.

    The ideal situation is to have SQL Server on a dedicated box with as much memory as possible, memory being directly related to performance.

    Let us know if you need more details.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. Luis Martin Moderator

    True. SQL server take all memory he can. So more memory, more performance.
    BTW: Ask programmer if you have 10 databases 30Gbytes each, you need 30 server[:I]?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  4. Raulie New Member

    Alot of Memory is always good. Here are some Hardware tips on what type of memory characteristics to look for.

    EDO RDRAM SDRAM
    Error Correcting
    Fault Tolerance
    Access Speeds
    Densities



  5. satya Moderator

    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.

    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 (between 4 - 10 MB), 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.


    KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;319942] to determin proper configuration.

    Also refer to Books Online; topics: "Effects of min and max server memory"; "Memory Architecture"; "Memory Architecture"; "Server Memory Options"; "SQL Server Memory Pool".

    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.

Share This Page