Glenn Berry

Perfect Storm for Upgrading to a Modern Version of SQL Server

Free eBook : Query Optimization
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Register to Download

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

A number of ongoing and upcoming developments in software, hardware, and storage technology are shaping up to make the next twelve to eighteen months an ideal time to migrate from a legacy version of SQL Server to a modern version of SQL Server.

Personally, I consider any version of SQL Server prior to SQL Server 2016 to be a legacy version of SQL Server. SQL Server 2014 will fall out of mainstream support on July 9, 2019 (the same date that SQL Server 2008 and SQL Server 2008 R2 will fall out of extended support). SQL Server 2012 fell out of mainstream support on July 11, 2017.

SQL Server Developments

The modern versions of SQL Server are SQL Server 2016, SQL Server 2017, and the upcoming SQL Server vNext. Previously, I wrote about why SQL Server 2017 is a tangibly better upgrade choice than SQL Server 2016 right now. When SQL Server vNext is released (perhaps in Q4 2018) it is likely to have a number of useful new features and enhancements that will make it a superior upgrade choice to SQL Server 2017. Regardless of new features, the next version of SQL Server will be in mainstream support for a longer period than SQL Server 2016 or SQL Server 2017.

Operating System Developments

Microsoft is set to release Windows Server 2019 sometime later in 2018. Microsoft recently had an online Windows Server Summit on June 26, 2018 that had a lot of useful information about this release. There are a number of improvements in Storage Spaces Direct (S2D), including deduplication and compression in ReFS. Another improvement is True Two-Node quorum for two-node S2D clusters using a USB thumb drive as a file share in a router.

Windows Server 2019 S2D will let you have up to 4PB of raw storage capacity per S2D cluster, which is a 4X improvement over Windows Server 2016. There is a new PoSH cmdlet called Get-PhysicalDiskIoReport that lets you view much more granular performance information for individual physical disks, that allows you to manually monitor drive latency, and can be used to automatically detect drive latency outliers.

Windows Server 2019 fully supports existing NV-DIMM persistent memory, along with Intel Optane 3D XPoint memory and storage. There are also improvements in the free Windows Admin Center management utility that is a great dashboard for hyper-converged infrastructure (HCI) deployments.

VMware vSphere 6.7 supports vSphere Persistent Memory, which will let you take advantage of persistent memory in a VMware virtualized environment.

Hardware Developments

Both Intel and AMD are scheduled to release new generations of server processors during the next three to six months, both of which will work in existing model servers. This will make them actually available more quickly than if they required new server models.

For Intel, it will be the 14nm Intel Xeon Scalable Processor “Cascade Lake-SP” which is rumored to be due in Q4 of 2018. These processors will support “Apache Pass” DIMMs (meaning Intel Optane 3D XPoint persistent memory) and they are socket compatible with current Intel Xeon Scalable Processor “Skylake-SP” processors, which means that they will work in existing server models. More important for many customers is the fact that Cascade Lake-SP will have hardware-level protection for most side-channel attacks (such as Spectre/Meltdown), which will have less performance impact than software-based mitigation techniques.

The Cascade Lake-SP processors will be followed by 14nm “Cooper Lake-SP” in 2019, and then 10nm “Ice Lake-SP” server processors in 2020. Intel’s continued struggles with 10nm manufacturing are definitely going to hurt their competitive position compared to AMD in 2019/2020. I expect AMD will gain significant market share from Intel in the server space during this period.

AMD will have the 2nd generation, 7nm “Zen2” EPYC “Rome” family processors in early-mid 2019. These processors are supposed to be socket compatible with existing server models and have up to 48 cores/ 96 threads per processor. These processors are also rumored to have PCIe 4.0 support, which will give them double the bandwidth per lane compared to PCIe 3.0.

Looking further out, AMD is planning on releasing the 3rd generation 7nm+ “Zen3” EPYC “Milan” family of processors sometime in 2020.

Storage Developments

Intel released its first 3D XPoint storage product, the 375GB Intel Optane DC P4800X SSD in Q1 2017. These use a PCIe 3.0 x4 link along with the NVMe protocol, and they have roughly 10X lower latency and 5-8X better throughput at low queue depths compared to the fastest PCIe NVMe NAND-based SSDs. They also have 2-4X better write endurance than enterprise NAND-based SSDs.

I have had great success using these DC P4800X drives for SQL Server instances with extremely heavy tempdb workloads. They are relatively inexpensive and offer the fastest currently available type of traditional block mode storage, bar none. They are transparent to SQL Server and will work in any system that supports PCIe 3.0 x4 slots as HHHL add-in cards or U.2 connected drives.

What is coming in the very near future is Intel 3D XPoint-based DIMMs (“Apache Pass”) that use a traditional low-latency DDR4 memory interface and form factor. These DIMMs will be available in 128GB, 256GB, and 512GB capacities, and they will fit in DDR4 memory slots. They will be addressable in a lower performance block mode that uses the entire storage stack, or a much higher performance direct access (DAX) mode that is byte addressable and bypasses the storage stack.

Both Windows 10 and Windows Server 2016 already have DAX support, and SQL Server 2016 SP1 has the persistent log buffer feature that lets you leverage a DAX storage volume that is built on persistent memory to create a small additional 20MB transaction log file that is used to greatly reduce latency writing to the transaction log. My guess that that SQL Server vNext will improve this particular feature.

Windows Server 2019 will have even better support for persistent memory. New two-socket servers with Intel Xeon “Cascade Lake-SP” processors will support up to 6TB of 3D XPoint DIMMs, which can be combined with traditional DDR4 memory in other memory slots.

Conclusion

Over the next few months, we are probably going to see a new release of SQL Server, along with the release of Windows Server 2019, new generations of server processors from both Intel (and a newly competitive AMD), and new high-performance layers in the memory and storage hierarchy.

This convergence of events makes it much easier to build a business and technical case for moving from a legacy version of SQL Server running on a legacy operating system on old, out of warranty hardware and slow storage, to a much better and more flexible data platform that is able to take advantage of all of these new developments to get better performance and scalability.