Glenn Berry

Database Hardware and Infrastructure Trends

The only software that truly measures all performance aspects of SQL Server Analysis Services.  More
Answers.SQLPerformance.com

Upload your tough execution plans and get helpful query tuning advice from renowned experts like Paul White.

Visit Site

Featured Author

Paul White is an independent SQL Server consultant specializing in performance tuning, execution plans, and the query optimizer.

Paul’s Posts

As I have been watching what has been going on recently in the Windows and SQL Server world, I have come to the conclusion that it is a particularly exciting time to be a database professional working with SQL Server, largely because of all of the recent and upcoming advances in technology. There are a number of useful and valuable new developments in hardware, storage, and the Microsoft Windows and SQL Server ecosystem.

Server Processors

Right now, you can purchase extremely capable, high performance server processors with physical core counts between four and twenty-two cores per processor. I am referring to the current 14nm Intel Xeon E5-2600 v4 (Broadwell-EP) and the 22nm Intel Xeon E7-8800 v3 (Haswell-EX) families that both use high bandwidth DDR4 memory.

On March 31, 2016, Intel released the 14nm Xeon E5-2600 v4 family (Broadwell-EP) for two-socket servers. This is a Tick release, building on the current Haswell microarchitecture that has up to 22 physical cores and DDR4 2400 support. This processor will work in existing model servers such as the Dell PowerEdge R730 with a BIOS update, which means that there will be less delay before they are actually available for sale.

You still have the flexibility to choose a particular processor based on its physical core count and clock speeds to balance your SQL Server core license costs with your performance and scalability needs. Tables 1, 2, and 3 show the best processor choices for a given core count for these two families.

Model

Cores/L3 Cache

Base Speed

Turbo Speed

Price (USD)
E5-2699 v4 22/55 MB

2.2 GHz

3.6 GHz

$4,115
E5-2698 v4 20/50 MB

2.2 GHz

3.6 GHz

$3,226
E5-2697 v4 18/45 MB

2.3 GHz

3.6 GHz

$2,702
E5-2697A v4 16/40 MB

2.6 GHz

3.6 GHz

$2,891
E5-2690 v4 14/35 MB

2.6 GHz

3.6 GHz

$2,090

Table 1 : Preferred High Core Count Broadwell-EP Processors for SQL Server Usage

Model

Cores/L3 Cache

Base Speed

Turbo Speed

Price (USD)
E5-2687W v4 12/30 MB

3.0 GHz

3.5 GHz

$2,141
E5-2640 v4 10/25 MB

2.4 GHz

3.4 GHz

$939
E5-2667 v4 8/25 MB

3.2 GHz

3.6 GHz

$2,057
E5-2643 v4 6/20 MB

3.4 GHz

3.7 GHz

$1,552
E5-2637 v4 4/15 MB

3.5 GHz

3.7 GHz

$996

Table 2 : Preferred Low Core Count Broadwell-EP Processors for SQL Server Usage

Model

Cores/L3 Cache

Base Speed

Turbo Speed

Price (USD)
E7-8890 v3 18/45 MB

2.5 GHz

3.3 GHz

$7,175
E7-8867 v3 16/45 MB

2.5 GHz

3.3 GHz

$4,672
E7-4850 v3 14/35 MB

2.2 GHz

2.8 GHz

$3,003
E7-4830 v3 12/30 MB

2.1 GHz

2.7 GHz

$2,170
E7-8891 v3 10/45 MB

2.8 GHz

3.5 GHz

$6,841
E7-8893 v3 4/45 MB

3.2 GHz

3.5 GHz

$6,841

Table 3 : Preferred Haswell-EX Processors for SQL Server Usage

Later in 2016, we should see the 14nm Xeon E7-4800/8800 v4 family (Broadwell-EX) for four and eight-socket servers that will have up to 24 physical cores.

Finally, sometime in 2017, we should see a new 14nm Skylake server CPU that may merge the Intel Xeon E5 and E7 lines into a single family, with up to 28 physical cores, that will be part of the Purley platform – detailed here and in Figure 1:

Intel Server Purley Platform RoadmapFigure 1 : Intel Server Purley Platform Roadmap

