CHAPTER  15

Selecting and Sizing the Server

By Glenn Berry

With the release of SQL Server 2012, Microsoft has moved to a new licensing model that is quite different from previous releases. Because of this, it is quite important to revisit how you go about selecting database server hardware in order to get good performance and scalability while still keeping your SQL Server 2012 license costs under control. SQL Server 2012 Enterprise Edition uses core-based licensing, where you pay for licenses based on physical processor cores, with a minimum of four physical core licenses required for each physical processor. This is quite a change from the socket-based licensing used by SQL Server 2008 R2 and all previous releases of SQL Server.

This new model came as an unpleasant shock to many people when it was first revealed in November 2011, causing some database professionals to immediately assume a worst-case scenario of a fourfold price increase for SQL Server 2012 licenses. This chapter will show you how to properly select your database server hardware to maximize your performance and scalability, while keeping your SQL Server licensing costs under control.

Depending on your workload type, you will want to make different hardware choices to get the best performance and scalability for your workload.

The hardware I look at in this chapter is specifically the server itself, meaning the form factor, and also the CPU and memory. Your CPU choice most strongly affects your licensing costs, and I spend a good deal of time looking at CPU choices with respect to SQL Server’s new licensing model. Microsoft’s new pricing scheme changes the game, and it’s important to revisit assumptions you might be holding that are no longer valid.

Understanding Your Workload

SQL Server places different demands on its underlying hardware depending on what type of database workload is running against the instance of SQL Server. Database engine workloads are quite different than file server, web server, or application server workloads. SQL Server instances that are running the database engine generally see one of two main types of workloads. One type is an online transaction processing (OLTP) workload, while the other type is a relational data warehouse (DW) workload.

OLTP workloads are characterized by a high number of short-duration transactions and queries that are usually executed on a single thread of execution. They can have a higher percentage of write activity, and the data in some tables can be extremely volatile. These characteristics have important implications for the hardware selection and configuration process. For example, you would want a processor with excellent single-threaded performance, as most queries use only a single thread of execution. You would also want an I/O subsystem that can supply a high number of input/output operations per second (IOPS), and one that has excellent write performance because of the volatility of the data and the high transaction log write activity.

DW workloads are characterized by longer running queries against more static data. These queries are often parallelized by the query optimizer, so having a higher number of physical cores in your processors can be very beneficial. Having a large amount of physical RAM is very useful for DW workloads, because you will be able to have more data in the SQL Server buffer cache, which will reduce the read pressure on the I/O subsystem. Outside of data loads, there tends to be very little write activity with a DW workload, which means the I/O subsystem would be provisioned and configured differently than for a typical OLTP workload. Many DW-type queries read large amounts of data as they calculate aggregates, so good sequential read I/O performance is very important. There is much less write activity (outside of data loads), which will also affect how you configure your I/O subsystem in terms of storage type and RAID level.

You should try to determine what type of workload your server will be supporting before you decide what type of hardware and storage subsystem to purchase. You also have to keep in mind that very few database workloads are pure OLTP- or pure DW-type workloads, so you will often have to deal with mixed workload types. You also might have to host multiple databases on a single SQL Server instance, where each database has a different type of workload. Another possibility is multiple databases on a single SQL Server instance where each database has the same type of workload, but the aggregate workload on the server ends up looking quite different from what you might expect. Given all of this, how can you determine your expected workload type?

If you have an existing server, running a production workload, you can run a number of Dynamic Management View (DMV) and Dynamic Management Function (DMF) queries, and monitor some key PerfMon counters to get a better idea about your existing workload type. For example, you can query the sys.dm_os_wait_stats DMV to see your top cumulative wait types since the last time that SQL Server was restarted (or the wait statistics were cleared) to get a better idea of your current bottlenecks.

For a new application that is under development, you can run these same queries and counters on your development and test servers, along with reviewing documentation and talking to the developers, business analysts, and end users to get a better idea about the expected workload type. The point of all this detective work is to have a better idea of the type of workload your hardware and storage subsystem is going to be dealing with, so you can make the best hardware selection and configuration decisions.

SQL Server 2012 Enterprise Edition Consideration Factors

One critical question that you must answer early on in the design process is whether you are going to be able to use SQL Server 2012 Enterprise Edition, or whether you will be forced to use a lower-end edition, such as SQL Server 2012 Standard Edition or SQL Server 2012 Business Intelligence Edition. Speaking as a database professional, I strongly believe that Enterprise Edition is very much worth the extra license costs, but I know that it can be a tough sell when you have a limited budget. Following is a listing of some of the more valuable features that you get in SQL Server 2012 Enterprise Edition:

• Data compression
• Enhanced read ahead
• Advanced scanning for reads
• Automatic use of indexed views
• Table partitioning
• Distributed partitioned views
• Scalable shared databases
• Asynchronous database mirroring
• AlwaysOn Availability Groups
• Online index operations
• Fast recovery
• Parallel index operations
• Database snapshots
• Hot-add CPU and RAM
• Online restore
• Resource governor
• SQL Server Audit
• Change data capture
• Mirrored backups
• Oracle replication publishing
• Peer-to-peer replication
• Transparent database encryption

Many of these features are extremely useful for improving the performance and availability of your SQL Server instance, so you should make a determined effort to convince your organization to purchase Enterprise Edition.

If you must use SQL Server 2012 Standard Edition, then you will be restricted to using no more than 64 GB of RAM for the database engine. You will also be restricted to using the lesser of four processor sockets or 16 physical processor cores in your database server. If that is the case, you do not want to make the mistake of specifying a big database server that is a four-socket server with 40 physical cores and 256 GB of RAM, because the SQL Server 2012 database engine will only be able to use a fraction of those hardware resources.

Don’t forget about the operating system version and edition as part of this decision process. There are licensing limits for maximum RAM, number of processor sockets, and total number of logical processors that are enforced by the operating system.

Windows Server 2008 R2 Standard Edition has a RAM limit of 32 GB and cannot use more than four processor sockets, while Windows Server 2008 R2 Enterprise Edition has a RAM limit of 2 TB and can use up to 256 logical processors. Windows Server 2012 Standard Edition has a RAM limit of 4 TB, and can use 640 logical processors. Windows Server 2012 Datacenter Edition has the same limits, but has higher virtualization limits.

Server Vendor Selection

Before you can start looking at specific server models and components, you need to determine what server vendor you will be dealing with. There are a number of what I consider “first-tier” server vendors out there, including the following (in alphabetical order):

• Dell
• Fujitsu
• HP
• IBM
• NEC
• Unisys

Each of these server vendors has advantages and disadvantages, depending on what your priorities are. Some vendors have less expensive hardware, while others have proprietary technologies that extend their capabilities beyond what is seen in a reference design from Intel or AMD (for a higher price). Some vendors (such as Dell) target the low- and mid-range market, so if you really need an eight- or sixteen-socket server, you will not be able to buy a Dell server because they sell only four-socket and smaller servers.

I am not going to recommend any specific server vendor in this chapter. Most companies and organizations have a single preferred hardware vendor that they deal with, for a number of valid reasons. For example, it is easier for your IT staff to support servers from a single vendor, as they are familiar with the models and how they are serviced. They can also take advantage of any cold spares or common components in the event of an emergency. If you are a large enough customer, you will also probably be able to get discounted pricing and expedited sales and service from your preferred server vendor.

If you are working at an “HP shop” or “IBM shop” (or any other vendor shop), it is usually fairly difficult to convince your management and the rest of the technical organization that you should buy a new server from a different server vendor. You might be able to play server vendors against each other by requesting quotes from multiple vendors for roughly equivalent equipment, and then use these quotes to get better pricing from your regular preferred vendor.

Despite all this, you should keep an open mind about server vendors, and try to stay current with what they offer for the types of servers that you commonly buy. It could be that you will discover some truly compelling features in a particular model from a different vendor that would make it worth it to try to convince your organization to buy that particular model server instead of something from your regular server vendor. In most cases, you will probably have to pick a server model from your preferred vendor, whether you like it or not.

Server Form Factor Selection

After you know the server vendor, you must decide whether you will be purchasing a rack-mounted server, a tower server, or a blade server. There are some pros and cons for each form factor that you should consider.

Rack-mounted servers are probably the most common form factor in data centers. They are a standard width and length, and are available in a number of standard vertical heights, which are known as rack units (as in 1U, 2U, 4U, etc.). They are usually mounted on rails and have cable management arms in the back that allow you to gently pull the server out of the front of the rack while it is running, without unplugging any of its cables. This lets you service many components while the server is powered on or do more major work while the server is powered off.

One small disadvantage of rack-mounted servers is that they are densely packed with various components, which can make them a little more difficult to service and somewhat harder to cool. Because of this, rack-mounted servers typically have a number of loud, small-diameter, high-velocity fans that pull cool air in through the front of the chassis and push the resultant hot air out of the back of the chassis. This is why data centers are arranged with alternating cold aisles and hot aisles between the server racks. The cold aisles have air conditioned cold air flowing up though holes in the raised floor of the server room that flows into the front of the server and then exits out through the back of the server. The hot aisles have air returns in the ceiling where the warm air rises and then exits the server room.

