SQL Server Standard 2016 – finally, a reason to upgrade!

Since MS SQL Server 2005, there have been 5 releases (2008, 2008 R2, 2012, 2014, 2016). Each new release came with some interesting new features – for those on the Enterprise Edition. For the majority of users, on SQL Server Standard, there were updates to the user interface and some security patches, but nothing to get excited about.

I’m sure Microsoft’s intention was that companies who needed the new features would upgrade to the Enterprise Edition. What has happened is that many companies have just stayed on their old software. It’s probably more common for me to see SQL Server 2008 than SQL Server 2014.

Finally, with the release of Service Pack 1 for SQL Server 2016, Microsoft have announced feature parity between versions. There are still differences in size of database supported and memory limits between versions, but not much else.

For full details of new functionality in SP1, there’s an excellent blog post here.

From my perspective, as a BI developer, the biggest reasons to upgrade to SQL 2016 come from the performance improvements that were denied to SQL Standard users until now. Headline items are:

Indexed Views: Known as Materialised Views in Oracle. Basically, just a view that contains the data, automatically updated as new data is added to the source tables. All you need to do is take an existing view and add an index. When the view is accessed by a query, instead of needing to query the source tables, the data is already available. The improvement in performance can be significant. Sadly, there are limitations, the most significant being the lack of Outer Joins (and all the workarounds for lack of Outer Joins). Just bear in mind that this will have an impact on CPU during inserts.

Columnstore Indexes: How about your query finishing in 10% of it’s current time by just making one minor change? Columnstore databases have been around for a long time. Viewed from a high level, it’s ‘just’ a change in how the data is stored. A ‘normal’ database stores data in table form. When retrieving rows of data from the table, the database retrieved a whole row of data even if you only need 2 or 3 columns. This introduces a huge overhead on disk performance. Using a Columnstore database changes this by storing each column of a table separately. This is then further enhanced using some clever compression and indexing to greatly reduce the amount of data that needs to be read. The downside is much slower insert performance, so be careful before adding this to a production database. As always, document any changes and test! You may read elsewhere that tables with Columnstore Indexes are read-only. That used to be the case, but changed in SQL 2016.

One other benefit is the potential reduction in storage space. In our customer’s database, one table has been reduced in size from 30GB to just 700MB simply by changing the Primary Key to a Columnstore Clustered Index.

As always, there is a gotcha – creating the index uses lots of RAM, so be careful when you create or the improvements won’t be as significant.

While this has been available from SAP/Sybase IQ and others for some time, the advantage of having this in MS SQL is that you can just add to an existing database without needing to make changes to queries or stored procedure.

Partitioning: Allows you to split a table or index across multiple filegroups. This can improve query performance, but needs careful planning. Not the first thing to try!

Compression: Data in a table can be compressed to reduce both storage space and the number of reads that need to be made when querying a table (more rows of data fit in a page, so fewer pages need to be read). For details of how compression is implemented, MSDN has a good article. Again, there is CPU overhead but as the major limitation for querying databases tends to be RAM or Disk I/O, this is normally a good trade off.

In-Memory OLTP: Just testing! This is about data in, not data out.

What next? First off, SQL Server Developer Edition is now free and contains all this functionality. Download that and test.

Alternatively, ask us! We’re happy to advise on SQL Server performance at various levels. Once you’re happy that the new features in SQL Server 2016 SP1 will help, we can even provide the necessary licences. If you want to run a separate database for reporting, we can help with that too.