Skip to Content
avatar image
Former Member

Sizing SQL Memory

We have a landscape with 6 systems.

We are planning on a three tier architecture.

An "Active-Active" cluster will be used to host the databases on SQL2005 64bit/Ent. Three systems will reside on each node under business as usual conditions.

Does increasing the amount of memory on the database servers have a significant impact on performance? Or if sized correctly, would little gain be achieved from increasing the amount of memory beyond the sizing results?

Regards,

Andrew

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jul 14, 2008 at 05:24 PM

    The rule we have here inhouse is:

    1. For physical servers where database and SAP application will reside:

    Memory setting (fixed) for SQL = (physical RAM - 2GB) divided by (Number of apps [including SQL]).

    E.g. If I am running 2 SAP instances along with the DB server on a physical server with 48GB of ram. It would be:

    (48 - 2) / (3 applications) = 15GB fixed memory for SQL.

    2. For physical servers where only database is running, the rule is simplier: physical RAM - 2GB.

    FYIs:

    - The 2GB is for the O/S.

    - Make sure in local security policy you enable "lock pages in memory" for the user running the SQL NT services. This minimzes paging outs and ensures SQL performance.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Kevin is right on that more memory never hurts, however at some point you hit a diminishing return and you are just throwing memory at nothing.

      If you want to be sure you need the memory increase try looking at the perfmon counter "Buffer Manager: Page life expectency". If the value is < 300-400s you need to increase the memory. If its substantially above that you are probably ok.