Tower servers are usually in tall and wide enclosures that sit on shelves instead of racks. They have more room inside the case and they are easier to work on once you have opened the enclosure. They do not usually have rails, so they often sit on the floor or on shelves in a rack. Some tower servers can be fitted with rails and mounted sideways in a rack, but this is not that common because it wastes vertical space in the rack compared to an actual rack-mounted server. Tower servers are usually considered to be entry-level servers that are more commonly used by smaller organizations because they can be less expensive than rack-mounted servers.

Blade servers are even smaller and denser than rack-mounted servers. They have a rack-mounted, blade enclosure chassis that holds common components for power, cooling, network connectivity, I/O connectivity, and management functionality. Multiple, individual blade servers are installed in a single blade enclosure, allowing you to have more individual servers and overall computing capacity in a smaller amount of physical rack space. While this sounds attractive, there are several issues you should be aware of if you are considering using blade servers for database servers. One problem is that the blade enclosure is a single point of failure for all of the blade servers that are installed inside the enclosure. If the blade enclosure has any hardware failures, it can affect all of the blade servers in the enclosure. This means that you would not want to have multiple nodes in a Windows Server failover cluster instance in the same blade enclosure chassis. The same caution holds true for multiple nodes in a SQL Server 2012 AlwaysOn Availability Group or for both sides of a database mirroring partnership.

The common infrastructure in the blade enclosure, especially the I/O interface between the blade servers and the actual storage subsystem, can become a performance bottleneck, especially as the number of blades increase. This is another reason not to use blade servers as mission-critical database servers.

It is standard practice for most database servers to use conventional rack-mounted servers. The main decision you will have to make is which vertical unit size (1U, 2U, 4U, or larger) that you want for your rack-mounted server. This choice is somewhat dictated by how many processor sockets are going to be in your server. Single-processor socket and dual-processor socket servers are available in a 1U vertical size. You can also get dual-processor socket servers in a taller, 2U vertical size that lets you have more internal storage drive bays in the chassis. You can get quad-processor socket servers in both 2U and 4U vertical sizes, with the larger size giving more space for internal storage drive bays. Finally, you can get higher processor socket counts in 4U or larger vertical sizes.

One thing to keep in mind is the overall power and cooling demands of multiple 1U and 2U rack-mounted servers in a single server rack. Dual-processor socket servers that are fully populated with internal drives, large amounts of physical RAM, and many PCI-E expansion cards can use a large amount of electrical power and generate a great deal of heat. This means that you can sometimes exceed the overall power capacity of a single 42U server rack if you populate the entire rack with 42 1U rack-mounted dual-processor socket servers. This problem is not as common as it was a few years ago as more modern processors that use less power and other more energy-efficient components become available. You should still check with your data center to see how much power and cooling capacity is available for each server rack.

Server Processor Count Selection

One huge decision that you have to make is how many physical processors will be in your database server. The number of physical processors in the server has a somewhat indirect relationship to the performance, scalability, and overall load capacity of the server. With SQL Server 2012 core-based licensing, the number of physical processor cores (which is related to the number of processor sockets) has a huge effect on the overall cost (including software licensing) of the server. It is very easy to spend far more on the SQL Server 2012 core licenses than you will spend on the actual server hardware and storage subsystem.

One common mistake that many database professionals make is to assume that a “bigger” server (in terms of physical processor count) is a faster server compared to a smaller server. In most cases, this is simply not true. As explained in detail later in the chapter, the specific processors used in dual-socket servers often perform much faster for single-threaded performance than their contemporary four-socket processor counterparts.

Traditionally, it has been quite common to use four-socket servers for database server usage because they had more total processor cores, more memory slots, and more expansion slots than a contemporary two-socket server. Two-socket servers that were available before roughly 2008–2009 were limited to about eight total processor cores, 32 GB of RAM, and two or three PCI-E expansion slots (used by things like RAID controllers, Host Bus Adaptors, and network interface cards), which meant they simply could not handle higher-intensity database server workloads.

Since then, processor core density has increased, so you can have between 16 and 32 physical processor cores in a two-socket server. Memory density has increased, with two-socket servers having up to 24 memory slots that can each hold a 32-GB DIMM, giving a total RAM capacity of 768 GB in a two-socket server. You can also have up to seven PCI-E 3.0 slots in a new, two-socket server, which is a significant increase in total I/O capacity over what was available several years ago.

