SQL Server 2016 Is Simply Faster…A Lot Faster

Aug 4, 2016

By Kevin Kline

I had the pleasure to spend some time with my old friend at Microsoft, Mark Souza, while speaking at the SQL Saturday event in Dublin, Ireland. Now keep in mind that Mark and I have known each other since the 1990s when SQL Server was just being ported to a brand new operating system called Windows NT. Mark and I were having a laugh and more than a twinge of nostalgia about how much SQL Server has improved over the decades and now sits atop the heap on most analysts’ “best database” reports. This isn’t just two old-timers sharing a few war stories though. This is a living, breathing transformation that is still in process.

The Cream of the Crop

One source of information you should absolutely make a part of your regular reading is the blog “SQL Server According to Bob” (https://blogs.msdn
.microsoft.com/bobsql). It’s written by not one, but two guys named Bob—Bob Dorr and Bob Ward. These individuals are perhaps the most widely respected deep technology experts on the SQL Server team. They’re the most senior members of the SQL Server support organization. Rest assured, if you have a support call about SQL Server referred to one of the Bobs, there is no higher authority.

Best of the Blog

Back to their blog. They take a lot of time to provide lucid and detailed explanations of how the internals within SQL Server work and, most pertinent to this discussion, reveal the mysteries of why SQL Server 2016 is so much faster than previous versions. Just look for the tag “It Just Runs Faster” to see all of the relevant articles. There are literally dozens and dozens of deep-code improvements in SQL Server 2016, so let me run down a few highlights of things that get a lot faster in the newest release. (These are arbitrary and personal preferences. You probably will have other favorites.)

• DBCC, SQL Server’s internal consistency checking utility, scales up by seven times. And that improvement happens despite having much additional consistency and logical checks. That means preventative maintenance operations are much faster.

• Tempdb, a file system where SQL Server does most of its internal processing, has better default handling of the underlying I/O subsystem.

• Transaction Logs, the primary method through which SQL Server ensures durability in ACID-compliant transactions, gets an improved “stamping” algorithm to maximize modern hardware, improve multi-threaded processing, and optimize storage reclamation and cleanup.

• Automatic Soft NUMA, seldom seen on older hardware, is now the norm for better memory and CPU partitioning. This provides a series of cascading benefits to other internal structures, such as spinlocks, latches, mutexes, and semaphores. Gains of 10% to 30% are not uncommon on certain OLTP workloads.

• Better Thread Scheduling enables SQL Server to better schedule worker tasks and balance the workload for higher scalability. This, combined with the Soft NUMA improvements, means that many background SQL Server processes can run within a NUMA-node rather than outside of the NUMA-node.

These are subtle and deeply internal improvements, but they’re ones that make everything else in the database engine faster and better balanced. An analogy I like to use is that many improvements are flashy and easy to spot, such as a cherry-red sports car. But to truly make most people’s commute a lot faster and smoother, you have to redesign the roads and traffic patterns. These are the sorts of improvements we’re seeing delivered at internet-speeds with SQL Server.