SQL Server 2016 SP1 Standard Edition = Enterprise Edition!?

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Last week Microsoft made a historic announcement regarding SQL Server: with the introduction of the Service Pack 1 the Standard Edition of SQL Server offers you the same features as the Enterprise Edition. You don’t trust me? Just look here.

This changes the whole game in the market for relational databases and is mostly a death blow against Oracle. In today’s blog post I want to talk a little bit more about that exciting announcement – whether there are still differences between Standard and Enterprise Edition, and in which cases the Enterprise Edition is still needed.

Standard Edition = Enterprise Edition

With the Service Pack 1 of SQL Server 2016 Microsoft makes all the development features of SQL Server available in the Standard, Web, Express and LocalDB editions:

  • Change Data Capture
  • Database Snapshots
  • ColumnStore Indexes
  • Partitioning
  • Compression
  • In-Memory OLTP
  • Always Encrypted
  • PolyBase
  • Auditing
  • Multiple FILESTREAM Containers

From a developer’s perspective you no longer have to care which features of SQL Server you use in your database applications. Everything is available across all editions of SQL Server. This is amazing, *really* amazing! So the question is now, why do you still need the Enterprise Edition of SQL Server? Let’s have a look at that.

Standard Edition <> Enterprise Edition

The Enterprise Edition is still needed for the following 2 reasons:

  • High Availability/Disaster Recovery
  • To be able to use more Hardware Resources

For example if you want to deploy Always On Availability Groups, you still need the Enterprise Edition of SQL Server, because the Standard Edition only gives you Basic Availability Groups. Even Partial Database Availability is only supported in the Enterprise Edition.

And the most important key point is that the Standard Edition is limited to only 128 GB RAM and 4 CPU Sockets or 24 CPU Cores. If you want to throw more hardware at your workload, you will need the Enterprise Edition.

Let’s talk here a little bit more about the supported RAM. In the Standard Edition if you use In-Memory OLTP or ColumnStore Indexes, SQL Server will give you 32 GB of *additional* RAM (besides the traditional Buffer Pool memory) for each feature:

  • 32 GB additional RAM for In-Memory OLTP
  • 32 GB additional RAM for ColumnStore Indexes

This means that the maximum possible useable RAM in the Standard Edition is now 192 GB (128 GB Buffer Pool + 32 GB In-Memory OLTP + 32 GB ColumnStore), as long as you use In-Memory OLTP and/or ColumnStore Indexes. If your Memory Optimized Tables are larger than 32 GB RAM, all additional INSERT and UPDATE statements will just fail – ouch…

The story is different with ColumnStore Indexes, because SQL Server only holds up to 32 GB of your ColumnStore Indexes in RAM, and if you exceed that limit, then other ColumnStore data will just be discarded from the RAM. A Clustered ColumnStore Index will also give you great compression rates compared with a traditional RowStore Index, therefore the 32 GB of additional RAM for ColumnStore Indexes can store a lot of data in RAM.

Summary

AWESOME, AWESOME, AWESOME!!!

Thanks for your time,

-Klaus

7 Comments

  • Thomas Franz

    The new CREATE OR ALTER syntax is also AWESOME…

    • Andrea Caldarone

      Yes after decades we have an Oracle equivalent of “create or replace”

  • Slawek Guzek

    Hi Klaus!

    How about parallel index operations? Has those been enabled in 2016 SP1 Standard?

    Regards
    Slawek

    • Klaus Aschenbrenner

      Hello Slawek,

      Thanks for your comment.
      I guess you mean Online Index Rebuilds?
      If yes, they are still only part of the Expensive Edition of SQL Server.
      *Only* the above mentioned features are part of the Standard Edition.

      Thanks,

      -Klaus

  • Adrian

    Hi Klaus,

    Yes, online indexing and also large page support (NUMA Aware and Large Page Memory and Buffer Array Allocation) is still EE only:

    https://technet.microsoft.com/en-gb/windows/cc645993(v=sql.90)

    Thanks,
    /Adrian

  • Slawek Guzek

    Hi Klaus,

    I actually meant parallel index creation. CREATE INDEX goes parallel in Enterprise. Seeing your announcement (Standard Edition = Enterprise Edition!) I hoped that Standard 2016 SP1 does the same. And it turns it does not 🙁

    Regards,
    Slawek

  • Robert Eder

    I find it interesting that Microsoft made Always Encrypted available to Standard Edition, a new feature to 2016, yet Transparent Database Encryption (TDE) was not made available to Standard Edition.

It`s your turn

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

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top