The main point here is that a modern two-socket server has enough CPU capacity, memory capacity, and I/O capacity to handle a very high percentage of SQL Server workloads, so you should strongly consider selecting a two-socket server instead of a traditional four-socket server. An exception would be if you absolutely need more than 16 to 32 physical processor cores, or more than 768 GB of RAM, or more than seven PCI-E expansion slots in a single server, and you are unable to split your workload between multiple servers. If any of those conditions are true, you could be forced to move up to a slower and more expensive four-socket database server. Most people will probably be able to run most of their workloads on a modern, two-socket database server.

Dell 12th Generation Server Comparison

In order to make this discussion a little more concrete, it is useful to look at the various 12th generation, rack-mounted server models available from Dell, comparing them from a SQL Server perspective. There are currently seven models in this line, ranging from an entry-level, single-socket server to a four-socket server. All of these servers use the Intel Xeon E5 processor, but different models use different series of that processor family, which is a very important detail to pay attention to for SQL Server 2012 usage. You could easily compare the available server models from another vendor in the same way.

Dell PowerEdge R320

This model server has a 1U form factor, one processor socket, uses the Intel Xeon E5-2400 series processors, has six memory slots (96 GB total RAM with 16-GB DIMMs), has eight 2.5-inch drive bays, and has one PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of four, six, or eight physical cores for SQL Server 2012 core licensing purposes. It has a total of eight, twelve, or sixteen logical cores with Intel hyper-threading enabled.

The R320 is an interesting option for some smaller workloads because it uses the Xeon E5-2400 series Sandy Bridge-EN processor (that is usually used in two-socket systems) instead of the Xeon E3-1200 Sandy Bridge or Xeon E3-1200 v2 series Ivy Bridge processor that is used in most new single-socket servers. This lets you use up to 96 GB of RAM instead of being limited to 32 GB of RAM, and it lets you have up to eight physical processor cores instead of being limited to four physical processor cores. The downside of this is being limited to slower processor clock speeds with the E5-2400 series compared to the E3-1200 series processors, which means you will see slower single-threaded performance. The R320 might be a good choice for a DW type of workload, where the extra processor cores and higher memory capacity would be more useful. A single-socket server with an Intel E3-1200 v2 series processor would be better for a small OLTP workload. Keep in mind that SQL Server 2012 Standard Edition is limited to 64 GB of RAM.

Dell PowerEdge R420

This model server has a 1U form factor, two processor sockets, and uses Intel Xeon E5-2400 series processors. It also has twelve memory slots (192 GB total RAM with 16-GB DIMMs), has eight 2.5-inch drive bays, and has two PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, twenty-four, or thirty-two logical cores with Intel hyper-threading enabled.

This model is a bad choice for SQL Server 2012. The Xeon E5-2400 series Sandy Bridge-EN processor is very limited compared to the Xeon E5-2600 series Sandy Bridge-EP processor. It has slower clock speeds, less memory bandwidth, and less memory capacity. Since you pay the same amount for each SQL Server 2012 core license regardless of what type of physical core is in the processor, the E5-2400 series is a bad choice compared to the E5-2600 series. Another problem with this model server is the fact that it has only two PCI-E expansion slots and eight internal drive bays, which limits your overall I/O capacity and performance.

Dell PowerEdge R520

This model server has a 2U form factor, two processor sockets, and uses Intel Xeon E5-2400 series processors. It also has twelve memory slots (192 GB total RAM with 16-GB DIMMs), has eight 3.5-inch drive bays, and has three PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, twenty-four, or thirty-two logical cores with Intel hyper-threading enabled.

This model is also a bad choice for SQL Server 2012 because it uses the same Intel Xeon E5-2400 series processor as the R420. It does have four PCI-E expansion slots, which is a little better for I/O capacity and performance. Still, I would steer clear of both the R420 and R520 models for SQL Server usage.

Dell PowerEdge R620

This model server has a 1U form factor, two processor sockets, and uses Intel Xeon E5-2600 series processors. It has twenty-four memory slots (384 GB total RAM with 16-GB DIMMs), has ten 2.5-inch drive bays, and has one PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. It also has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, twenty-four, or thirty-two logical cores with Intel hyper-threading enabled.

The R620 is a much better choice for SQL Server 2012 than either the R420 or R520 because it uses the Intel Xeon E5-2600 series Sandy Bridge-EP processor. This processor series gives you higher clock speeds, higher memory bandwidth, and higher memory capacity compared to the Xeon E5-2400 series Sandy Bridge-EN processor. The R620 is limited to three PCI-E expansion slots, but it does have ten internal drive bays. Overall, it is a good model for use as an entry-level, two-socket database server.

Dell PowerEdge R720

