estimation

Question

Post reply

estimation

sqlfriend

SSC Guru

Points: 52781
More actions
May 11, 2017 at 11:28 am

#325583

I need to estimate the RAM and Cores for a database server that some databases added up to size of 200GB. There is potentially more databases going to be there. Is there a best way to estimate the size to buy a physical SQL server box?
Does the max size of RAM in standard edition only allow 128 GB? Does that mean we have to buy the physical server about the same RAM size?

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

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

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

128GB is max that SQL Server instance will use for its buffer pool - and this is per instance.

but as there are other things that can use the available memory you should put more on the server.

Not only the operating system, but any application running on that server can and should make use of extra memory on top of the 128GB above.

For SQL Server its not only the buffer pool that matters but eventually what other functionality you use.
see https://www.microsoft.com/en-us/sql-server/sql-server-2016-editions for details.

For example if you get a instance with the following items

SQL Server
SSAS Tabular
SSAS Dimensional
SSRS

you can use 128 + 16 + 64 + 64 - = 272 GB just for SQL Server stuff in a single INSTANCE of each.

Note that all limits are per INSTANCE, not per server. Multiple instances on the same server will have the same cost and each will use their own block of memory

Number of Cores is per server - all must be licensed (with a small exception where the instance is limited to 20 cores by design).

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks for the info frederico_fonseca. I can only find the document online about 128GB is max that SQL Server instance will use for its buffer pool - and this is per instance.

But I cannot find info above like SSRS 64, SSAS 64Gb etc. is there a link of online document?
So that means for operating system we can give much bigger size of RAM then 128 GB, and SQL server will use 128 GB for buffer pool.

My server probably will use SSRS too, and we will only use one instance, and no other application installed on it, just for SQL server services.
Also can we estimate the RAM by database sizes, that is the total of database sizes plus more?

Thanks,

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Sorry, I searched and found the info.

So if I buy 240 GB for the server, and my SQL server will use Database engine of 128 GB and SSRS will use 24 GB, do I need to explicitly to assign the SQL server max memory of 152 + a little bit more of others to SQL server?

Or leave it as default- not to assign max memory, it will probably adjust itself dynamically, most cases it will use up all the RAM of the server which is far more than 152, is that still in license compliance ?

frederico_fonseca SSCoach Points: 16155 More actions · Answer 4

With standard even if you set max memory higher it will only use the 128 so do not worry about that aspect. So the correct thing to do here is to see what you will need to have running on the server (including SSIS if you need it and this can take up a bit of memory) and provision the server as required - and always a bit more for contigencies.

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Is it also a way to estimate by the size of database, for example if total size is 200GB, worst case put them all in the RAM, we should buy 200GB plus for RAM?

Also what is maximum size RAM allowed for SSIS?

Thanks,