A week late and more than a few dollars short, this blog post is focusing on a few of the features announced for SQL Sever 2014. I’ve watched the Keynote and a few of the videos from the sessions at TechEd North America 2013, and read the whitepapers and datasheets. Information is still limited though, like which versions the features will be added to, so it’s certainly not a deep dive into the features. Nor is it an exhaustive list of the features; moreover, it’s a look at those ones that immediately piqued my interest in the Mission Critical Performance category of improvements.
(Update: I’ve written about SQL Server CTP1 here)
SQL Server 2014 was announced during the keynote at TechEd in New Orleans. Makes me wish I went all the more now. The features in this release are categorized into 3 areas:
- Mission Critical Performance
- Platform for Hybrid Cloud
- Faster Insights from Any Data
The CTP is set to be released later this month. So, aside from the new Enhanced Query Processing features which we should come to expect, what are the new features which I am excited about? Well late last year I blogged about Hekaton, part of the In-Memory OLTP feature. But some of the other features that stood out for me were:
Sysprep at the cluster level
A little bit about sysprepping SQL: SQL Server Sysprep allows you to prepare a stand-alone instance of SQL Server on a computer and to complete the configuration at a later time.SQL Server Sysprep currently supports SQL Server Database Engine, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services, as well as shared features. You can now configure Sysprep for SQL Server failover cluster installations. Not much more to say here other than good stuff!
Columnstore indexes were a big feature of SQL Server 2012, limited by the fact that they were static, meaning that you had to disable or drop and then enable/rebuild once updated. With SQL Server 2014, Columnstore Indexes are now updateable: Prior to SQL Server 2014, if you ever wanted to update a table without dropping/disabling the Columnstore index, the fastest process was to leverage partitions by switching in tables that used the same columnstore index design.
Compression for Columnstore indexes as also been improved: we can apply a new compression called COLUMNSTORE_ARCHIVE for greater compression and storage space savings of (reportedly) as much as 90 percent.
Hekaton is one of the features I was interested in hearing more about, having followed it from last years announcements. They’ve not mentioned which version of SQL Server it will be in (e.g. Standard or Enterprise) and what with the 64GB limit on standard, maybe they won’t add it as a feature to standard. Conjectures aside, It seems that though it is still built into SQL Server, there are a few caveats. Tables can be declared as ‘memory optimized’ to take advantage of In-Memory OLTP’s capabilities. There are some limitations on these tables, which is to be expected:
- None of the LOB data types are allowed; there can be no columns of type XML, CLR or the max data types, and all rows will be limited to 8060 bytes with no off-row data.
- No DML triggers
- No FOREIGN KEY or CHECK constraints
- No IDENTITY columns
- No UNIQUE indexes other than for the PRIMARY KEY
- A maximum of 8 indexes, including the index supporting the PRIMARY KEY
- Database mirroring and replication are not supported
According to the whitpaper on Hekaton CTP 1, after installing SQL Server In-Memory OLTP, the AMR (Analysis, Migration and Reporting) tool can provide recommendations as to what tables and procedures you might want to consider migrating to In-Memory OLTP. This tool is built on management Data Warehouse using data collector, and comes in the form of new report types:
- One of the reports generated will contain recommendations on which tables might provide the biggest performance gain when converted to memory-optimized tables.The report will also describe how much effort would be required to carry out the conversion based on how many unsupported features the table concurrently uses.
- Another report will contain recommendations on which procedures might benefit from being converted to natively compiled procedures for use with memory-optimized tables.
So this is a real win: using sophisticated analysis of the metadata stored within the database the developers can abstract recommendations as to what tables and procedures you might want to consider migrating to In-Memory OLTP. Nice!
IO Control via Resource Governor
Database Administrators can now define the workloads which can take a percentage of performance on any given CPU, memory, and now also I/O. Through Resource Governor, administrators can control I/O resources by adding a setting for maximum and minimum IOPS per volume to Resource Governor resource pools.
So other than the exciting new features above, there was also information around some of the features that were introduced SQL Server 2012:
Virtual Machine Capacity
SQL Server virtual machine is still pegged at 64 virtual processors and 1 terabyte of memory. This constraint is set by Hyper-V 3.
Physical Machine Capacity
Physically, the limits are at 640 logical processors memory is set at 4TB respectively. These constraints are more to do with the limits set at the OS as these limits are the same as Windows Server 2012.
Table partitions are also still set at 15,000 partitions. I’ve wondered why the partition counts was set to such an arbitrary sounding number; it’s not like its scaled from 1000. But recently we were hitting the upper limits of this (lots of empty partitions) and it might have something to do with perform acne, as merging/splitting partitions was taking up to 2 seconds at the higher limit, as opposed to milliseconds with lower ranges. Either way number of partitions is a more than generous 15000 partitions.
So some great new features for us to hear more about in the coming months (no release date confirmed).Last years release, SQL Server 2012, focused largely on Data Warehouse via xVelocity features. This release the marquee name belongs to In-Memory OLTP via xVelocity features. The fact that Columnstore Indexes are now updateable is also a real nice win and makes it a lot more likely that the feature will be leveraged on non-partitioned tables. It makes me wonder if the release after this will remove the limitations with regards to In-Memory OLTP. Conjectures aside, I’ve not even touched upon the SQL Azure features, nor the improved data compression for backups, or even the smart backups! This is a real big release no doubt, and I look forward to trialing out In-Memory OLTP in the near future.