This model server has a 2U form factor, two processor sockets, and uses Intel Xeon E5-2600 series processors. It has twenty-four memory slots (384 GB total RAM with 16-GB DIMMs), has sixteen 2.5-inch drive bays, and has six PCI-E 3.0 x8 and one PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, twenty-four, or thirty-two logical cores with Intel hyper-threading enabled.

The R720 is one of my favorite models in the Dell 12th generation line. It uses the same Intel Xeon E5-2600 series processor as the R620, but it has seven PCI-E expansion slots and sixteen internal drive bays, which combine to give you a lot of potential I/O capacity and performance. It does cost a little more than the R620, and it is in a 2U vertical size, so there are some scenarios where I would prefer an R620. An example scenario would be an OLTP workload where I knew that I would have external storage area network (SAN) storage with very good random I/O performance, and I wanted to be able to use 1U database servers instead of 2U database servers.

Dell PowerEdge R720xd

This model server has a 2U form factor, two processor sockets, uses Intel Xeon E5-2600 series processors, has twenty-four memory slots (384 GB total RAM with 16-GB DIMMs), has twenty-six 2.5-inch drive bays, and has four PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. It has a total of eight, twelve, or sixteen physical cores for SQL Server 2012 core licensing purposes. It has a total of sixteen, twenty-four, or thirty-two logical cores with Intel hyper-threading enabled.

The R720xd is similar to the R720, except that it has twenty-six internal drive bays and only six PCI-E expansion slots. This model could be a good choice if you can run your I/O workload on twenty-six internal drive bays, some or all of which could be solid state drives (SSDs). This could let you avoid the expense of an external direct-attached storage (DAS) enclosure or a SAN.

Dell PowerEdge R820

This model server has a 2U form factor, four processor sockets, and uses Intel Xeon E5-4600 series processors. It has forty-eight memory slots (768 GB total RAM with 16-GB DIMMs), has sixteen 2.5-inch drive bays, and has five PCI-E 3.0 x8 and two PCI-E 3.0 x16 expansion slots. It also has a total of sixteen, twenty-four, or thirty-two physical cores for SQL Server 2012 core licensing purposes. It has a total of thirty-two, forty-eight, or sixty-four logical cores with Intel hyper-threading enabled.

The R820 has four processor sockets in a 2U vertical size. It uses the Intel Xeon E5-4600 series processor, which has lower clock speeds than the Xeon E5-2600 series. There is also some nonuniform memory access (NUMA) scaling loss as you move from a two-socket to a four-socket server (i.e., a four-socket server does not have twice the scalability as a two-socket server with the exact same processor). The R820 does have sixteen internal drive bays and seven PCI-E expansion slots, so it has good I/O capacity and performance potential. It also has twice the total RAM capacity compared to an R620, R720, or R720xd. In spite of all these factors, I would tend to prefer two R720xd servers instead of one R820 server, assuming you can split your workload between two servers. You would have faster, less expensive processors, over three times as many internal drive bays, and nearly twice as many PCI-E expansion slots, while paying the same SQL Server 2012 license costs.

Dell Comparison Recap

I really like the R720xd, with its 26 internal drive bays. I suspect that a very high percentage of SQL Server workloads would run extremely well on an R720xd. If 26 internal drives did not give you enough I/O performance and capacity, you could always add some internal solid state storage cards or use some form of external storage.

As a database professional, I would be actively lobbying against using the R420 or R520 models, since they have the entry-level Intel Xeon E5-2400 series processors, which have lower clock speeds and less memory bandwidth compared to the Intel Xeon E5-2600 series processors that are used in the R620, R720, and R720xd. They also have half of the total memory capacity and far fewer PCI-E slots compared to the higher-end models. They are a little less expensive, but the hardware cost delta is pretty small compared to the SQL Server 2012 license costs. Remember, you are paying for SQL Server 2012 core licenses based on physical core counts, so you want to get the best package you can as far as the rest of the server goes. One nice fact is that the Intel Xeon E5 processor family is available in four-core, six-core, and eight-core models, with specific four-core models having higher base clock speeds than the “top-of-the line” eight-core model processor. If you wanted to minimize your SQL Server 2012 core-based licensing costs and were willing to give up some scalability and capacity, you could pick one of these faster base clock speed four-core model processors for your server and actually see very good single-threaded performance.

Processor Vendor Selection

In the Microsoft Windows and SQL Server world, you will have to pick between an Intel Xeon processor-based server or an AMD Opteron processor-based server. Your choice here directly dictates which model servers are available from your preferred server vendor, as only certain server models are available with an Intel or an AMD processor. Especially with the change to SQL Server 2012 core-based licensing, it is nearly impossible to recommend an AMD processor-based server for use with SQL Server 2012, especially for OLTP workloads.

