locked
Capacity Planning (RAM requirements) for sql server 2012 RRS feed

  • Question

  • Hi Experts,

    We are planning to move a replicated environment having 5 subscriptions present in 5 locations globally to a single consolidated environment.Average user connections is 600 per subscription database.So we are expecting a consolidated user connection of around 3000.

    Please Note: In the present setup the users are connecting to instances present in local locations while for the planned setup around 2500 users will connect remotely from UK,AUS,GERMANY,SINGAPORE to the consolidated instance present in US along with local US users.

    Our query:

    1. Expected RAM requirements for SQL Server 2012 installation of this entire setup.The database size is around 100GB in all.

    2. Please advise us of any other hardware level configurations.

    Regards,

    Avishek

    Tuesday, August 26, 2014 3:04 PM

Answers

  • .

    Our query:

    1. Expected RAM requirements for SQL Server 2012 installation of this entire setup.The database size is around 100GB in all.

    2. Please advise us of any other hardware level configurations.

    Regards,

    Avishek

    I always rely on perfmon counters to check how much is memory requirement of SQL server. If your database is 100 G 32 G of RAM would be good to start with considering above details you mentioned. Please note this is just a starting value we manage 100 G database with 16 G RAM as well it totally depends on complexity of your env.

    You can refer to below perfmon counters

    Following counters one should look taken from  this link

    1. SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR): IIf your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again
    2. SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.  
    3. SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.  
    4. SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.  
    5. SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx 
    6. SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
    7. SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

     

    Few Points

          1. If Target server memory is greater than Total server memory there can be memory pressure. Let me put emphasis on word can be ,it is not a sure shot signal.Please refer to this MSDN forum thread where OP had target server memory greater than total server memory but because there were no memory grants pending ,and page life expectancy was high so there was no memory pressure

      2.  Generally on stable system these 2 values are equal. 
      3. Free Pages counter is removed from SQL Server 2012. And also its value does not holds importance as the values for BCHR,PLE,Target server memory and Total Server  memory



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Tuesday, August 26, 2014 3:16 PM