Post reply

Is this memory configuration optimal ?

  • January 24, 2012 at 1:20 am

    #249544

    Hi,

    If we have a server with below specification:

    OS: Windows Server 2008 R2

    Total RAM: 48 GB

    Number of SQL Instances : 8

    Now, the memory for each instance has been confiured as given below:

    S.No. InstanceName ConfigType Min. Mem. Max.Mem

    1. Instance1 Static 18 GB 18 GB

    2. Instance2 Static 18 GB 18 GB

    3. Instance3 Dynamic Defaut Default

    4. Instance4 Static 1 GB 30 GB

    5. Instance5 Static 20 GB 20 GB

    6. Instance6 Static 1 GB 5 GB

    7. Instance7 Static 1 GB 5 GB

    8. Instance8 Dynamic Defaut Default

    I want to know that is this configuration of memory is optimal ?? Because even if we add up the minimum memory assigned to the instances it is 59 GB more than that we have on the server (48 GB). So is it any good to have this kind of configuration ?

    Any help is appreciated. 🙂

    Sujeet Singh

  • January 24, 2012 at 1:36 am

    #1437496

    Divine Flame (1/24/2012)

    Because even if we add up the minimum memory assigned to the instances it is 59 GB more than that we have on the server (48 GB). So is it any good to have this kind of configuration ?

    So in other words, you want SQL to starve the OS, cause severe memory pressure, be paged to disk often and possibly hang the server due to memory starvation (especially if you're running locked pages)

    Do you think that specifying that SQL must use no less that 11 GB more memory than exists is a good thing? (ie the OS will have no option other than to severely page those SQL instances to disk all the time)

    The total of those max server memory settings is 96GB, so you're telling SQL it is allowed to use up to twice the memory that exists on the server. There's a good recommendation for setting max server memory in chapter 4 http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    p.s. Setting min server memory and max server memory to the same thing is not generally recommended. With that setting, SQL can't dynamically adjust memory usage if there's memory pressure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 24, 2012 at 2:05 am

    #1437509

    Thanks Gail for providing the answer. I have been assigned to a new project where I saw this kind of memory configuration on one server. It was very surprising for me ( in fact shocking!) to see this type of memory configuration.

    I wanted to be sure that this configuration doesn't have any good thing in it. You made that clear 🙂 . I'll proceed with the change process to set the memory configuration to optimal settings.

    I would also like to know that why should we have 8 instances on a server ??? Does it have any special advantage ??

    Sujeet Singh

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply