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

  • Article

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 SQL Server 2016 SP1 release blog post. 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.
      SQL Server 2016 SP1 Editions Columnstore Index quota (per instance)
      Express 352 MB
      Web 16 GB
      Standard 32 GB
      Developer Unlimited
      Enterprise Unlimited

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