SQL Server 2019 Standard Edition Feature Limitations Are Out

The features list by edition is out, and there’s a disclaimer at the top:

This content is being updated for SQL Server 2019. The information in the article is not final.

Which means if you’re really angry, and your company buys a lot of licenses, now’s your time to raise holy hell with your Microsoft reps if there’s something in this doc that disappoints you.

The most important stuff:

  • Standard Edition is still capped at 128GB RAM.
  • Accelerated Database Recovery is in Standard Edition.
  • Automatic tuning, batch mode for row store, adaptive memory grants, adaptive joins, and memory-optimized TempDB are Enterprise Edition only.
  • Automatic inlining of scalar functions isn’t listed, but given the above, it’s probably Enterprise Edition only as well. Update: scalar UDF inlining is now listed, and it’s in Standard Edition! Woohoo!

Update: how I feel about the limitations

Looking back at this, I realize that I didn’t editorialize this at all – I didn’t say how I felt about it. I’m actually totally cool with it except for two things, and I’ll get to those in a second.

Microsoft’s gotta make money just like you do, and they need big differentiators between Standard Edition and Enterprise Edition. They need to have serious reasons as to why you’d spend the extra $5K per CPU core. I think automatic tuning, batch mode for row store tables, adaptive joins, and memory-optimized TempDB are all fair limitations. If you’re hitting the kinds of performance issues where you need those features in order to survive – not just that you want them, but that you have to have them – then you should probably pony up for Enterprise Edition.

The vast, vast majority of shops have survived just fine without those features for decades. Sure, you’ve hit performance limitations that have caused you to do some performance tuning, and those took you some time. Maybe even a lot of time. But that’s the value proposition of Enterprise: when you hit big performance issues, you could EITHER tune it manually yourself, or you could pay Microsoft to improve the performance for you. I think it’s a fair trade.

First, Basic Availability Groups are a joke. I know, they’re supposed to replace database mirroring, but the requirement of a separate Availability Group for every database, and a separate listener, and a separate IP address – those are just ridiculous. Let people put multiple databases in the same Basic Availability Group and fail them over together. (I’m not asking for multiple replicas or read replicas – I get that both of those are great differentiators for Enterprise. I know, some readers are gonna say they need multiple replicas for both high availability and disaster recovery, and I don’t think that’s unreasonable, but I’m not fighting that battle today.)

Second, there’s one area that’s unforgivable: memory grants. SQL Server itself makes horrible, horrible decisions around memory grants, especially around over-estimation. Standard Edition has the perfect storm of problems:

  1. It’s capped at 128GB RAM (especially around query workspace)
  2. You can’t use Resource Governor to cap query grants
  3. SQL Server can’t learn from its mistakes because Standard doesn’t get adaptive grants

I would totally understand if we had ANY way at the system level to fix SQL Server’s bad memory grant decisions, but the only option we have is by changing the queries themselves (by rewrites or by adding grant hints.) Microsoft should give us SOME way to fix these bad grants at the system level – and putting any one of the 3 above options in Standard would be fine.

Or, you know, they could fix the query optimizer to stop granting so many queries 25% of the buffer pool every time they run a query. But let’s be realistic here.

Previous Post
How to Think Like the SQL Server Engine: Adding a Nonclustered Index
Next Post
How to Think Like the SQL Server Engine: So Index Seeks are Great, Right?

