With the Service Pack 1 for SQL Server 2016 released today, Microsoft puts an end to the differences in the relational database Engine in terms of programmability features between the editions (LocalDB, Express, Standard, Web, Enterprise)!

As of today, EVERYONE who is already working on SQL Server 2016 can create In-Memory optimized tables, partitioned tables, use Columnstore Indexes and so forth.

Wow, just wow… the possibilities thus offered to customers are unbelievable. Gone are the days when you had to make sure not to deploy any Enterprise features such as Compression or Database level auditing on Standard Editions. Finally, there is the option to accelerate even small systems, which are still extremely latency-critical, with memory objects (assembly-line scenarios).

What will remain in place is that the larger editions simply support more hardware. The limits of 16 Cores and 128 GB RAM for the standard edition and even less for Express will remain the same. That is and remains key.

But I definitely need to think of a new line: In the past, I always used to say: “In order to decide whether a feature is Enterprise it suffices to know if it’s ‘cool’ (e.g. Database Snapshot) or ‘quick’ (e.g. Columnstore).”

As of today, the following applies: it is the size that makes the difference.

But before someone gets me wrong: There are definitely still features that remain exclusive to the Enterprise edition. The emphasis on the getting equal is on the programming features. Mere HA features such as multiple secondaries, and certain security features such as TDE or engine-internal performance optimizations like “Advanced Read-Ahead”, Star Join-Optimization etc. remain exclusive to the Enterprise edition. Some of it also remains limited in technical terms. For Change Data Capture, for example, you do not have an SQL Agent in the Express edition. And of course, a Columnstore Index hardly makes sense if a table does not even contain 1 million rows.

(EN)SQL Server 2016 is finally available and, by extension, the “version 2” of the In-Memory OLTP Engine, if you will.

- At various international conferences and already at the IX in 2014 I have presented what is behind the In-Memory Engine of SQL Server introduced in SQL Server 2014. Only I had not yet found the time to put it into a blog article so far.

In this article I will illuminate the innovations and improvements Microsoft has been working on for the past 2 years, and which can be attributed much to customer feedback. In fact, feedback that to a great extent consisted of notes like “not practicable because this and this is missing.”

And let me say one thing before I start: in my view, Microsoft has been able to address the majority of blockers.

That means, everybody should at least consider evaluatingIn-Memory, and in almost all database projects there are structures that can be solved more elegantly In-Memory. – Ok, maybe not for everybody, because this feature is unfortunately limited to the enterprise edition.

It is now possible to define Unique Indexes as well as foreign key constraints. The latter are only possible between memory-optimized tables (and not between disk-/page-based and memory-optimized tables), and must always refer to the primary key – referring to Unique Indexes is not possible.

Moreover, NULL-values in Non-Unique Indexes are now allowed (as opposed to disk-based tables not in Unique Indexes!).

Equally very important is the support of all code pages and of non-Unicode data as well as the encryption of memory-optimized data with TDE (hence not in the main memory itself but of the data that stored on disk). *1

In my view, these were the most frequent blockers in projects in which In-Memory was evaluated, as there were hardly any practicable workarounds for this issue.

*1 Data encryption with the ENCRYPTION functions in SQL Server is not supported – this is also true for the new Always Encrypted Technology and Dynamic Data Masking.

Row-Level Security of SQL Server 2016 yet is supported. The predicates and functions must consequently be compiled natively.

A further limitation has been eliminated with the possibility of altering Memory-optimized tables afterwards.Adding, dropping and altering columns and indexes afterwards is supported. Instead of CREATE/ALTER/DROP index it must now be used ALTER TABLE, since in Memory-optimized tables indexes are part of the table definition (and are being compiled in its entirety).It is particularly important here that it is now also possible to change the bucket count of Hash-indexes which during operation may naturally change considerably over time.

Moreover, natively compiled procedures can now also be changed with ALTER PROCEDURE. In this way, they will naturally be stored compiled in the new shape in the last step.In order to facilitate a new implementation plan in the case of changed statistics one can now also executive sp_recompile against natively compiled procedures (and functions).

