The Accidental DBA (Day 1 of 30): Hardware Selection: CPU and Memory Considerations

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at https://www.sqlskills.com/help/AccidentalDBA. Enjoy!

As an accidental (or even a regular) DBA, you need to be aware of the exact type of hardware you have for your existing database server(s). This applies whether your servers are running bare metal or if you are using virtualization. It is unfortunately very common for DBAs to have only a vague idea of the specific hardware details for their database servers, which is not a good state of affairs. You should know the brand and model number of the server, the exact model of the processors in the server, and how much RAM you have in the server. You should also find out as much information as you can about your storage subsystem.

There are a number of ways to find out this information, depending on whether you are able to remotely logon to the server itself, or whether you can only connect to it from SQL Server Management Studio (SSMS) from your workstation. If you can logon remotely, you can look at the results of msinfo32.exe, you can look at the Properties page for the Computer, and you can run the CPU-Z utility to find out this sort of information. If you are running Windows Server 2012, much of this information is exposed in the Performance tab of Windows Task Manager. If you only have SSMS access, there are are several queries from my SQL Server Diagnostic Information Queries that retrieve this information.

Once you know what type of hardware you have in your existing servers (and how hard it is working), you are in a much better position to start looking at intelligently selecting a new server to handle the type of workload that you will be dealing with. Erin Stellato has a great Pluralsight course called SQL Server: Benchmarking and Benchmarking that can help you determine how hard your current server is working and what its bottlenecks currently are. Jonathan Kehayias has a very valuable book (which all DBAs should have on their bookshelf) called Troubleshooting SQL Server: A Guide for the Accidental DBA that has a lot of useful information on this subject.

With the licensing changes that were introduced with SQL Server 2012, the seemingly minor details of the exact model processor you select for your database server will have a huge effect on your SQL Server 2012 licensing costs, along with your performance and scalability. Your SQL Server 2012 license costs are very likely to be significantly higher than the hardware cost for the server, so you need to chose wisely in order to not make some expensive mistakes.

Rather than the old familiar socket-based licensing used in SQL Server 2008 R2 and older versions, SQL Server 2012 uses a combination of core-based and Server plus Client Access License (CAL) based licensing, depending on which Edition you buy (and which licensing choice you make for Standard Edition). With SQL Server 2012 Standard Edition, you can choose core-based licensing or Server plus CAL-based licensing. With Business Intelligence Edition, you have to use Server plus CAL-based licensing, while Enterprise Edition requires the use of core-based licensing.

Standard Edition is the base edition, with a limit of 16 physical processor cores or four physical sockets (whichever is lower), and a 64GB RAM limit for the Database Engine or Analysis Services. SQL Server 2012 Business Intelligence Edition includes all of the functionality of Standard Edition, plus extra BI features and functionality. Enterprise Edition includes everything in BI Edition, plus all of the extra Enterprise Edition features and functionality. Enterprise Edition is the top of the line edition of SQL Server 2012, able to use the operating system limit for both logical processor cores and memory. If you are using core-based licensing (like you must for Enterprise Edition), each physical socket in your server must use a minimum of four core licenses. That means if you have very old hardware that uses dual-core processors, you would still have to buy four core licenses for each socket. That is yet another reason to not use ancient hardware for a new version of SQL Server! Keep in mind that only physical cores count for licensing purposes (on non-virtualized servers), so Intel hyper-threading comes for free.

Core licenses are sold in two-core packs, again with a minimum of four-cores per physical socket. The full retail license cost per physical core is $6874.00 for SQL Server 2012 Enterprise Edition. This is pretty grim news for AMD, with their higher physical core counts (up to 16) and lower per-socket performance compared to Intel.  To try to compensate for this disadvantage, Microsoft has a SQL Server Core Factor Table that gives a 25% license cost discount for most modern AMD Opteron processors that have six or more processor cores. You can download it here (PDF warning). Even with this discount, it simply does not make economic or technical sense to chose an AMD Opteron processor for a SQL Server 2012 OLTP workload.

With core-based licensing, it is very important to get the best single-threaded processor performance possible for each one of those relatively expensive core processor licenses. Based on the cumulative TPC-E OLTP benchmark results over the past six years, you will get much better single-threaded processor performance and much lower SQL Server 2012 license costs with an Intel processor compared to an AMD processor. So the next question is what Intel processor should you select for your database server?