19 Comments. Leave new

  • Matt MacDonald
    October 17, 2019 3:34 pm

    Am I reading this correctly that TDE is part of standard edition in 2019?

    Reply
    • As of the moment, yes – but they’ve already edited the doc at least once since publishing it, so keep your fingers crossed.

      Reply
      • George Walkey
        October 18, 2019 4:05 pm

        Thats massively huge
        Tde everywhere will allow us to get on 2019 std and encrypt everything
        That cant be real

        Reply
    • Why is TDE an advantage? Why should I be considering it a feature worth including in Standard Edition instead of something really useful? What is the real use case here – besides adhering to some standards like HIPAA? What TDE is really protecting me from? Properly configured NTFS permissions and encrypted backups give me very similar security without performance penalty.

      Reply
  • Definitely agree on basic availability groups. They aren’t only a joke, they’re almost entirely useless. No readable secondary? Fine. Two replica limit? Fine.

    One DB per AG? Makes it almost unusable. If you only use internally developed software (and I seriously doubt your ERP is internally developed) you could work around it, but otherwise you are more than likely to run into an app which uses more than one database but only allows one connection string.

    No integrity checks on secondaries? Absolute Belligerence on the part of MS. You can’t just skip your checkdbs on the secondary and hope there was no corruption. Its not like it is a ‘feature’ to offload an integrity check that isn’t 100% sound for the primary on a secondary replica. And if you get a corrupted secondary with a healthy primary you can just about be guaranteed it was the result of a bug MS wrote into their code, or the result of a driver that they had on their approved hardware list.

    I’m not convinced these two limitations are actually protecting their cash flow either. With the limited features in standard and no license mobility possible, a small shop could quickly come off better buying licensing for one SQL enterprise server, putting a whole bunch of RAM in it, and using license mobility to cover failover-only scenarios and end up ahead of licensing four SQL standard servers and have better DR, performance features and vertical scalability. They could at least allow 2 or 3 databases in a basic AAG and checkdbs on secondary and really can’t imagine this impacting their cash flow all. It might even improve their cash flow by causing more standard licenses over time.

    Reply
    • Also, Postgres seems to be roaring forward in feature parity. SQL seems to be stagnating in the ETL area as well, and when comparing SQL standard to Postgres, Postgres is probably superior in absolute and comparative terms The only thing I doubt is the quality of support available for Postgres. Making standard more useful could go a long ways to maintain dominance.

      Reply
  • Performance can be a real pain for SQL Server for the reasons mentioned. To get around some of the issues i would like to share that Query Hints can be very useful for some of these issues:
    https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15

    Reply
    • Ron – yep, that’s why I wrote, “the only option we have is by changing the queries themselves (by rewrites or by adding grant hints.)”

      Reply
      • I agree with you Brent with one exception.
        There is another option…to pay someone else to fix it like Brent Ozar Unlimited! 🙂
        I greatly appreciate all the free stuff you post and your free tools as well which are great.
        However, after about 31 years here, i am training my replacement. (outsourced/job eliminated). Company has decided they want someone else to manage infrastructure.

        Reply
        • Awww, sorry to hear that. On the plus side, now you can join a contracting or consulting firm (or start your own) if you want. You’ll probably find that the money involved with being a service provider is even better than the old gig.

          Reply
  • Is the 128GB cap on the machine or the ram that SQL Server uses?

    Reply
  • Georgi Kyuchukov
    October 19, 2019 9:45 pm

    “Automatic tuning, batch mode for row store, adaptive memory grants, adaptive joins, and memory-optimized TempDB are Enterprise Edition only.”

    No. That can’t be true. Give me the TempDB or the batch mode for row store, seriously? I am waiting for the batch mode for whole year. Don’t push me to postgress only, please! I love the SQL Server.

    Reply
    • I don’t think it is reasonable to expect either of those features in standard edition yet, but I agree that MS is floundering in their product development in SQL server while postgres is roaring forward and is also cheaper. It is becoming more and more true, that not only does postgres potentially have a comparative advantage over SQL server, but they are also starting to gain absolute advantage.

      It’s fine that MS wants to develop SQL for their database as a service offering and attempt to steer on-prem customers to the cloud, sql database as a service is a GARBAGE service offering of absolutely appalling performance and feature set and think they are doing a better job at steering customers to other platforms. Given that you can go to AWS and get a DB as a service offering that is almost fully compatible with Postgres, for less money and superior performance, they aren’t making their case. they are making novel’s and IBM’s case.

      Reply
      • Georgi Kyuchukov
        October 23, 2019 9:54 am

        Actually, I was pretty sure that batch mode on row store will be available, but yes – they might release it in the next version of SQL Server. Unfortunately. this is not working for us – the Azure was never the solution and we start the migration of some services to PostgreSQL. Migrating legacy system to new database engine is difficult, but the management made the decision.

        Reply
  • If anyone wants to make noise, I added a suggestion that you all can vote on. Only had a few sips of coffee so far today so let me know if I totally missed anything.

    https://feedback.azure.com/forums/908035-sql-server/suggestions/38866414-move-2019-enterprise-feature-to-standard-to-assist

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}