There are a number of reasons for this rather strong assertion. First are the relatively high physical core counts in modern, high-end AMD Opteron processors, with models available with 12 or 16 physical cores. Each of these physical processor cores requires a relatively expensive SQL Server 2012 processor core license. That would perhaps be acceptable if each one of those physical cores provided excellent single-threaded performance, but sadly, that is not the case. You can easily check the relative performance per physical processor core for OLTP workloads by performing some simple arithmetic against official TPC-E OLTP benchmark scores. You can simply divide the actual TPC-E score for a system by the number of physical processor cores in the database server of the system under test (SUT), to get a score per physical processor core for a particular model of processor. This is a valid method, because the TPC-E benchmark is processor bound as long as you have sufficient I/O performance to drive the workload, which is a fairly safe assumption given the cost and time for a hardware vendor to develop and submit an official TPC-E benchmark submission.

For example, on July 5, 2012, Fujitsu submitted a TPC-E score of 1871.81 TpsE for a PRIMERGY RX300 S7 system that had two Intel Xeon E5-2690 processors that have a total of 16 physical cores in the system. Dividing 1871.81 by 16 physical cores gives you a score per physical processor core of 116.98.

On November 14, 2011, HP submitted a TPC-E score of 1232.84 TpsE for a Proliant DL385 G7 system that had two AMD Opteron 6282 SE processors that have a total of 32 physical cores in the system. Dividing 1232.82 by 32 physical cores gives you a score per physical processor core of 38.52. The score per physical processor core of the Intel system is 3.03 times as high as the score per physical processor core of the AMD system.

To make matters worse for AMD in this comparison, the SQL Server 2012 license costs for the AMD-based system are twice as high as for the Intel-based system, because you have to purchase 32 core licenses versus 16 core licenses.

The retail cost for SQL Server 2012 Enterprise Edition core licenses is $6,874. That means that you would pay $219,968 for 32 SQL Server 2012 Enterprise Edition core licenses for the two-socket AMD Opteron 6282 SE database server versus $109,984 for 16 SQL Server 2012 Enterprise Edition core licenses for the Intel Xeon E5-2690 database server. This means that you are paying twice the SQL Server 2012 core license costs for about one-third of the relative single-threaded performance.

Perhaps recognizing how bad this situation was for AMD, Microsoft released a document called the “SQL Server Core Factor Table” on April 1, 2012. This table allows you to adjust your physical core counts for licensing purposes for certain AMD processors, including AMD 31XX, 32XX, 41XX, 42XX, 61XX, 62XX Series Processors with six or more cores by multiplying your actual physical core count by 0.75, which essentially gives you a 25 percent licensing discount if you use an eligible AMD processor. In our earlier example, the SQL Server 2012 core licensing cost would be reduced from $219,968 to $164,976, which is a significant reduction. Still, it does not compare very favorably to the $109,984 license cost for the faster Intel-based system. Choosing the AMD-based system for SQL Server 2012 OLTP workloads would be fairly hard to defend to an intelligent CTO in your organization.

Another piece of evidence for this argument is the fact that there have been only four AMD-based TPC-E submissions out of a total of 54 total TPC-E benchmark submissions over the past five years. I honestly wish the evidence was not so grim for AMD, since healthy competition between AMD and Intel is good for everyone. If AMD cannot close this performance gap, Intel is very likely to slow down its pace of innovation and delay its release cycles to earn more profits out of its existing processor models for a longer period of time.

Processor Model Selection

Let us assume that you are convinced that you should choose an Intel processor for your SQL Server 2012 database server. The next step would be choosing which exact processor your new server should use. This is a very important decision, as it will have a huge effect on your SQL Server 2012 core license costs, and it will have a direct effect on the performance, scalability, and capacity of your database server. You are also likely to be stuck with your processor choice for the next several years. It is possible to upgrade the processor(s) in an existing database server, by buying a “processor kit” from your server vendor. The problem with this is that the server vendors typically charge several times the retail cost of a bare processor for a processor kit, so that it is often more affordable to buy a brand new database server rather than to upgrade the processors in an existing database server.

In fact, I have never seen anyone actually upgrade the processors in an existing database server in my entire career. I have seen people buy additional processors to fill empty processor sockets. The point of this is that you will, in fact, be using those original processors for the entire life of the server, which is another reason to choose wisely.

