Archive for category Next version
Now that SQL Server 2014 CTP1 is available many of us will be playing with the Hekaton functionality. To understand what is going on under the covers here is a very good internals presentation given by Sunil Agarwal at the recent TechEd North America 2013:
In this link Sunil explains filestream data files (each 128Mb in size), delta files, merging of delta files, offline checkpoint process, minimal transaction log writes, new fn_dblog_xtp function plus much, much more.
Sunil presented two other Hekaton sessions at the same conference:
It’s no secret that the next version of SQL Server will have two features that many customers are looking forward to seeing: firstly, read write clustered column store index and second, a fast, latch free, in-memory technology for OLTP workloads (code name Hekaton). If you’re working purely in the Microsoft world these additions seem to be cutting edge. That’s because over 90% of the product uses decades old features such as row based data formats, conventional disk storage, uncompressed data and scale-up only solutions.
If you look outside the Microsoft world you’ll find that many of the features that will only start to appear in next version of SQL Server are already at the core of some competitor products. To give you one such example purely in terms of utilising modern technologies, consider SAP HANA (http://www.saphana.com/welcome). This system has in-memory, compressed columnstore and rowstore at its core. Large data volumes are handled through scale-out (hash distribution partitioning of data across large number of nodes). The traditional query optimisation approaches (introduction of numerous indexes, store the data more than once using materialised/indexed views or using a separate OLAP database for the aggregates) are unnecessary as query performance across terabytes of in-memory, real-time, raw data is already blazingly fast. The system comes as an appliance (Parallel Data Warehouse like) but unlike PDW V2 it already makes use of modern solid state technologies for the persistence of its in-memory data.
In term of compliance the SQL language is ANSI compliant. Some nice additions results from its in-built version store approach for data changes. For example, see the SQL reference guide for SELECT (http://help.sap.com/hana/html/sql_select.html). You’ll see this under the ‘Time Travel’ section:
select * from x as of utctimestamp '2012-01-02 02:00:00'; // return one record (1,1) select * from x as of utctimestamp '2012-01-03 03:00:00'; // return two records (1,1) and (2,2) select * from x as of utctimestamp '2012-01-04 04:00:00'; // return nothing
Although it may not sound like it, this blog entry is not supposed to be an advert for SAP HANA. Admittedly the architecture and performance results are impressive. The reason for the posting is that from a Microsoft SQL Server user’s point of view it’s great that our own product’s feature set is being added to. But it’s only by reviewing what already exists in the non-Microsoft market can we put in proper perspective the functionality, limitations, cost and benefits of any new features.