Post reply

Calculating memory for SQL Server on a VM machine

  • March 27, 2018 at 4:48 am

    #330066

    Dear DBAs
    I hope you guys are doing great
    I am a little confused with calculating the required RAM for SQL Server
    My server is on a VM machine where the ESX host holds 2 CPUs with 12 cores each but only 4 virtual CPUs are assigned to the VM hosting the SQL server
    I have 1 instance with 2 databases and the memory allocated to the Servre is 8GB
    Now i came across the following formulas:

    http://sqlmax.chuvash.eu/
    and
    https://sqlcan.com/2013/03/04/how-to-calculate-max-memory-for-sql-server/#comment-2112
    But i dont know how these formulas are calculated in a VM environment.
    Can anyone shed some light? I am using SQL Server 2012 Standard Edition x64 on Windows 2008 R2

    Hurricane

  • March 27, 2018 at 9:20 am

    #1984663

    how much memory does it actually need, have you monitored for this

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" šŸ˜‰

  • March 27, 2018 at 9:55 am

    #1984673

    Those are good questions but My answers are how do I know how much memory I need? Iā€™m trying to follow the best practice and now the server is having memory at 90%.

    Im going to setup monitoring and see what I come up with.

    What do you recommend?

    Hurricane

  • March 27, 2018 at 9:59 am

    #1984675

    How big are the databases?

  • March 27, 2018 at 12:08 pm

    #1984692

    hurricaneDBA - Tuesday, March 27, 2018 9:55 AM

    Those are good questions but My answers are how do I know how much memory I need? I’m trying to follow the best practice and now the server is having memory at 90%. Im going to setup monitoring and see what I come up with. What do you recommend?Hurricane

    There is no general practice for how much memory a server needs, it will need to be determined by factors as others have mentioned such as:
    -  Is this an OLTP system or a reporting / OLAP type system?
    -  How large are the user databases that will be on this server?
    -  What is the usage of that data like?  Is most of the queries performing on a small subset of that data or is the workload spread more evenly across?
    -  How many concurrent users will be connected to the database?
    -  Is this replacing an older server?  If so, what was that configured like and what were its bottlenecks?

    Having memory 90% utilized is not really an indicator of anything good or bad, SQL Server will grab as much memory as it can and do its own memory management.  The things to watch are how long are data pages staying in memory, how many requests are being satisfied by data pages in memory vs requiring physical I/O, and is the operating system having to hit its page file frequently?  You obviously want to satisfy as many requests as possible using data pages in memory since it's quicker.  You obviously want to minimize the number of times the operating system hits its page file on disk since that adds an extra hidden READ and WRITE to a data request that SQL Server thinks is already in memory.

  • March 28, 2018 at 1:21 am

    #1984743

    Dear Chris
    Here are my answers:

    There is no general practice for how much memory a server needs, it will need to be determined by factors as others have mentioned such as:
    - Is this an OLTP system or a reporting / OLAP type system?
    KY - Single application but will be adding more databases to it
    - How large are the user databases that will be on this server?
    KY - Size might go up to 50GB
    - What is the usage of that data like? Is most of the queries performing on a small subset of that data or is the workload spread more evenly across?
    KY - This is a PTW system so work is spread evenly
    - How many concurrent users will be connected to the database?
    KY - Maybe upto 500 users
    - Is this replacing an older server? If so, what was that configured like and what were its bottlenecks?
    KY - This is a new system and has been online for 1 year but i am moving it to SQL Server 2016 so i need to know how much memory to configure for the server

    I am devising a document to standardize SQL server security / hardware settings hence is the reason i need to get a rough estimate of what to set the RAM for SQL Server 2016 which will have one instance with up to 50 databases max per instance on VM machines having 4 virtual CPUs per VM machine
    What do you guys think?
    Hurricane

  • April 7, 2018 at 1:47 am

    #1985876

    While there's no specific algorithm to get a magic number, what one has to do is to first get an ideal about much memory the applications need . To do that,
    The order of consideration / combination of RAM / CPU / Storage is as below :
    1.I recommend starting with increasing the 'RAM/ Memory' first till the point you have it more than enough to accommodate the applications' data even at peak business hours . And yes it requires due analysis.
    2.Now you've to look at raising the CPU level (not all higher clock speeds are faster than the lower ones)
    3.Then comes looking at the storage

    Hope it helps.

Viewing 7 posts - 1 through 6 (of 6 total)

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