Developers choice: Columnstore index in Standard and Express Edition with SQL Server 2016 (SP1)


On 16th Nov 2016, Microsoft announced the support for ‘Consistent Programming Surface Area’ across all editions of SQL Server with few exceptions. Please refer to the https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released. The key value proposition is that it allows customers to use the same code base independent of the editions in SQL Server.  For example,

  • An ISV developing an application to target customers both on standard and enterprise editions typically will use features that are common to both editions to maintain a single code base.
  • A customer developing an in-house application may initially target standard edition but may need to move to enterprise edition to meet the workload demands as the business grows. With consistent programmability experience across all editions, the customer has full freedom to choose the features that are right for the workload from the beginning.

The details are shown in this chart:

columnstore_cpsa

This blog explains what you will get with columnstore index starting with SQL Server 2016 SP1 on Standard and lower editions.

Functional Surface Area

Full functionality of columnstore is available on standard and lower editions as described below

  • You can create clustered columnstore index (CCI) with or without NCIs, updateable nonclustered columnstore index with/without filtered conditions, and columnstore index on memory-optimized tables.
  • SQL Server 2016 introduced read-committed snapshot (RCSI) and snapshot isolation (SI) on queries leveraging columnstore index. These are fully supported.

    Query Performance

    This is where the differences are. While the programming surface area is consistent across all editions, Enterprise retains its performance advantages.  Now, you can develop anywhere, but when you deploy on Enterprise Edition, it just runs faster. Let me illustrate each of the key performance aspects of columnstore index which are unique to Enterprise and Developer editions.

    • Compression – No changes to compression or the physical structures. You have the delta/compressed rowgroups, dictionaries, delete bitmap, delete buffers. This allows you to take, for example, a database backup from enterprise edition and restore on Standard edition.
    • Column Elimination – No changes. When running query, only the referenced columns are retrieved.
    • Batch Mode execution: No differences. SQL Server leverages this execution mode to process a set of values together (i.e. in batches) to typically provide 4x or more performance improvement in analytics query.
    • Aggregate Pushdown: This performance enhancement was introduced with SQL Server 2016 RTM and typically gives 2-4x performance gains by pushing qualifying aggregates to the SCAN node. This enhancement is reserved for Enterprise edition of SQL Server.
    • String Predicate Pushdown: This performance enhancement was introduced with SQL Server 2016 RTM and can improve performance of analytics queries using predicate(s) on string columns by pushing these predicates to SCAN node. This enhancement is reserved for Enterprise edition of SQL Server.
    • Single Instruction Multiple Data (SIMD) Optimizations: This feature of Enterprise Edition takes advantage of a set of hardware instructions which are capable of processing an array of data in a single instruction, dramatically speeding up aggregate operations.
    • Degree of Parallelism: Limited to 2 on Standard Edition and 1 on Web and Express Editions. This restriction is only for the queries running in Batch Mode.
    • Memory Limit: Columnstore index has memory limitations on lower editions (EXPRESS ~1.4 GB, WEB-64GB and STANDARD-128GB) as shown in the table below. For example, if you install Standard edition on a box with 64GB, you can get up to 32GB for all columnstore indexes on the instance. Given the memory limitation, you may, depending upon the schema/data, find compressed rowgroups are of smaller size. To confirm this,  you can query the DMV Sys.dm_db_column_store_row_group_physical_stats to see if smaller rowgroup resulted because of memory pressure. Not having enough memory may slowdown your workload and you can fix this by upgrading your database to a higher edition of SQL Server.

    memory-limits

    Data Load

    All optimizations are fully supported include logging, as well as loading data in parallel from the staging table.

    Supportability

    No changes. All DMVs, XEvents and Perfmon counters are supported.

    Index Management

    Index REORGANIZE command is fully supported. You can use it force compressing delta rowgroups or to remove deleted rows from the compressed rowgroups.

    Example: Performance Gains

    Columnstore will allow you to get much better performance for analytics workload compared to rowstore on lower editions of SQL Server 2016SP1. Here is one example to show simple analytics query on a table with 11 million rows. There are two versions of the table, one with PAGE compression and other with Columnstore Index. As you can see that the query on table with clustered columnstore index runs took 33 ms CPU time (single threaded) while it took 3547ms CPU on table with PAGE compression (multi-threaded).

    2016-sp1-perf

    Hope this provided you a good overview of the benefits of columnstore index on lower editions of SQL Server. Look forward to hearing from you

    Thanks

    Sunil Agarwal and Kevin Farlee

    Comments (9)

    1. Mohd Ehtesham Ansari says:

      Hi,
      Currently I am using SQL Server Enterprise edition, the clustered columnstore index in our SQL code is working fine. We are planning to migrate from SQL Server enterprise edition to SQL Server Standard edition.
      I just need to verify whether the clustered columnstore index will be supported by SQL Server 2014 Standard edition.
      Thanks in advance!

    2. Mohammed, thanks for your interest. CCI is supported in SQL 2016 STD edition only. So I will recommend you to upgrade

      1. Mohd Ehtesham Ansari says:

        Thanks for the reply. So, just to be clear, SQL 2014 Standard edition will not support Clustered Columnstore index.

        1. correct. No plans at this time

    3. Sunil Gure says:

      Good that CCI even supported in Standard Edition in 2016.

    4. Manu says:

      Hi,We went sql 2016 enterprize for availing column store indexes and partitioning features, earlier we were ran with sql 2014 web edition ,Now Microsoft has announced that in sql 2016 sp1 web edition we can get partition and columnstore index features ,so as part of my downgrade plan(for saving cost for client) i have ran some sample same queries on both the servers (both the servers configurations and databases are same ,difference is one is on enterprize (Live production server) and another one is on Wed edition ) but i can able to see there is too much difference in execution time like in enterprize query got executed in ~ 24 secs the same query in web got executed in ~ 135 Seconds ,at this moment i’m not sure what went wrong ,i have checked my all column store indexes size is ~ 7 GB as per this article web edition support upto ~16 GB,can you please guide me what else i need to check in order to balance both the servers .
      Note : while testing no one using my web edition server .
      Thanks in Advance ..

      1. Hello, as you can see in the blog post, there are a number of scale features related to Columnstore that are Enterprise Edition only, such as DOP or Aggregate Pushdowns. You will need to capture and analyze your actual execution plan on both to understand the differences. Recommend using the Plan Comparison Tool for some quick insights: https://blogs.msdn.microsoft.com/sql_server_team/tag/comparison-tool/

        1. Manu says:

          Thanks Pedro,after comparison i came to know that my execution plans are same but the operators statistics are different in terms of estimated I/O & CPU cost .So ultimately sql 2016 sp1 web edition will support column store indexes but not (not 100 %)other features which are related to CCI.

          Thanks Sunil for look into my concern.

      2. Manu, thanks for your interest in columnstore and it is great to see that having this feature in standard edition will make it possible for customer to use it. As Pedro indicated, we have high perf/scale options in Enterprise Edition. However, you should see comparable data compression.

    Skip to main content