As these new processor families show up, with ever higher physical core counts, hopefully Intel will continue to offer lower core count, “frequency-optimized” SKUs, which offer higher clock speeds and much lower SQL Server license costs than their highest core count models.

With 64GB DDR4 DIMMs, you can have 6TB of RAM in a commodity, four-socket server, which is actually more memory than is supported by Windows Server 2012 R2. Luckily, Windows Server 2016 raises the memory limit to 12TB. With increasing memory density and better memory controllers in the latest processors, it is increasingly feasible to have enough RAM in your database server to have your entire workload in the SQL Server Buffer Pool.

Storage

We also have much less expensive, high performance enterprise flash storage from vendors such as SanDisk and Intel. For example you have the latest SanDisk Fusion ioMemory SX350 PCIe Application Accelerator product line, with capacities from 1.25TB to 6.4TB. Intel has a number of PCIe NVMe enterprise flash storage products with capacities from 400GB to 4TB.

Intel has recently announced a new enterprise product line that uses 3D NAND, for even lower costs, which will be competitive with SATA SSDs. The DC P3320 series has capacities of up to 2TB with better performance than SATA SSDs.

Enterprise flash storage is much more affordable than it was even a year ago, so it is worthwhile to reconsider using it if it seemed unaffordable in the past. The next 6-12 months should really see much more widespread adoption of PCIe NVMe flash storage devices from multiple storage vendors.

Microsoft

Microsoft is on a rapid and aggressive release cadence for the Release Candidate builds of SQL Server 2016, with RC3 being released on April 15th, 2016. Microsoft has also separated SQL Server Management Studio (SSMS) into a separate product than can be updated independently from the rest of the product, and can also automatically check for product updates.

SQL Server 2016 has a number of interesting improvements over SQL Server 2014. One of the most exciting is a feature called Query Store, which gives you a much deeper level of visibility and insight about query plan choice and performance for any database where you have enabled Query Store with an ALTER DATABASE command.

It automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. Unlike DMV/DMOs, Query Store information survives restarts of the SQL Server service. Perhaps the most useful feature in Query Store is the ability to “force” a particular query plan with a simple system stored procedure, which should work much better than the old plan guide functionality.

One new development with SQL Server 2016 that may surprise some people is that it requires Windows Server 2012 (or Windows 8) or newer to run the server components. The new separate version of SQL Server SQL Server Management Studio (SSMS) will run on Windows 7 Service Pack 1, which will make it much easier to deploy in some organizations.

Windows Server 2016 is likely to be released in mid-2016, and it will have a number of important improvements compared to previous versions of Windows Server, such as a higher 12TB memory limit, better clustering and virtualization support, and a new version of Server Message Block (SMB 3.1.1) with better support for scale-out file servers and a new feature called Storage Spaces Direct (S2D).

Future developments

Intel and Micron jointly unveiled a new kind of non-volatile data storage device called 3D XPoint (pronounced cross-point) during a press conference on July 28, 2015. According to Intel and Micron, it is 1,000 times faster and has 1,000 times more endurance than current conventional NAND flash and is ten times denser than DRAM. It's also non-volatile, which means there's no need to power it at all times. 3D XPoint has already entered production at their joint factory in Utah, and they claim that it will be in volume production later in 2016.

Initially, we'll probably see some sort of hybrid memory devices, such as sticks of memory that have both DRAM and 3D XPoint soldered on, or hybrid drives with NAND flash for mass storage and 3D XPoint providing a faster cache for the NAND. All of this will require hardware support from the server vendors and operating system support from Microsoft before we see the full benefits of the technology, but it is exciting nonetheless.

Conclusion

As a database professional, I think it makes a lot of sense to pay attention to relevant industry trends and product release cycles so you can do a better job planning for future upgrades. For example, if you are aware that a new version of SQL Server, a new version of Windows Server, a new family of Intel server processors, and a new generation of Intel PCIe NVMe storage devices are due to be released by mid-2016 – that might have a significant impact on your infrastructure budget and planning process.

Staying current with technology is also good for your career, both from a personal and professional perspective. Personally, I find that your job is much more enjoyable if you are actually passionate about it. Getting excited about new technology is a good thing if you are going to be working with technology! Professionally, staying up to date with new technology helps you do your job better and it also makes you a more valuable employee.