Microsoft SQL Server Hardware Evaluation and Sizing – Best Practices Guide (Part 1)
Note: Part two to this series, including CPUs selection and benchmarking tools overview, can be found HERE.
Introduction
As my last client engagement required me to provide a suitable server hardware specifications for the upcoming data warehouse deployment I though it would be a good idea to revise SQL Server hardware sizing best practices and what considerations should be given to ensure the data warehouse performance is not compromised by the inadequate components selection. The cardinal mistake most professionals make during the hardware assessment stage is that no distinction is made between the projected database workload i.e. OLTP vs DW/DSS and no consideration given to the licensing aspects, which in case of SQL Server can easily surpass infrastructure cost. Also, often very little analysis is conducted to examine existing or projected storage, CPU, and memory requirements. If SQL Server can run on a tablet (think Microsoft Surface) why would anyone in their sane mind procure a hundred thousand dollar server? A database is a database, just like any other application, right? Not quite!
To make matters worst, the sprawl of cloud-based services with their elastic hardware-sizing capabilities has exacerbated this lassitude further and even though the concept of dynamic hardware sizing is a great in principle, there is still a huge number of businesses that can only host their data locally and are not ready for IaaS deployments. As a result, correct hardware sizing is paramount as future expansion can become very cumbersome if under-provisioned. Similarly, if over-provisioned, the hardware could be under-utilized due to licensing restrictions thus making the whole endeavor a rather expensive mistake.
There is a number of techniques and best practice guidelines that can help with appropriate hardware sizing so let’s look at some of the key factors (budget issues aside) that should be analysed in order to make the most accurate decision.
The Importance of Proper Hardware Selection and Sizing
The choice of hardware will have a huge (adverse or favorable) effect on the overall SQL Server cost and performance. As total processor count (with the exception of SQL Server BI Edition) drives licensing cost, making poor selection a potentially very expensive mistake. Unfortunately, most decisions on what CPU to procure are taken haphazardly and with little consideration for the minor details which can influence the overall performance a great deal. Classic examples of this technical shortsightedness is businesses unknowingly purchasing servers with high core-count CPUs or higher socket-count motherboards, falsely believing that a higher core/socket number is always the cheapest option to maximize SQL Server capacity or opting for older, antiquated hardware hoping to save a little bit of money. Similarly, sometimes to much emphasis is put on CPU selection, with little consideration for I/O subsystem performance e.g. required IOPs and sequential throughput, RAID level for performance and redundancy, architectural choices (DAS, SAN etc.), required amount of storage space etc. in view of the magnitude of database workload.
Short Version
When sizing hardware for any database taking advantage of SQL Server technologies there are two approaches one should consider, each depending on the type of workload the database is designed to perform. The grossly simplified distinction concerning performance between OLTP and data warehouse databases is as follows.
Based on the above requirements, the following rudimentary recommendations can be made when sizing hardware for the fastest OLTP database.
Likewise, the following can be more applicable when building a DW/DSS workload-oriented system.
To put some concrete albeit very approximated numbers around these recommendations the following table provides a rough guideline on the core hardware components sizing when building or upgrading SQL Server infrastructure.
If the above still sounds too complex, different vendors offer hardware sizing tools which can provide ballpark estimates based on the number of parameters used when filling out a survey on current or projected SQL Server environment. For what it’s worth I found those hardware recommendations on the excessive side but as the saying goes – “Nobody ever got fired for buying IBM equipment” – can now be extended to Dell and HP as well.
Dell provides their web-based SQL Server adviser under the following link: http://goo.gl/u6X3s3
HP also provides their desktop sizing tool for SQL Server which can be downloaded here: http://goo.gl/jmQos4
TL, DR Version
SQL Server sizing can be a difficult task so let’s analyse some of the core factors that have the biggest impact on the subsequent performance level (this post will be spread across two sections, second one accessible from HERE).
I have divided the most important aspects influencing hardware selection that one should take into consideration into five categories which I will describe in this and the following post. These are workload type, SQL Server edition/licensing, CPU selection, storage selection and memory selection.
Workload Type
One of the first factors to consider when making a decision on appropriate hardware sizing is to consider SQL Server workload type. Typical workload types that SQL Server is usually deployed with are OLTP (online transaction processing), DW/DSS (data warehouse/decision support), OLAP (Online Analytical Processing) and a mixture of those with some backups and maintenance activity thrown in e.g. OLTP database that also serves as a reporting source or DW/DSS database with frequent updates.
The following characteristics overview provide a good summary of the contrasts present between the two most commonly workload types.
Based on those characteristics, it is easy to see that the two types of workloads require a slightly different approach to hardware selection. Below are some of the key factors that should be considered when choosing and sizing hardware based on the workload type and intensity.
Licence Type
SQL Server 2014 comes in a few different editions, each with its own set of licensing requirements that impact the hardware they may be installed on. The main differences between those flavors are as per below.
As you can see from the above, the dichotomies between SQL Server licences can make it tricky to decide which edition would best suit your needs and workload. The important thing to remember is that the core differences between licensing model is based on the paradigm of core vs server-based licensing which is tied directly to SQL Server 2014 editions i.e. Enterprise and Standard editions are core-based (Standard Edition can be licensed as either – core-based or server-based) whereas Business Intelligence Edition uses server-based licensing, allowing for a large number of cores to be utilized without increased licensing cost. What makes it even more complicated is the fact that Standard Edition and BI Edition are limited to the lesser of four sockets or sixteen logical cores for the database engine and 128GB of RAM so procuring, for example, a four-socket server with high core count will mean that not all available resources can be used – a rather expensive mistake. Similarly, licensing cost can far outweigh hardware cost so analyzing hardware requirements and associated cost without looking at the bigger picture can lead to false or incomplete assumptions and bring the cost up by a considerable margin.
Bearing that in mind, the licensing model most appropriate to the given workload and budget requires a careful consideration in view of the benefits and disadvantages it can pose so analyzing a detailed a comparison of each SQL Server edition, not only from the hardware perspective, is a must. A very detailed outline of most of the key features of each edition can be found HERE.
This concludes the first part of this series with the second installment describing CPU, memory and storage considerations available HERE.
http://scuttle.org/bookmarks.php/pass?action=addThis entry was posted on Thursday, October 2nd, 2014 at 1:46 am and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
