Aaron Bertrand

Thoughts on SQL Server 2019 Editions

November 1, 2019 by in SQL Server 2019 | 1 Comment
Download the SentryOne Plan Explorer Extension for Azure Data Studio
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

Erin Stellato is a Principal Consultant with SQLskills and a Microsoft Data Platform MVP.

Erin’s Posts

As I embark on my first PASS Summit in a few years, it is hard to contain my excitement about the newest version of SQL Server. I helped Bob Ward (@bobwardms) with technical editing for his most recent book, "SQL Server 2019 Revealed," and have been actively engaged with the product teams and my MVP peers throughout the CTP and RC cycles. I even attended the very exclusive SQL Server 2019 Airlift event in Redmond in the first week of October – and while it was too late to provide feedback that would impact RTM, I did make several constructive suggestions that we'll hopefully see in a CU (or as late as vNext).

The product is not released yet, at least at the time of writing, but they have started to shape which features (and there are a lot of them) will be available in which editions. As you may recall, back in SQL Server 2016 SP1, they opened up all of the programming surface features to all editions, and many (but certainly not all) of the performance and availability features. I wrote about this in a post entitled, "A Big Deal : SQL Server 2016 Service Pack 1." This was a very exciting time, and I just wanted to share some thoughts about the hits and misses in the newest version.

Hits

  • Accelerated Database Recovery is in Standard Edition. This was easily the biggest surprise to me, as I thought it would be an Enterprise Edition feature. It is an availability feature, because it can drastically reduce failover and recovery time, and it can be considered a performance feature as well, because you can now use the same local version store for things like RCSI instead of sharing version store in tempdb. You can even put the local version store in its own filegroup, which wasn't an option when I wrote about the feature in March. Having this available in all editions is fantastic, but you need to make sure you test your workload against a baseline.
     
  • Transparent Data Encryption (TDE) is now in Standard Edition. Keeping in mind that the documentation is not final, this is a great change for a lot of shops, and it makes sense that such an important security feature should not be a differentiator for the most expensive edition. It is neither a performance nor an availability feature, and basic, sensible data security shouldn't cost extra, IMHO. Nike Neugebauer agrees. Always Encrypted and other features like Row-Level Security and Dynamic Data Masking are available in all editions, but they don't always fit into the "easy button" solution customers are looking for.
     
  • Scalar UDF Inlining is in all editions – even Express. This is a great feature that essentially hides all the bad performance you used to get from inefficient scalar user-defined functions (I first wrote about this feature a year ago). I'm surprised this one isn't Enterprise-only – it could have been like indexed views, where the functionality is available everywhere, but the behavior is better (at least by default) in Enterprise Edition. I'm glad everyone will benefit in this case.
     
  • Setup provides better real-world recommendations. This isn't an item on the feature list, per se, but there are some new options and suggestions around MAXDOP and min/max server memory that I think are great additions and will allow people to better configure their instances from the start:

    New MaxDOP screen in setup (click to enlarge)New MaxDOP screen in setup (click to enlarge)

    New Memory screen in setup (click to enlarge)New Memory screen in setup (click to enlarge)

    Now if only they could add options for other things that are disruptive after install, like Lock Pages in Memory, changing the default cost threshold for parallelism, specifying startup trace flags (like 3226!), highlighting sub-optimal power plan settings, and enabling Availability Groups directly instead of using Configuration Manager after the fact. And maybe they could remove that pesky warning about the firewall; it's always the same and, for me at least, it's never been meaningful or helpful in any scenario.

Misses

  • They still won't budge on the 128GB limit for Standard Edition, in spite of many requests (like this one from Erik Darling). It isn't my original idea, and it wouldn't make licensing or enforcement simpler, but the memory limits could be made relative to the number of licensed cores. This way your memory limit is based on how much you spent on licensing, not some arbitrary limit decided in a conference room somewhere 5 years ago.
     
    Brent Ozar (@BrentO) also talked about the "perfect storm" involving memory grants in Standard Edition, and I quite agree – I think customers would happily pay the premium of shifting from CAL to core licensing on Standard Edition if it now meant that they could use more (or even all) of their memory.
     
  • Memory-Optimized TempDB is Enterprise Edition only, while other In-Memory Database technologies, like Memory-Optimized Tables and Hybrid Buffer Pool, are available in Standard Edition. I feel that this feature is kind of a hybrid between performance and availability; at least more of a balance than, say, UDF inlining. Slow functions just make people wait; an overwhelmed tempdb can almost literally take down your instance. I also feel like Enterprise customers tend to already have more and better hardware to throw at the problem than smaller shops can afford. Customers who choose Standard Edition don't necessarily have the savings sitting around waiting to write checks against.
     

    One of the suggestions I had around this feature was that there should be command-line arguments and/or a UI checkbox during setup to enable this feature immediately after an install or upgrade. This would avoid disruption after installation, since the only way to enable the feature is to perform a service restart. The reason it's not on by default is that there are workload patterns where the benefit won't be obvious, and some breaking scenarios involving transactions and other databases with memory-optimized tables, so they want you to test your workload and make sure that you are observing the right types of contention and that the benefit is there. But what if I've already done that on another system with the same workload?

Parting Thoughts

While it might sound like I am complaining, I am still super excited about this version and everything it has to offer. I expect availability to be announced at both Ignite and PASS Summit, so you may have the RTM bits in your hand by the time you read this.