THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Rethink Server Sizing

Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996. There were very good reasons for this, but it was so long ago that they have been forgotten. Yet the practice continues unquestioned, almost as a reflex action ingrained in our subconscious. Back then, 2-way was the baseline, and a 4-way was for databases. Now, the large majority of standard systems are 2-way (2S) with 4S for exceptional requirements.

It might seem that the 2-socket system continues to be a good choice, as two processors with an intermediate number of cores is less expensive than one processor with twice as many cores. An example is the Xeon Gold 6132 14-core versus the Xeon Platinum 8180 28-core processors. In addition, the two-socket system has twice the memory capacity and nominally twice as much memory bandwidth.

XeonSP_2x14a

XeonSP_1x28a

So, end of argument, right? Well, no. This is because any multi-socket system, including the two-socket, has non-uniform memory access (NUMA). It so happens that database transaction processing performance is largely governed by round-trip memory access latency. Memory latency is lower on the one-socket system than on a multi-socket system unless the application has been architected to achieve a high degree of memory locality on a NUMA system. Almost zero real world databases have been architected for NUMA system architecture. So, memory access on a 2-way is most probably 50/50 to the local and remote nodes.

The implication of memory latency differences is that single thread performance decreases by about 25% going from 1S to 2S, with throughput scaling from 1S to 2S being around 1.55X. In this regard, two 14-core processors with 28 cores total has comparable throughput performance to a single 20-22 core processor. Given the cost of Enterprise Edition licensing, the difference in processor cost per core does not really matter. The system that can deliver the required performance with fewer cores will win on cost. The single socket system will have fewer unusual (interpret this as bad or very bad) characteristics than a multi-socket system.

Because of the memory latency effect, high processor frequency does not help much in transaction processing because most cycles are spent waiting. There is usually more than one frequency option at a given core count, usually with a difference in price. Finally, the expectation is that Hyper-Threading is highly effective for transaction processing, so this feature should be enabled. And in addition, Intel really needs to increase the degree of HT from 2 to 4 logical processors per core.

Single Socket System Recap (edit 12-27)

  1. 16-28 cores with HT can handle most transaction workloads
  2. Memory capacity massive overkill not needed with storage on flash/SSD
  3. Single socket has best performance per core versus multi-socket
  4. Makes most effective use database per-core licensing
  5. Avoids problematic NUMA issues,
  6. Example: high volume inserts into table with identity key (or comparable)

 

Update: Rethink Server Sizing II
Full article on: Rethink Server Sizing 2017
Related: SRAM as Main Memory
and also on Linkedin Rethink Server Sizing 2017
and SRAM as Main Memory

Published Tuesday, December 26, 2017 12:00 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Maneesh said:

Really informative. Please provide some examples to get deep dive. For us beginner who learn from you will be obliged.

May 8, 2018 3:17 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Privacy Statement