SQL Server Hardware
by Glenn Berry
Simple Talk Publishing, 2011
ISBN: 978-1906434632

Download a free PDF version
Purchase Hard Copy: Amazon US | Amazon UK
Kindle Edition: Amazon US | Amazon UK

I’ve noticed that many of the DBAs I’ve talked to over the years have become less interested in the SQL Server infrastructure. I have heard many DBAs say that they leave the selection and configuration of the hardware and the operating system (OS) to others, such as system administrators.

I can fully understand why some DBAs feel this way. It is difficult enough to master the many facets of SQL Server without having to master the hardware and OS as well. On the other hand, DBAs who delegate hardware and OS selection and configuration are making a mistake they will eventually regret. This is because the hardware and the OS provide the foundation of SQL Server high performance and availability. If the foundation is solid, then SQL Server will perform as expected. If the foundation is weak, then SQL Server’s performance and availability will never be as good as the DBA or the organization expects.

Sure, some DBAs might be lucky with the hardware and software selection and configuration performed by a system administrator; but based on my personal experience; this is often not the case. That is why I insist on selecting and configuring the hardware and OS myself, to ensure that it is appropriate to meet the needs of a SQL Server instance and its particular workload. I have had enough bad experiences in the past to know that it is unwise to assume that system administrators will get it right, especially given their lack of detailed knowledge of how SQL Server works.

I have an advantage over many DBAs because I was a systems administrator before becoming a DBA, and I am still very familiar with hardware and the OS. So what about those DBAs who don’t have that background? How can they learn what they need to know about hardware and the OS so they can ensure that the proper selection and configuration is optimal for a particular SQL Server instance?

One option is for DBAs to get their CompTIA A+, CompTIA Network+, CompTIA Server+, or CompTIA Storage+ certifications. Or they might consider getting the Microsoft MCSE Server Infrastructure certification. While these are great goals to work on, what about right now? How can DBAs get the basic knowledge in hardware and the OS they need in order to help them to specify and configure the optimum hardware and OS for their individual SQL Server instances?

One option to get DBAs up to speed quickly on hardware and OS selection and configuration is for them to read Glenn Berry’s book SQL Server Hardware. Don’t let the title fool you, as this book is not only about hardware selection and configuration. It also includes information on selecting and configuring the OS, selecting the right SQL Server version and edition, and on how to install and optimally configure a SQL Server instance before using it for the first time.

For those of you who don’t know Glenn, he is a SQL Server MVP and has entire slew of Microsoft certifications. He is a long-time Database Architect, a Principal Consultant with SQLSkills.com, and is an Adjunct Faculty member of the University of Denver. He blogs regularly at sqlserverperformance.wordpress.com, where he keeps readers up-to-date in the latest in hardware and other SQL Server topics.

SQL Server Hardware is different than most SQL Server books, which often tend to focus on general topics that aren’t directly related to the day-to-day tasks of the average DBA. On the other hand, Glenn’s book provides focused and practical information that can be put to use by DBAs as soon as they read the book. There is no theory, just real-world information based on Glenn’s many years of experience.

As you might imagine, given the title of the book, there is a comprehensive, in-depth look at virtually every aspect of hardware selection and configuration, with a special emphasis on CPU and storage selection and configuration. Glenn seems to have a special interest in CPU technology, and his discussion of CPUs can sometimes be a little hard to follow. But this is really not Glenn’s fault, as much it is the fault of Intel and AMD, who offer a complex array of CPU variations that seem to be a target that never sits still.