Prior to SQL Server 2012, my advice was to simply select the “top-of-the-line,” most expensive processor available for the particular model server that you were considering for purchase. This was because older versions of SQL Server used socket-based licensing, where you did not pay anything extra, regardless of how many physical cores were in a particular processor. It was foolish not to get that “top-of-the-line” processor back then, since your socket-based licensing cost was the same regardless of your processor choice. Despite this, I saw many people select low-end to mid-range processors to use with their very expensive (roughly $27,000 each) SQL Server 2008 R2 Enterprise Edition processor licenses, in order to save a few hundred dollars per processor but take a very noticeable performance and scalability penalty.

With the licensing changes in SQL Server 2012, you will have to rethink this old processor selection strategy. Depending on your budget and objectives, you can go in one of two directions. First, if your organization is running mission-critical applications that will be using your database server, and you have sufficient budget to work with, you are still going to want to pick that “top-of-the line” processor anyway, despite the added SQL Server 2012 core licensing costs. Those extra licensing costs might seem like a lot of money to you, personally, but to many large organizations, these costs can be quite trivial.

As a negotiating strategy, I try not to negotiate with myself. I will always ask for what I really want, and then come down only if it is really necessary. If you start out with a mid-range processor, and then end up getting a lower-end processor to save on hardware and licensing costs, you are the one that will get the blame if you have performance or scalability problems in the future. You will also have to live with the reduced performance and capacity over the life of the server, and the users of your application will also have to suffer with that reduced performance. After all, I have never heard anyone complain that their database server was too fast, but I have often heard complaints that it was too slow.

Second, if your application is not mission critical, or your organization honestly cannot afford to buy a larger number of SQL Server 2012 core licenses, then you can purposely select a lower core count processor that might have a higher base clock speed. This method can save a lot of money on SQL Server 2012 core licenses, at the cost of scalability and capacity headroom. If you go down this route, you should make sure everyone knows what you are doing and why you are doing it, and is in agreement with what you are doing. This can help you avoid being blamed if you have scalability or capacity issues in the future.

Walking through an example of how this might work, you could buy a two-socket database server that had two Intel Xeon E5-2690 processors that have eight physical cores each, for a total of sixteen physical cores in the server. The SQL Server 2012 Enterprise Edition core licensing cost for this one database server would be sixteen times $6,874 for a total of $109,984. If you wanted to focus on reducing your license costs for this server, you have two viable alternatives. One option would be to select two Intel Xeon E5-2643 processors that have four physical cores each, for a total of eight physical cores in the server. The SQL Server 2012 Enterprise Edition core licensing cost for this one database server would be eight times $6,874 for a total of $109,984.

A second option would be to select only one Intel Xeon E5-2690 processor that has eight physical cores, for a total of eight physical cores in the server. The SQL Server 2012 Enterprise Edition core licensing cost for this one database server would be eight times $6,874 for a total of $109,984.

There are some key differences between these two lower-cost options. The E5-2643 processor runs at a higher base clock speed of 3.3 GHz, with the ability to use Intel Turbo Boost to increase the clock speed of individual cores to 3.5 GHz (when all of the cores are not running at full speed already). This compares to the E5-2690 processor with a base clock speed of 2.9 GHz and a maximum Turbo Boost speed of 3.8 GHz. A heavily loaded system with E5-2643 processors will be running the processor cores at 3.3 GHz most of the time, with occasional bursts to 3.5 GHz for individual cores. A heavily loaded system with the E5-2690 processor will be running the processor cores at 2.9 GHz most of the time, with occasional bursts up to 3.8 GHz for individual cores. Some of the relevant specifications for these two processors are shown in Table 15-1.

Purposely leaving one socket empty would leave you with a lot of reserve capacity that you could take advantage of by purchasing a second processor in the future (and paying for the extra SQL Server 2012 core licenses). Having both sockets populated with the four-core processor would force you to replace both processors with something better if you ever needed to upgrade. That would be a harder proposition to sell in my experience, especially since the system vendor would likely charge a premium for the processor kits. You would also have the two original processors that would most likely end up in a drawer somewhere as rather expensive paperweights.

A two-socket system with either one of these processors would support 384 GB of RAM using 16-GB DIMMs if both processor sockets were populated. If only one processor socket were populated, the maximum RAM amount would be reduced to 192 GB using 16-GB DIMMs.

If I had to choose between these two alternatives, I would likely lean toward having one E5-2690 processor instead of two E5-2643 processors, as the upgrade path would be easier and less expensive.

Memory Selection

Generally speaking, I like to specify database servers with a large amount of RAM. Currently, all new Intel-and AMD-based servers use error correcting code (ECC) DDR3 RAM, but ECC DDR4 RAM will start to be used in new servers by 2014. Over time, ECC DDR3 RAM prices have steadily declined to the point where this type of RAM is extremely affordable.

