Author:
This article is written by our contributing author Ken Lassesen. His bio can be found here .
Recently I have seen two configurations that caused me to shake my head in disbelief. In reality, the problem is the classic one of people working off valid but stale past knowledge. Two SQL Server configurations were:
- A 16 core machine running with 4GB of RAM, or 250MB per core
- A two socket (with 12 cores on each) running with 32 GB, or 1.5 GB per core
The second one actually made sense to me if it was two sockets running with a single core each. The implication of multiple cores per socket on memory requirements may not have registered .
I know well the problem of getting budget for hardware and the resulting accidental crippling of performance. Searching the internet will get you thousands of opinions but very little hard reliable data. Testing different installation configurations is time consuming and expensive – especially with the rate of technology change. So who are you going to call? Not Ghostbusters; the TPC folks!
Transaction Processing Performance Council
Their motto says it well: “ The TPC defines transaction processing and database benchmarks and deliver trusted results to the industry ”. They test various hardware configurations submitted by the industry and then calculate the cost per transaction . The winner is the cheapest cost – something that your firm’s Chief Financial Officer understands.
I went to the site and proceeded to look at the cheapest cost per transaction found on SQL Server for various benchmarks, focusing on the amount of RAM per core. If a hardware manufacturer could get the same performance with less RAM , they would have submitted the cheaper machine for testing. The following is what I found:
-
TPC-C
is an on-line transaction processing benchmark.
- Microsoft SQL Server 2005 Enterprise x64 Edition SP3 : 24 cores, 256GB = 10.6 GB/core
- Microsoft SQL Server 2005 Enterprise x64 Edition SP3 : 32 cores, 1TB = 32 GB/core
-
TPC-E
is a new On-Line Transaction Processing (OLTP) workload developed by the TPC.
- Microsoft SQL Server 2008 R2 Enterprise Edition : 12 cores, 96GB = 8 GB/core
- All of the Top Ten Price/Performance Results were Microsoft SQL Server 2008 R2 Systems [Feb 2011]
-
TPC-H
is an ad-hoc, decision support benchmark.
- Microsoft SQL Server 2008 R2 Enterprise Edition : 24 cores, 192 GB = 8 GB/core
- Microsoft SQL Server 2008 R2 Enterprise Edition : 12 cores, 192 GB = 16 GB/core
Feel free to check out other configurations listed there to find a close match for your Installation.
Bottom Line Memory Requirements
Having less than 8 GB of memory per core is not seen with the configurations above, and you will likely find memory pressure deteriorating SQL Server performance. My recommendation is to target 16GB/core. With a tight budget, this may mean picking slower older less-core CPUs to get more RAM.
“.. give you 8GB of RAM per CPU core. It might just be my point of view, but that’s not that much per core.”
CAVAET
There can be some limits on the maximum memory per core based on memory type, the number of memory channels for each core and other factors. The lower limit appears to be 12GB/core. For more information see Know Your RAM .