Columnstore Indexes – part 118 (“SQL Server 2017 Editions Limitations”)

Continuation from the previous 117 parts, the whole series can be found at

As a logical continuation from the blog post Columnstore Indexes – part 91 (“SQL Server 2016 Standard Edition Limitations”), this blogpost will focus on the SQL Server 2017 and different editions – in my case I shall test Standard and Express Editions, while Enterprise/Developer Editions have received all the functionalities that I have tested before. The reason behind this attempt is to keep up with the different versions that Microsoft is launching and to provide a place for the reference. Besides that it is always nice to check on the previous versions editions limitations, you know – just in case there are some sudden, undocumented changes 🙂

In SQL Server 2017 these are some of the additions that were made to the Columnstore Indexes:

  • NCCI Online Rebuild
  • LOBs for CCI
  • Computed Columns for CCI
  • Trivial Plans
  • Machine Learning Services
  • Batch Mode Adaptive Joins
  • Batch Mode Memory Grant Feedback

and so let us start going through them 1 by 1, but before that let us setup 2 Virtual Machine with SQL Server 2017 RTM Enterprise & Standard Editions (and I will use Azure for that purpose), or you can install them both parallel on the same VM.
The almost everlasting free database ContosoRetailDW & TPCH 1 GB (generated by free utility HammerDB) will be used again for the tests. Notice that as always in my blog posts, I am using the backup from the C:\Install\ folder:

Now, let us drop the primary key on the FactOnlineSales table and create a Clustered Columnstore Index on this table:

For the TPCH 1 GB Version, here is the setup script that will be useful for the examples below:

Nonclustered Columnstore Index Online Rebuild

The detailed description and the tests against the Enterprise Edition can be found at Columnstore Indexes – part 96 (“Nonclustered Columnstore Index Online Rebuild”)

Attempting creating a Nonclustered Columnstore Index with the following code

on the SQL Server 2017 Standard and on the SQL Server 2017 Express Edition, will provoke the following error message, delivering very clearly the message that this functionality is not supported:

This is totally expected, since Online operations were always part of the enterprise edition, and having a particular index support operation ported to any other edition would be something beyond any surprise.

Large object binaries (LOBs) for Clustered Columnstore Index

Described already in Columnstore Indexes – part 92 (“Lobs”), the supported for the Large Object Binaries was missing for the Data Warehouse scenario since the very first version of the SQL Server with Columnstore Indexes (SQL Server 2012) until finally in SQL Server 2017 we have received this functionality.
Though very much limited with the compression engine (LOBs over 8MB are getting less to progressively no compression), they allow to unlock a couple of key scenarios, such as where some design mistakes were made and it is too expensive at the moment to make such change.

The following script will ensure the new creation for the table ManyLobs and then inserts some data into it:

It might take a while for this code to finish, but it works fine on the Standard Edition of SQL Server 2017 as well as on the Express Edition of the SQL Server 2017 RTM.
This is also quite expected functionality, since it makes absolutely no sense to limit Data Type usage on the particular version of the SQL Server. Especially since Service Pack 1 for SQL Server 2016, which democratised the usage of the programming surface for every edition, as explained in SQL Server 2016 SP1 – Programmability Surface for everyone!

Computed Columns for Clustered Columnstore Indexes

One of the very late addition to the SQL Server 2017 engine, the Clustered Columnstore Indexes started supporting non-persisted computed columns as described in Columnstore Indexes – part 108 (“Computed Columns”).

To test the computed columns I went with the script, borrowed from the original blog post, that creates a copy of the FactOnlineSales table with a non-persisted computed column:

On both Standard & Express Editions of SQL Server 2017 this script was executed successfully, leaving no doubts about the feature support. Like in the case of the of the Large object binaries (LOBs) for Clustered Columnstore Index, this is a programability feature which must be supported in all SQL Server editions in SQL Server 2017 and t works perfectly.

Trivial Plans

One rather small (relatively other features, as I imagine), but an incredibly useful improvement was described in Columnstore Indexes – part 109 (“Trivial Plans in SQL Server 2017”) – is the ability to automatically produce Fully Optimised execution plans for the Database, which compatibility level is set to 140.

Running on both instances (Standard & Express), the following script, while altering the compatibility level between 140 (SQL Server 2017) & 130 (SQL Server 2016), will produce different execution plan for the SELECT COUNT_BIG(*) operation – the fast one (with FULL optimisation in 140 compatibility level) and slow one (with TRIVIAL optimisation in 130 compatibility level):

SQL Express Full Plan

SQL Express Full Plan

SQL Express Trivial Plan

SQL Express Trivial Plan

You can see the fully optimised execution plan and the trivial execution plan on the left side of this text, both were taken from the SQL Server 2017 Express Edition.

At the end of the experiments, please, do not forget to set the compatibility level back to SQL Server 2017:

I am happy that this feature has got no Edition dependence, this is a needed improvement that simply increases the value of the offer and can actually be achieved in a lot of different ways, event without parallelism kicking in.

  • Machine Learning Services
  • Described in Columnstore Indexes – part 114 (“Machine Learning Services”), the improvement for the Columnstore Indexes in SQL Server 2017

    While the Standard Edition of the SQL Server 2017 supports Machine Learning Services (In-Database), there is no such option for the Express Edition of the SQL Server 2017, because for that purpose you will need Express Edition with Advanced Services.

    The setup script that should be executed is the following one – it creates a new table with 1048576 rows:

    The test scripts work perfectly for all supported editions, enabling Batch Mode execution for the UDX iterator, transferring data to and from Machine Learning Services, of course only for the Compatibility Level of 140 set for the database

    Batch Mode Memory Grant Feedback

    Originally described in Columnstore Indexes – part 93 (“Batch Mode Adaptive Memory Grant Feedback”), the very first available improvement of the Adaptive Query Optimiser in SQL Server 2017 – this was the incredible improvement and I remember putting it to work in the January 2017 on the production instance of Azure SQL Database. 🙂

    Picking the query from the original article and modifying it a little bit (with MAXDOP = 1) so that it would be comparable to the Standard Edition of the SQL Server – I will be running the following query against all 3 instances with different editions (Enterprise, Standard & Express) of SQL Server 2017, while measuring the memory grants:

    While the execution plans differ (this execution plan has an Adaptive Query Joins on the Enterprise Edition), the memory grant size on the Enterprise edition on the original execution 76 MB, but after a couple of consecutive executions it will eventually lower to 7.3 MB, more than times improvement, because actually not much more than 2 MB of memory will be needed for the successful execution of this plan.

    On the Standard Edition, the original plan will not get modified, no matter how often we shall run the above query – the excessive memory grant warning will remain

    You can see a more detailed part of the execution plan on below, where the SELECT iterator is presented with the excessive memory grant warning, as shown above:

    On the Express Edition the situation is even simpler, there is not really much of memory to play with – the instance memory cap of 1GB would make the adjustment attempts look quite miserable and because standard edition does not support this feature, the lower (and actually free) edition does not support as well.

    Even though I would love to have Batch Mode Memory Grant Feedback on every single edition, I am fine with this feature being currently a part of the premium (aka Enterprise Edition) subset.

    Batch Mode Adaptive Joins

    The second Adaptive Query Processor improvement that was described in Columnstore Indexes – part 104 (“Batch Mode Adaptive Joins”), it is a feature that allows to choose the execution path of the particular execution plan dynamically, during its execution.
    In SQL Server 2017 we have support for the plans involving Columnstore Indexes only, but it was already announced that in the SQL Server vNext there will be an expansion for this functionality over to the Rowstore Indexes. In short, when we have a Columnstore Index Scan in Batch Mode and a potential choice between Inner Loop Join and a Hash Match, this choice will be done, based on specific cardinality estimations.
    To test it, I will use the original queries from my blog post on the TPCH 1GB database:

    You can see from the execution plans below, that the Standard Edition (and Express Edition as well) does not support Adaptive Query Joins currently.

    Batch Mode Adaptive Joins – Enterprise Edition

    Batch Mode Adaptive Joins – Standard Edition

    Unfortunately from my tests I have confirmed that Batch Mode Adaptive Joins are exclusive to the Enterprise Edition of the SQL Server 2017.
    Since this functionality falls under intelligent database, I understand why Microsoft decided currently to implement it only for the Enterprise Edition and I consider it to be a true enterprise solution.

    The SQL Server 2016 functionalities in SQL Server 2017

    Passing through the SQL Server 2016 Enterprise-only functionalities, I decided to check how do they changed in SQL Server 2017, and here are the list of the original Service Pack 1 enablement, making Columnstore Indexes being part of every SQL Server 2016 SP1 edition:

    – Columnstore Object Pool = 32 GB
    – MAXDOP = 2 (for Standard Edition) and 1 (for all other editions)
    – Aggregate Pushdown
    – Local Aggregation
    – String Predicate Pushdown
    – Index Build/Rebuild = Limited to 1 Core
    – SIMD Support

    while I also decided to add and test one still not documented item
    – the Nonclustered Columnstore Index on Indexed View

    Regarding the Columnstore Object Pool memory cap, there is not much to add, besides that this number is great and for the total maximum size of the Buffer Pool of 128GB, I have not seen or heard anyone mentioning that the Columnstore Object Pool would be anywhere near the 32GB so far. This is a very generous cap and I hope that it will be taken advantage with better memory assignments for the Columnstore Indexes.
    I understand that someone building a Data Warehouse should consider going Enterprise Edition, but its price is absolutely out of question for the medium and small businesses. I expect Azure SQL Database to provide a very capable answer, but not everyone is ready/can/legally allowed to migrate their precious data to the cloud just yet.

    The MAXDOP = 2 of the Columnstore Indexes has been a pretty constant and no tests of mine has managed to take the SQL Server into exhibit more cores being available for processing.

    Aggregate Pushdown

    Originally described in 2015, Columnstore Indexes – part 59 (“Aggregate Pushdown”), Aggregate Pushdown is one incredible functionality that can potentially elevate the performance of the Columnstore Queries to the unbelievable level, executing simple aggregation operations on the Storage Engine level, not pushing millions of rows from the Columnstore Index Scan into the Hash Match aggregation iterator, but already delivering calculated result.
    Here is a simple query, that delivers quite distinct execution plans

    On the screenshots below you can see the difference between the Enterprise Edition execution plan (the first one) and the Standard/Express Editions execution plan (the second image):

    Nothing has changed here from SQL Server 2016 and I honestly would love to see this functionality to be added at least to the Standard Edition, I do not expect this to change anytime soon or not so soon.

    Local Aggregation

    Local aggregation is actually a very close “relative” to the Aggregate Pushdown, it is also enables to aggregate some of the data at the Storage Engine before pushing it all into the execution plans, and some of the partial aggregation (with GROUP BY, for example) shall be perfectly fitted for it.
    For more detailed description follow the Columnstore Indexes – part 80 (“Local Aggregation”).

    Let us execute a simple aggregation query against the FactOnlineSales table with a GROUP BY, which shall result in a partial local aggregation of the retrieved information.

    The execution plans for the Enterprise (the first image) and the Standard/Express editions (the second image) are presented below:

    On the first image (Enterprise Edition) you can see that 230609 rows were locally aggregated and simply the result was passed to the Hash Match iterator, while the second image contains the total number of rows from the FactOnlineSales table being pushed into the Hash Match iterator (12627608 rows).

    No surprises here, I expect actually the same feature doing the Aggregate Pushdown and so

    String Predicate Pushdown

    A rather late implementation but an incredibly smart one that the String Predicate Pushdown is. Filtering the searched information through the usage of the dictionaries and elimination of the affected Row Groups is a very smart thing to do, no doubt!
    If you are interested in the details, please consult the blogpost Columnstore Indexes – part 58 (“String Predicate Pushdown”), because here I am more interested in testing the support of the different SQL Server 2017 editions:

    The execution plans are presented below and you can easily identify the filter operation that distinguished the Standard & Express Edition execution plans:

    String Predicate Pushdown – Standard & Express (where Express would simply be Single-Core Execution Plan

    String Predicate Pushdown – Enterprise

    On the performance side the difference for the given set of data is more than 2 times, the Enterprise Edition is driving through the other editions.
    A rather interesting note in my experience is that the Express Edition was capable of delivering an incredibly similar result, even though running with just 1 core the same data that the Standard Edition processed in parallel …

    This is THE FEATURE I would love to see in the Standard & Express Edition as soon as possible. Given the lack of support for the String Row Group Elimination or Joins (to start a few of the cases), I can not consider this option to be a premium one. This should be the most basic Batch Mode functionality and it makes me sad that Microsoft decided to make it Enterprise only … and not changing their mind for SQL Server 2017. 🙁

    Index Build/Rebuild = Limited to 1 Core

    No changes.

    SIMD Support

    There will be an additional post dedicated to this topic.

    The Nonclustered Columnstore Index on Indexed View

    The not really documented and not very fast functioning Nonclustered Columnstore Index on the Indexed Views is another feature that I wanted to make sure that worked fine with Standard & Express Editions and for the description and more detailed thoughts in Columnstore Indexes – part 87 (“Indexed Views”)

    Below is a little setup script to create an indexed view on our FactOnlineSales table that is joined to the DimProduct, with a unique clustered Rowstore index and Nonclustered Columnstore Index over the columns DateKey & SumTotal.

    One very important thing to consider is that our test query against the index view on the Standard & Express Editions should consider including NOEXPAND hint, forcing the usage of the Indexed View. This hint is not necessary for the Enterprise Edition, but this is a different conversation and if you are choosing the Indexed Views architecture, you definitely should consider reviewing your application logic:

    You will find the execution plan from the Standard Edition below – it just works. Yes, I know – Just Works is a harsh statement when considering the performance aspects of this implementation, but hey – there are scenarios when building it after the data modification is totally worth it!

    🙂 It just works 😉


    Final Thoughts

    I have compiled a small table with the Columnstore functionalities, and the different SQL Server Editions (Enterprise, Standard, Express) where you can observe easily what is required for the feature to work (compatibility level) and the respective editions that do support or do not support it.

    Feature Compatibility Level Enterprise Edition Standard Edition Express Edition
    NCCI Online Rebuild yes no no
    LOBs for CCI yes yes yes
    Computed Columns for CCI yes yes yes
    Trivial Plans 140 yes yes yes
    Machine Learning Services 140 yes yes (no ML Services)
    Batch Mode Adaptive Joins 140 yes no no
    Batch Mode Memory Grant Feedback 140 yes no no
    Aggregate Pushdown 130+ yes no no
    Local Aggregation 130+ yes no no
    String Predicate Pushdown 130+ yes no no
    NCCI on the Indexed View yes yes yes

    This blog post will be updated occasionally in the nearest future.

    to be continued with Columnstore Indexes – part 119 (“In-Memory Columnstore Location”)

    Leave a Reply

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