Once you get through a discussion of all the various CPU options and which ones work best for SQL Server, his actual advice essentially boils down to this: “Buy the newest and fastest CPUs available on the market when you purchase new hardware.” This is because each new generation of CPUs bring new features and benefits to SQL Server, and because you will probably be using those CPUs for many years, and you might as well get the best when spec’ing out a new system. But one thing he says may surprise you. Depending on the workload, he generally recommends getting the latest two-socket database servers instead of the latest four-socket database servers. This is because Intel almost always introduces their newest technology for their two-socket system before their four-socket systems, which often means that you can get better overall CPU performance (depending on the workload) from a new technology two-socket system over an older technology four-socket system. In addition, two sockets are much less expensive than four-socket system to license for both the OS and SQL Server. Of course there are exceptions to this, which are explained in the book.

Based on my personal experience, I have also always purchased the latest technology when building a new SQL Server, especially since you will never know how long a server will be in service. It is generally less expensive to purchase the best hardware upfront, rather than find out a little later that the hardware isn’t meeting the server’s workload needs, and then either having to upgrade the existing server, or replacing it with a new server.

Another topic that Glenn discusses is whether hyper-threading should be turned or not, along with a related discussion on how the ‘Maximum Degree of Parallelism’ SQL Server setting should be set. Like all good DBAs, Glenn’s answer is “it depends”. But Glenn doesn’t just stop there. He explains where hyper-threading is beneficial or not, along with how hyper-threading and ‘Maximum Degree of Parallelism’ interact with each other. In the end, there is no exact answer that will meet ever server’s needs, as each server has a different workload, and may require a different configuration to provide optimal performance.

While a lot of people chuckle when they hear “it depends” as an answer from an experienced DBA to a question they have, this is so very true. I think many DBAs laugh at this comment because they don’t understand what it really means, and they are too afraid to admit they don’t know. When an experienced DBA starts out giving an answer with “it depends”, what they are trying to tell you is that each workload on a SQL Server instance is unique, and that the only way to determine an optimum answer is to perform objective tests and see what works best for it.

For example, Glenn recommends that hyper-threading be turned on for OLTP loads but turned off for OLAP loads. In most cases, this is probably the best advice. But because each workload is different, you can’t accept such general advice without thoroughly testing it for yourself. When I configure a new server, I start out with the recommended best practices based on how I think the SQL Server instance will be used, and then I carefully monitor the instance over time to ensure that everything is working as it ought.

If I should see a potential problem after continued monitoring, it might be because one of the recommended best practices is not ideal for a particular workload, and needs to be changed. Once I identify what the issue is, then I will create a hypothesis that I can test (preferably on a test server). By a test, I mean I will change a single setting, such as turning hyper-threading off if it is currently on, and then monitor the system to see if the change fixed my problem. If the problem is not fixed, then I return to the original settings and then test another hypothesis, testing one at a time until I resolve the problem. Unfortunately, there is no single answer for every question, and testing is often required to determine an optimal solution.

The “it depends” theme permeates the book, although Glenn does not suggest how to monitor systems, how to diagnose problems, or how to best test a hypothesis to resolve problems. This is not a failing of the book, because this is a topic that is best covered in book with a different scope, such as Troubleshooting SQL Server: A Guide for the Accidental DBA, which is designed specifically for helping DBAs diagnose and resolve a variety of performance and other SQL Server-related problems.

As you know, technology changes quickly. Both hardware and software change very fast, much faster than authors can keep up with it. Even though this book was written in 2011, some of the content is already out of date. The book focuses on hardware sold in 2011, which is different than the hardware sold in 2012. In addition, the OS focuses on Windows 2008 R2, but Windows 2012 Server is just around the corner. The same goes for SQL Server. The book focuses on SQL Server 2008 R2, but SQL Server 2012 is now out.

Even though some of the book’s content is outdated (of course many of you are still using older versions of the hardware and software discussed in the book, so it is not outdated for you), the best practices discussed by Glenn in the book are still the same, no matter what hardware or software you are using. So whether or not you are using the hardware or software versions discussed in the book, I think you will find that this book is still extremely helpful, especially to those DBAs who don’t have a solid background in hardware or the OS. I highly recommend SQL Server Hardware, and I am sure that you will learn something new from reading it, no matter how experienced a DBA you are.