SQL Server Performance

Lower max server memory = better performance ?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Mark_M, Oct 30, 2008.

  1. Mark_M New Member

    Hi, newb here so go easy please!
    Anyway - I have a 'write intensive' batch application which I have just moved to a new 2005 server with 2GB of RAM and 4 core processor and have been having problems with performance. Basically; when the 'max server memory' is left as default (2GB) my process takes over 24 hours and one of the CPUs is averaging nearly 100%. I notice also that about 1GB of memory is actually being used and there is no heavy paging (still quite a few MBs of RAM left).
    Now, when I change the Max memory config to 750MB (and Min to 8MB - was 0 previously) my process takes 2hours which, for the type of operation is acceptable (this was the configuration on the old machine). Also CPU usage is a lot less and better spread across cores.
    My question is; why is this? You would think more available memory would mean at least equal performance to the lower configured value, wouldn't you...
    Any thoughts greatly appreciated.
  2. Luis Martin Moderator

    Welcome to the forum!.
    Have you try leaving memory dynamically?
  3. Mark_M New Member

    Yes - the original config (2GB) is the dynamic (default) setting...
  4. Luis Martin Moderator

    Is other application running or is SQL dedicated?
  5. Mark_M New Member

    Thanks for reply - no - no other apps running. It's a dedicated server. Exactly the same conditions for both runs. The only difference is the SQL Server memory config.
    This is very strange [*-)]
  6. Luis Martin Moderator

    Yeap, and I have not a clue![:(]
  7. bpman New Member

    I assume you are running Win2k3? and SQL2k5 sp2?
    With only 2gb of ram, the os will try to use most of that so leave the setting as dynamic.
    Consider more memory, sql 2005 is a memory hungry beast. my rule of thumb is 80% of the time you are looking at 20% of the db, therefore for a 10gb database, you will need 2gb of ram for sql server, and as the os will take the first 2gb, you would need 4gb, otherwise your 4 cores will be busy paging swapping data to / from disk.
    also, you say "write intensive", what disk configuration are you running? Scsi, RAID, Fibre?

Share This Page