Microsoft's SQL Server 2014 early code: First look
Welcome to the Hekaton
In-memory database engine, improved integration with Windows Azure, and new indexing technology for high performance data warehousing applications - there's plenty to like in SQL Server 2014, released to manufacturers on Tuesday.
But while Microsoft has been busy and done some heavy lifting, the code that will become generally available on 1 April has some glaringly rough edges.
Let's tackle the best bit first - I looked at the CTP2 and saw later builds and tried them in a hands-on lab.
The in-memory database engine codenamed Hekaton is the most eye-catching feature, thanks to the dramatic performance increase it can offer – up to 30 times, according to Microsoft. The feature has been in development for five years, program manager lead Kevin Liu told journalists on the SQL Server 2014 workshop I attended.
The database engine is new code which accesses data directly in memory, uses a high level of concurrency, and compiles stored procedures to native code for further optimisation. A copy of the data is streamed to disk for persistence, though you can disable this for maximum performance if you do not care about losing data.
The performance benefit is real. Even on a modest virtual machine running on Azure (four cores, 7GB memory) I saw the time for inserting 100,000 orders, each in its own transaction, decline from two minutes and 54 seconds to 36 seconds after switching to in-memory tables.
Another plus is integration. You can mix in-memory and disk-based tables in a database, though querying across both is inefficient.
There are limitations though. The most severe is a long list of T-SQL keywords that are not supported for in-memory tables, including IDENTITY, UNIQUE, OUTER JOIN, IN, LIKE, DISTINCT and other common commands, Triggers and BLOB fields. Workarounds are suggested, but this does mean a porting effort in order to take advantage.
There are some other limitations for this first release. One is a recommendation that in-memory data does not exceed 256GB.
“Rest assured, that is something we will bump up drastically in the next release,” Liu said.
The other is that “the recommendation for hardware is two sockets” to avoid issues with NUMA (Non Uniform Memory Access) that affect performance.
The best fit for using in-memory tables is where business logic is in stored procedures and client-server communication is not too chatty. Applications that implement business logic in external code are not optimal.
Microsoft is also making a big deal of new Azure integration. There are several possible scenarios. You can mount database files that are in Azure storage; the latency makes this unsuitable in many cases, though SQL Server will cache the most active data - but it can be useful for archiving.
Of wider use is the ability to backup to Azure storage, which is now built-in. In Management Studio you can select URL as a backup destination, which prompts for Azure credentials. There is also a new Managed Backup tool, aimed at smaller organisations, which will automatically backup databases to Azure storage. You only need configure the credentials and the data retention period.
Another Azure feature is replication to SQL Server databases running on Azure VMs. An Add Azure Replica wizard sets up always-on availability.
Clustered columnstore indexes are great... but not a killer feature
SQL Server 2012 introduced columnstore indexes. Column-oriented storage is good for compression, since columns typically contain similar values, and for queries that select only a few columns from a table. This can give substantial performance improvements in data warehouse applications, where large datasets are extracted from operational data for reporting and analysis. Columnstore indexes in SQL Server 2012 are not updateable.
New in SQL Server 2014 are clustered columnstore indexes, which are updateable, and are now intended to be standard for data warehousing. However, they have their own limitations. In particular, you cannot combine a clustered columnstore index with any other indexes. A table with a clustered columnstore index is a specialist table type in which the index is the primary storage for the table. In a data warehouse context that is fair enough, but this is not a general-purpose feature.
Other new features include the Buffer Pool Extension, which uses a solid-state drive as a database engine buffer pool to improve throughput, and enhancements to the resource governor, for allocating usage quotas, which allow you to control the I/O operations per second for a specified resource pool.
What is notable in SQL Server 2014 overall is that so much of the product seems unchanged. The new in-memory database engine is a huge feature, despite painful limitations, and without it Microsoft could not justify a full version number bump. Clustered columnstore indexes are great, but only applicable for a small subset of applications. For those who cannot make use of either feature, SQL Server 2014 will be a hard upgrade to sell.
At the same time, the hardware trend today is towards rapidly increasing amounts of server memory as well as many processors, and the “Hekaton” feature is aligned with both, even though it has limited ability to take advantage in this first release.
I asked if Microsoft if it was paying attention to the rise of non-relational databases, and received a hint that there are research projects covering this area. I also mentioned dissatisfaction with the performance of some programmability features, such as the Entity Framework in .NET, and again it was hinted that programmability will be a focus of the next major release.
Microsoft has not yet given full details of pricing and licensing for SQL Server 2014, though the indications are that not much has changed. According to an online table, the memory limit on the Standard edition has been lifted from 64GB to 128GB. It is still limited to the lesser of four CPU sockets or 16 cores. The Enterprise edition does not have these limits. The free Express edition is limited to 1GB memory and one socket or four cores, with a maximum database size of 10Gb.
In summary? SQL Server 2014 is slicker than previous releases but retains enough familiarity for diehards to step in and start running things. Yet, for this speed Microsoft has stumbled in other key areas, meaning there's plenty to improve on next time around. ®