Because of the way that Intel designs and markets their server processors, the processor families that are used in two-socket servers are refreshed more frequently and much earlier than the processor families that are used in four-socket and larger servers. This means that the latest two-socket models are often a generation ahead (in terms of processor microarchitecture) of the latest four-socket models. Even within the same generation microarchitecture, you will see a significantly higher base clock speed and turbo boost clock speed with the two-socket models compared to the four-socket models.

Historically (back before 2008), two-socket database servers simply did not have enough processor capacity, memory capacity, or I/O capacity to handle many more demanding database workloads. Back in 2007, a two-socket server would typically have been limited to about eight processor cores, 32GB of RAM, and two or three PCI-E 1.0 or 2.0 expansion slots, which made it much more challenging to run a large database workload on a two-socket server in the past.

Processors have gotten far more powerful in the last few years, and memory density has gone up dramatically. Modern two-socket servers have 24 memory slots, which means that you can have 384GB of RAM with affordable 16GB DDR3 DIMMs or 768GB of RAM with more expensive 32GB DDR3 DIMMs. The price/GB of 32GB memory modules is still about $39/GB compared to about $13/GB for 16GB memory modules, but the prices for the larger modules has fallen 25% in the last two months. It is also possible to get much more I/O capacity connected to a two-socket server than it was a few years ago. The latest generation, two-socket servers that use the Intel Xeon E5-2600 series processor can have six or seven PCI-E 3.0 expansion slots, that each have twice the bandwidth of the older PCI-E 2.0 standard that is still used in four-socket servers.

The final reason to think about this issue is the cost of SQL Server 2012 core licenses. If you can run your workload on a two-socket server instead of a four-socket server, you can save over 50% on your SQL Server core-based license costs, which can be a very substantial savings! Even with SQL Server 2012 Standard Edition licenses, the license cost savings would pay for a very capable two-socket database server (exclusive of the I/O subsystem).

All this means that unless you are quite sure that a modern, two-socket server simply does not have the overall capacity to handle your workload, you will be much better off to choose a two-socket server instead of a four-socket server. The reality is that “bigger” servers are not faster servers. It is like comparing a very large truck to a Tesla Model S. The truck can carry more cargo, much more slowly than the Tesla, but the Tesla is much faster.

Given the way that the Intel Tick-Tock processor release strategy works, two-socket servers get the latest processor microarchitectures and manufacturing process technology releases significantly sooner than when these same advances show up in the four-socket space. Right now, two-socket servers have the 32nm Sandy Bridge-EP, while four-socket servers are still using the much older 32nm Westmere-EX that has higher core counts but lower single-threaded processor performance.

This performance gap will be even wider in Q3 of 2013, when the upcoming 22nm 12-core Ivy Bridge-EP is released. Four-socket servers will finally get caught up somewhat in Q4 of 2013, when the 22nm 15-core Ivy Bridge-EX is released but Ivy Bridge-EX  will still have higher core counts and lower single-threaded processor performance than Ivy Bridge-EP. The gap will open up again, probably in early 2015, when Haswell-EP is released. Figure 1 shows the Intel Tick-Tock Development Model.


Figure 1: Intel Tick/Tock Development Model

Our online training (Pluralsight) courses that can help you with this topic:

3 thoughts on “The Accidental DBA (Day 1 of 30): Hardware Selection: CPU and Memory Considerations

  1. Hello,
    Pedant here. You blogged, quote “This applies whether your servers are running bare metal or if you are using virtualization.”.
    Please could you define exactly what you mean by “bare metal” as I think you have used it out of context? There is “bare metal restore” and “bare metal programming”. It can also refer to virtualization or to a computer without an operating system. I don’t think you mean either of those as even Windows Server Core is still an operating system and you still need an operating system to run DBMS software. Please elaborate.

    1. I thought it was obvious from the context of the sentence (but apparently it was not), that “bare metal” meant running in a non-virtualized environment. The term “bare metal” is pretty commonly used for that situation, although it is not 100% technically accurate, since you would obviously still need to have some version of Windows Server, along with SQL Server installed on the actual bare metal…

      1. Yes, it was completely obvious. But he did identify as a pendant, to his credit.

        a person who makes an excessive or inappropriate display of learning.
        a person who overemphasizes rules or minor details.
        a person who adheres rigidly to book knowledge without regard to common sense.
        Obsolete. a schoolmaster.

        I have additional definitions, but decorum dictates that I keep those to myself.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.