Performance, too, was further tweaked. As a result, memory-optimized tables and Hash-indexes can now (in InterOP mode) be scanned simultaneously. In the IO area, the entire checkpoint process was reviewed and the data files can now be read and written with multiple threads, which may result in an almost tenfold increase of the throughput (if the IO-subsystem keeps up with it).

What has been going on in the other Storage-Engine “Vertipaq”, integrated in SQL Server since 2012, with the Columnstored Indexes? These are also Main-memory optimized, but with an entirely different objective:Storage space optimization and efficient OLAP-style queries.

The innovations here are very essential:

Both Columnstore Index Types, Clustered and Nonclustered, can now be updated!Additionally, Columnstore Indexes can now be extended with further traditional btree-indexes. This is important, as not every query really profits from the Columnstore storage form. This gain in flexibility is a decisive advantage over the previous releases and cannot be emphasized enough.

And something else is now possible: Nonclustered Columnstore can be created with a filter.

By means of new techniques the following problem can be solved, for example:

A table with sales transactions is filled by small inserts at intervals of seconds.At the same, one would also like to provide various reports on day and daytime aggregations. Maximally up to date of course.The problem typically lies in the fact that one has to decide between indexes for all report queries and those that are minimally required for possible updates. Inserts viewed in isolation do not require any indexes.This combination results in the OLTP tables overloaded with many indexes, which I frequently discover during my work and that then need to be “optimized” (removed).

The possibility to create a Nonclustered Columnstore Index in addition to the Clustered Index does not only save Indexes (because the Columnstore Index can cover every necessary column), but with a smartly applied filter the Index-Overhead can also be avoided that would otherwise affect the actually more important inserts.

The mixing of OLTP and OLAP queries are one of the most typical problems in databases, and these new possibilities are thus simply a dream for database architects.

In terms of performance, these improvements have made the SQL Server 2016 pull ahead of SQL Server 2014 by close to 40% more QphH (Query-per-Hour Performance Metric) in the TPC-H Benchmark. You can see in the screenshot that the Benchmark was sent in on 9 March 2016 and really was achieved on the same hardware as under SQL Server 2014 on 1 May 2015.

Further important improvementsfor Columnstore include the support of the SNAPSHOT Isolation Level (and RCSI), which is especially important to Read-Only Replicas of Availability Groups, as well as online-defragmentation and various analysis enhancements

In technical terms, a Clustered Columnstore Index is applied. As can be seen in the image, it omits the “hot-spot” of the data in order to prevent the overhead through the double data storage in case of alterations and the potentially quick succession of inserts in this area. In addition to the implied Delta Rowgroup (in the image: Tail) that is covered by the memory-optimized index, there is a “deleted rows table” for deleted data. Both areas are asynchronously compressed/added to the CCI according to the Columnstore Index standard threshold value of 1 million cells.

At this point, let me add another note: the maximum data amount that can be stored per database in (durable) memory-optimized tables has now been eliminated, too!As a result, according to the current technical state, in theory up to 12 TB (less a maintenance overhead) can be stored in XTP-memory under Windows Server 2016!

The outcome now offers the best from both worlds: high performing inserts/updates/deletes and singleton-queries, and at the same time high performing analytic queries that handle many millions of cells at once – and in fact at the same time in the same table!

There are of course still a number of features that have been taken over into SQL Server due to the decade-long development of the SQL language, but which have not made it into the new XTP Engine yet. This is not just because the latter is “simply new” but also because due to the completely different architecture of this engine, which is radically tailored to In-Memory, there are several significant differences vis-à-vis the traditional database engines.

Even though the list of missing feature/function support is still quite long, only few really make full use of these features. And for most of the remaining “blockers” there are actually quite good workarounds, be it in the form of a different architecture or in code terms. One has to bear in mind that the In-Memory tables do not necessarily make sense for all scenarios, but rather for the top-affected tables. And as for the latter, one should already have put some effort into the design anyway.

In general, I firmly believe that in almost every database project there are some instances that may profit from In-Memory functions.

Why can I be so sure?

