Article “SQL Server 2014 – New Fundament” in iX Issue 5/2014
Corrections in In-Memory OLTP and ColumnStore Indexes, and why AlwaysOn is not the same as Always On.
In the May-issue of the iX, on page 56, there is an article by me which I wrote together with my colleagues Volker Heck (Cloud- and BI-Part) and Holger Schwichtenberg (copy-editing).
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.
2) „…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.
3) „…Stored Procedures in machine language.“
… “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.
4) „(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.
5) „(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.
6) “(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”
7) And last but not least, I’m afraid in this article, too, a common mistake has slipped in:
The solution for high availability and emergency restoration reintroduced in SQL Server 2012 is spelled “AlwaysOn,” of course; – neither “Always On” nor “Always-On.” “Always On” (with space) was already applied in SQL Server 2005 in order to certify storage hardware for SQL Server. It includes, for example:
- The correct implementation of the APIs, the Write-Ahead Logging (WAL) protocol for both transaction protocols and data and backup files.
- The options FILE_FLAG_WRITETHROUGH and FlushFileBuffers when opening files.
- The support of asynchronous I/O.
- Write ordering.
- The correction transmission of the sector sizes to the Windows APIs in order to avoid sector size mismatches and Torn Writes.
- The NTFS-abilities such as Sparse Files, File Streams, Encryption, Compression and all security properties.
You can read more on “Always On” here, for example:
www.dell.com/downloads/global/solutions/
dell_pv_sql_always_on_tech_note_v_1_5.pdf
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 ;-).
For now, I feel like I have satisfied my sense of accuracy 😉
Since the following graph has unfortunately not made it into the article, I would like to share it with my readers here at least:
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.
These are the corresponding links to the article:
- Liste der unterstützten SQL-Server-Features bei In-Memory-OLTP / List of the supported SQL-Server-Features in In-Memory-OLTP
- The Bw-Tree: A B-tree for New Hardware Platforms
- Liste der unterstützten SQL-Befehle bei kompilierten Stored Procedures / List of supported SQL-commands in compiled Stored Procedures
- Microsoft Case Studies
- Dokumentation zum Column Store Index / Documentation of Column Store Index
- Buffer Pool Extension
- Änderungen im Cardinality Estimator / Changes in Cardinality Estimator
- Windows Azure
- Premium Preview for SQL Database Guidance
- PowerBI for Office 365
I hope the points above are helpful for a better understanding not only of the article but also of SQL Server 2014 in general.
You are welcome to leave comments or questions in my blog.
Andreas Wolter
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.
Leave a Reply
Want to join the discussion?Feel free to contribute!