SQL Server 2016 SP1 – Programmability Surface for everyone!

This is the most important blog post about SQL Server that I have ever written. As far as I am concerned this is the most epic moment in the history of the SQL Server since its inception (yeah, you could argue that SQL Server 7.0 was one of the most defining moment as well).

The just released Service Pack 1 for SQL Server 2016 represents the most awesome and epic change of the strategy from Microsoft in relation to the programmability surface of the SQL Server.

oprah-car-giveaway-4Microsoft has just announced that it will be enabling the usage of the enterprise features across all different SQL Server SKUs.
I will write it again – to make sure that you can read it slow and interpret each of the words correctly:
Microsoft has just announced that it will be enabling the usage of the enterprise features across all different SQL Server SKUs.

Switching into Morpheus voice: Yes …

Yes, you can have In-Memory OLTP on the Standard Edition of SQL Server 2016 SP1.
Yes, you can have Columnstore Indexes on the Standard Edition of SQL Server 2016 SP1.
Also Row-Level Security, Dynamic Data Masking, Always Encrypted (the feature that has received huge attention & excitement from the users in SQL Server 2016), Change Data Capture (BI people – it’s party time!), Database Snapshot (think about all the possibilities of getting to read the data that normally is not available in the standard editions), Partitioning (Woohoo!), Compression (Indeed!), Multiple Filestream containers, Distributed partitioned views,
Polybase (yes, this feature deserves a separate line – think about getting into the Hadoop Clusters from your own Standard Edition of SQL Server),
and Fine grained auditing !

Yes, you can have Columnstore Indexes on the Web Edition of SQL Server 2016 SP1.
And you can even have Columnstore Indexes on the Express Edition of SQL Server 2016 SP1 … (sound of the dropping jaws hitting the floor)

Yes, there will be some limitations and regarding the Columnstore Indexes I will be publishing a separate blog post soon. Some of them – such as the memory sizes for the Cache Stores are described below, some will be published later – and some may be discovered much later, who knows!

Talking about the memory sizes for the Cache Stores – let us notice that the amount of memory for Columnstore Indexes (Columnstore Object Pool) will be limited for 25% of the total allowed Buffer Pool size, meaning that for the Standard Edition the maximum size for the Columnstore Object Pool will be limited to 1/4 of the 128 GB, making it 32GB of RAM. This does not mean that you can’t build Columnstore Table with a couple of Terabytes (Tip: you will need to use partitioning for that), it means that at the same time you can keep in memory 32 GB of this data.
These 32 GB are not marked within the Buffer Pool and for that you are getting this 25% as a bonus to your traditional 128GB of RAM.

The same principle applies to the In-Memory Structures, which receive another 25% of the maximum Buffer Pool Size – 32 GB of RAM for the Standard Edition, making the potential total of the extra-memory addition equals to 50% (64 GB of RAM for Standard Edition) and making the total amount of memory increasing to 192 GB for the Standard Edition for free !!!
If you are using Web Edition then you will get 16GB for Columnstore & 16GB for In-Memory, increasing your total RAM from 64 GB to 96 GB!
For the Express Edition this will mean addition of 256 MB for Columnstore and 256 MB for In-Memory, increasing the total size of RAM to 1,5 GB.

For those who will be crying about the limitations (there are some for the Express Edition such as In-Memory which can’t function without Filestream permissions and Change Data Capture, which can’t work because of the missing SQL Agent) and some of the enterprise-grade availability features – please, get a life!
As for the memory limitations in SQL Server Express and SQL Server Standard – please remind yourself that it has received a 50% improvement, besides all those incredible features like Columnstore, Partitioning, Compression & In-Memory that should elevate any of your editions to the before unseen speeds.

I am more than confident now, that soon we shall be having the same feature set in Azure SQL Database – which is super-exciting!

2 thoughts on “SQL Server 2016 SP1 – Programmability Surface for everyone!

Leave a Reply

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