Infrastructure Recommendations - Memory vs Processors

Question

Infrastructure Recommendations - Memory vs Processors

jackimo

SSCommitted

Points: 1885
More actions
September 5, 2017 at 7:26 am

#339684

So I've been tasked to plan a new SQL Server environment, with very little guidance or requirements.
The SQL server will host of variety of COTS (some customized) applications.
I have some experience in this regard but I notice a few other sql servers in our data center that predate my employment. Their ratio of cores/memory seems strange to me.
16 GB of memory with 32 cores. CPU usage is near 0, and memory usage is high. Per configuration.
So question for others out there -
This there some general guideline at what point is the ratio of cores/GB of memory is ridiculous for SQL server?

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

I'm not aware of a specific type of recommendation along those lines. I can say, 16gb of memory and 32 cores is NUTS! My laptop has 16gb of memory for crying out loud. SQL Server can use every bit of memory you throw at it. You can always use more memory. Memory is faster than disk (even the modern disks). If I were picking what I'd spend money on, memory or CPU, it would be memory.

Generally, I recommend measuring the existing systems to see what their behavior is like and then buy a machine that addresses the bottlenecks and pain points. If you have no guidance at all on new applications, then look to the vendors web sites to see if they have documentation.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jackimo SSCommitted Points: 1885 More actions · Answer 2

Grant Fritchey - Tuesday, September 5, 2017 8:09 AM
I'm not aware of a specific type of recommendation along those lines. I can say, 16gb of memory and 32 cores is NUTS! My laptop has 16gb of memory for crying out loud. SQL Server can use every bit of memory you throw at it. You can always use more memory. Memory is faster than disk (even the modern disks). If I were picking what I'd spend money on, memory or CPU, it would be memory.
Generally, I recommend measuring the existing systems to see what their behavior is like and then buy a machine that addresses the bottlenecks and pain points. If you have no guidance at all on new applications, then look to the vendors web sites to see if they have documentation.

Thanks Grant!
This was my feeling as well but I wasn't around for the build of this server. A quick review of the server as shown me that there really isn't any memory pressure as well, its just that we wasted our money on processors and licensing.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Unless you are running VERY small databases, anything less than the maximum your version of SQL Server can run on (for standard edition it is 64GB pre 2014, 128GB 2014 and up) is, to paraphrase Grant, NUTS!! :hehe:

Another thing you best be monitoring before you provision is IO bandwidth/latency. In some fashion or another that is responsible for at least half of all performance problems I come across. Note that many of those problems are self-inflicted by the client due to a wide-range of worst-practices!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

jackimo - Tuesday, September 5, 2017 7:26 AM
So I've been tasked to plan a new SQL Server environment, with very little guidance or requirements.
The SQL server will host of variety of COTS (some customized) applications.
I have some experience in this regard but I notice a few other sql servers in our data center that predate my employment. Their ratio of cores/memory seems strange to me.
16 GB of memory with 32 cores. CPU usage is near 0, and memory usage is high. Per configuration.
So question for others out there -
This there some general guideline at what point is the ratio of cores/GB of memory is ridiculous for SQL server?

There's no specific guidelines and it's mostly data size and usage/traffic dependent. However, what you have is only 500MB per core. Unless all of your main working data fits cleanly with some headroom to spare into that 16GB then, to reiterate what others have stated, that's just nuts. Memory is comparatively cheap compared to a lot of other things having to do with database. Even if the max your edition of SQL Server would only be 64GB, there also has to be some headroom for the operating system. Go for whatever the max your operating system will allow. If you have the "Data Center" version (for example) of the operating system and you've cheaped out on the edition of SQL Server that can only use a very limited amount of memory, that's a whole 'nuther problem and would probably be worth the upgrade to something a bit more robust.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)