Having a large amount of physical RAM in your database server allows SQL Server to have a larger buffer pool, which is used for caching data that has been read into memory from the storage subsystem. Any data that is in the SQL Server buffer pool can be retrieved with a logical read instead of requiring a physical read from the storage subsystem. Logical reads are much faster than physical reads, even if you have flash-based storage.

Ideally, your entire database would fit into RAM, which would dramatically reduce the read I/O pressure on your storage subsystem. Having a large amount of RAM will also reduce how often the SQL Server lazy writer has to run, which can reduce the write I/O pressure on your storage subsystem. Buying more RAM is much less expensive than upgrading your storage subsystem.

Given all of this, how much RAM should buy for your new database server? The first decision point is whether you have SQL Server 2012 Enterprise Edition or not, as this directly affects how much RAM you can actually use for SQL Server.

SQL Server 2012 Standard Edition is limited to using 64 GB of RAM for the database engine and 64 GB of RAM for SQL Server Analysis Services (SSAS), so you could theoretically use 128 GB of RAM between the two (even though it is not a best practice to run the database engine and SSAS on the same instance for performance reasons).

SQL Server 2012 Business Intelligence Edition will let you use the operating system maximum for SSAS, and up to 64 GB of RAM for the database engine.

SQL Server 2012 Enterprise Edition can use up to the operating system maximum for the database engine, and up to the operating system maximum for SSAS. The operating system maximum is 2 TB for Windows Server 2008 R2 and it is 4 TB for Windows Server 2012. It is currently possible to get 2 TB of RAM in a commodity four-socket machine if you are willing to pay the premium for 32-GB DIMMs. The current sweet spot for memory module size is 16 GB, as you get a relatively large memory module for an affordable price.

If you will be running SQL Server 2012 Standard Edition, I don’t see any reason to get less than 64 GB of RAM in your database server. I would strongly consider getting 80 GB using ten 8-GB DIMMs, so that you can set the instance level Maximum Server Memory setting to 64000 and still leave plenty of RAM for the operating system.

With SQL Server 2012 Enterprise Edition, I would want to fill up every available memory slot in the server with 16-GB DIMMs if at all possible, since server RAM is so affordable and so effective for improving performance. Because your organization has already spent the money for Enterprise Edition, it is foolish to hobble the performance and scalability of the server by saving a relatively small amount of money on RAM. For example, a two-socket database server with two Intel Xeon E5-2600 series processors can use 24 memory slots. The current cost for 24 16-GB ECC DDR3 DIMMS would be $6,240, which is a very small fraction of the SQL Server 2012 Enterprise Edition license cost for that database server.

Table 15-2 shows the relative prices for various sizes of memory modules in late 2012.

One last factor to consider is the effect of different memory configurations on the memory bandwidth of the memory. As you add more DIMMs to a system to populate more of the available memory slots, the bandwidth of the memory can decrease, which can hurt memory access performance somewhat. This effect varies based on the processor and the associated, integrated memory controller that is being used. The latest Intel processors are much less susceptible to this problem. Regardless of this, I still favor using large amounts of RAM, because even “slower” RAM is much, much faster than your storage subsystem. If you are concerned about this, you should consult the documentation for your server to see the recommendations for how you should configure your memory.

Conclusion

This chapter has covered a lot of ground about a very complex subject. In order to get the best performance and scalability possible from your database server without spending a small fortune on SQL Server 2012 core license costs, you need to make wise choices, especially when it comes to the exact model of processor that you select for your database server. Because SQL Server 2012 uses core-based licensing, it is very important that you choose a processor that has very good single-threaded performance so that you get the most performance possible for each of those expensive core licenses. You also want to minimize your total physical core counts to minimize your licensing costs.

Two-socket servers are typically one generation ahead of their four-socket contemporary counterparts when it comes to processor technology. They also have much higher memory density and more PCI-E expansion slots than they did in the past, so they are an extremely attractive choice for many database server workloads.

You need to know whether you will be using SQL Server 2012 Enterprise Edition or whether you will have to use a lower edition, as this will affect how much RAM you can use and how many sockets and physical cores that you can use for SQL Server 2012.

You should take advantage of the extremely low pricing for ECC DDR3 server RAM by buying a larger amount of RAM for your new server. There is really no longer any excuse to have a new server with only 16 GB or 32 GB of RAM.

Finally, you need to understand your workload so that you can make the right hardware choices to get the best performance and scalability for that type of workload.