Already since SQL 2014 it has been possible to use memory-optimized table variables aside from memory-optimized tables. And using these, in turn, many temptable-constructs can be replaced. Now that does not necessarily result in higher performing applications right away, but it is a good way to start dealing with In-Memory in terms of code and to slowly but surely start programming with it. A further “Quick-Win” can often be found in data warehouse architectures in the so-called “staging area,” as it is frequently being applied in traditional DW-systems at the moment.

And it is via these “gateways” that you have will have ended up in the “In-Memory world” before you know it.

Unfortunately, in the final version, which I have actually only set eyes on at the newspaper kiosk, there are a few inaccuracies. In order to avoid misunderstandings, I will correct them here shortly, or rather ensure a correct understanding.

Starting with the introduction: 1)

”…After two years of developing, Microsoft introduces the new version of its database server…”

Correction: I am not sure as to when the starting shot was made for the SQL Server 2014, but it is quite certain that it was not 2 years before the release date (1 April 2014), as implied by this sentence. A little later, in the article I also say that the In-Memory OLTP Engine XTP was confirmed with its first patent already in 2009. I am not aware of when exactly it was certain that there would be a SQL Server 2014, and that the code would be branched accordingly. If I was to speculate, I would say it was more like 3 years before its release.

„…The most important innovation is the storing of relational data in the main storage instead of the hard drive.”

Correction: Those who have already familiarized themselves a little with this new technology will know of course: The data are stored both in RAM and in the hard drive – unless you work with “schema_only”-tables. This will become clear later in the article, but may cause some confusion here.

… “Native compiling” …”Before the first run, the server produces a DLL from the respective procedure for this. These libraries, however, do not last through the restart of database or server, so they have to be generated again afterwards…”

This can be easily misunderstood.

Correction: To be precise, these DLLs are regenerated after each restart of the database or database server (at first usage). – Thus, one does not have to generate these DLLs or even the procedures new oneself.

„(Natively complied procedures) … Such procedures … do not yet allow for all T-SQL language elements. For instance, Raiseerror and Begin Transaction are missing, as well as a few functions and Query Hints.”

This, too, could be misleading.

Correction: Put more precisely: “For instance, it is not possible to use particular commands such as Raiseerror or Begin Transaction, instead of which an “atomic” block is required.” The Atomic-Block already starts a transaction, so an additional “begin transaction” is superfluous in any case. – By the way, a few Query Hints are actually supported.

„(new concurrency control, „multi-versioned, timestamped optimistic concurrency control“)… For this, the server complements all data sets by an automatically updated timestamp created with each change, with the help of which it recognizes conflicts…”

This can also be easily misinterpreted and may make believe that always the same data set is being updated. However, the background to “multi-versioned, timestamped optimistic concurrency control” is in fact that there is a new data set per version, which comprehensive tests in realistic test series by Microsoft Research (with more complex transactions combined with longer read access and hotspot scenarios) have shown to be more efficient than “Single-version locking.” (Source: “High-Performance Concurrency Control Mechanisms for Main-Memory Databases,” Microsoft, University of Wisconsin – Madison)

Single-Version Locking, for example, is applied by Oracle TimesTen and IBM’s solidDB.

Correction: It is thus more precise to say that there is one data set per version, and the “old versions” are marked as such by an end-timestamp.

“(Clustered ColumnStore Indexes)… This enhanced type of the Main-Memory Index Technique was developed for the PDW-version (Parallel Data Warehouse) of the SQL Server 2012 made available in 2013 and is already being applied there…”

The choice of words suggests that these (Columnstore) indexes, just as with the In-Memory optimized tables & indexes, are located in the main memory only. This is of course not the case.

Correction: More precise would be to say: “Main-Memory-optimized indexes”

In SQL Server 2008, “Always On” was used for the entire range of high availability techniques. These include Database Mirroring, Log Shipping, Failover Clustering, Peer-to-Peer Replication, Backup and Restore (!), Database Snapshots, even partitioning, and more. (Read more here: High Availability – Always On Technologies) So this does not have anything to do with the new features AlwaysOn-Availability Groups or AlwaysOn-Failoverclusterinstances.

What is more, feature names are not simply „Germanized,“ just as you do not spell SharePoint separately – and no, I will not even do this for demonstration purposes ;-).

This is the result of a performance comparison of a schematically virtually identical “on-disc”-table compared to the different In-Memory OLTP variants. The test was carried out with standard hardware: Intel i7-3529 (2,9Ghz), 2 Cores hyperthreaded, 16GB RAM and SSDs. The result is quite impressive and matches Microsoft’s promise that new hardware is not imperative in order to obtain tangible performance gains through the application of the XTP-Engine.

P.S.: Unfortunately, there are no spots left (!) in my Master-Class Workshop In-Memory OLTP & ColumnStore - New Storage Engines in SQL Server 2014 (XTC). The decision for a remake either in the second half of 2014, or only in the first half of 2015, will probably be made in summer. – For the second half of 2014, many conferences, including MVP Summit, PASS Summit and PASS Camp, are lined up, so it is already quite cramped. The prospects may thus often be better for an in-house-training on request.

(Apollo was the Codename for the Columnstore Index project before SQL Server 2012)

(EN-US)

Whereas In-Memory Transactional Processing with XTP builds the foundation for the shift in processing OLTP in SQL Server 2014, for OLAP/datawarehouse-systems Columnstore Indexes have been improved fundamentally. In fact the improvements are so huge, that it can be foreseen that Columnstore will become the standard type of storage for DataWarehouses starting in SQL Server 2014 (at least for systems running Enterprise Edition).

Let’s start with what we had and missed at the same time so far, in SQL Server 2012 when Columnstore Indexes were introduced:

One Non-clustered columnstore index (I’ll refer to that as NCCI from now on) per table – the clustered was still row-based

No DML support: no updates (data refresh) – biggest bummer for close to real-time DataWarehouses and continuous updating

Mediocre memory management – i.e. Resource Governor not honored

No batch hash join spilling

Limited data types support – ok, I personally have little problems with that as it forced developers to wisely chose column-types and table design (remember, that columnstore indexes were mainly meant for FactTables in DataWarehouses), but that’s just my personal opinion on that :-)

Out of that list, probably the biggest issues in SQL Server 2012 are, that updates to columnstore indexed tables are only possible by drop NCCI/load data/create NCCI or partition switching and secondly: many queries actually do not get even get all of the possible performance gain from the NCCI because of an unsupported operation inside the query (plan).

At the TechEd Europe 2013 in Madrid CTP1 of SQL Server 2014 was announced publically available (www.insidesql.org/blogs/andreaswolter/2013/06/ctp1-sql-server-2014-release-techedeurope ) and as detailed information on the big improvements are finally official, I can share the good news with my readers (Big Thanks to Igor Stanko from the Parallel Datawarehouse-Team at Microsoft who answered all questions in detail, while "bound" with me and other MCMs and MVPs at the Microsoft booth ;-) )

(this is an official quote from Microsoft - and I see no need to say it any differently)

Note: The Clustered Columnstore Index feature is was actually developed by the Parallel Data Warehouse (PDW)-Team for the PDW Version 2012 which was released in the beginning of 2013. (Thanks, Henk van der Valk for reminding me of that not so insignificant fact.)

And here is a comparison of the possible space savings when using CCI over any other indexing methods so far (released at TechEd 2013 - I'll let the graph speak for itself):

After all the good news: Is there anything that we might still miss when using ColumnStore? I do not want to forget to mention the few things that might still hurt:

No constraints using index are supported, not even Foreign Keys without index. So if such is needed, one has to revert to using a hybrid approach with NCCIs and using partition switching or any other of the methods as before.

Also DML-Triggers are not possible with CCIs.

Just for completeness: no other indexes are possible when a CCI is present on a table. But there is no use for it anyways, as the attentive reader might have noticed by now :-)

So, as a datawarehouse-architect I am really excited and looking forward to the first implementations of Datawarehouses under SQL Server 2014. The index-design will become much easier, in fact in many environments even trivial. - Note, that I am not using the word “database-design” itself: A proper database-design (hint: “star-schema”) is still necessary to get the maximum performance gain possible – don’t forget that batch mode processing and segment elimination are the keys to the max.