Review: SQL Server 2014 pushes the pedal to the metal
Microsoft's latest SQL Server release boosts OLTP performance with in-memory tables, expands backup and HA options with ties to Windows Azure
There are limitations to the types of objects that can be converted, so you should read up and test your code before going whole hog with this solution. As a quick example, optimized stored procedures can't contain cursors, subqueries, or even CTEs (common table expressions). Those are a few examples in a long list, so you need to do your homework; expect database and table requirements as well. Given that this is a brand new feature, I would expect these restrictions to diminish over time.
What kind of speeds are we talking about here? Will it be enough to make a conversion worthwhile? I've seen some pretty impressive demos, and Microsoft's site boasts a 10- to 30-fold performance boost. The exact speeds depend on many factors of course, but from what I've seen in the demos and in my own testing, these are realistic numbers. The truth on the ground is that if you have a highly transactional OLTP scenario and it meets the requirements for Hekaton, you'll fall in love.
SQL Server 2012 introduced columnstore indexes for dramatically warehouse performance. I've personally seen queries that took several minutes with a traditional index reduced to subsecond times with the addition of a columnstore index. The problem was that columnstore indexes weren't updateable. In order to load data into the table, you had to drop the indexes first and re-create them afterward. SQL Server 2014 has solved that problem by making columnstore indexes updateable -- nice job, Microsoft.
I always tell my junior DBAs that there are only two ways to resolve a bottleneck: Either reduce the workload or increase the throughput. While the previous features address the throughput, these next two features deal with the workload.
Resource Governor has finally gained physical I/O control. Disk is quite often the bottleneck on any given system because it's the one component with moving mechanical parts. Quite often, disks get overworked because of some rogue query sucking up tons of extra I/O against the disks. In fact, I've seen warehouses brought down by queries pulling billions upon billions of IOPS. Now you can finally put queries into their own resource pool and limit the amount of I/O per volume they're allowed. This means no more ridiculous queries taking up all of your precious disk resources. Reducing the amount of I/O on a disk system can have a tremendous impact on overall performance.
Taking a shortcut
This next feature, called Delayed Durability, doesn't actually speed up the database, but it makes the database seem faster from the end-user point of view. Let's walk through a typical transaction (simplified) to illustrate this.
Let's say the end-user updates a record. The update first gets written to the log in memory, and the log entry gets written to the disk. After the log entry is written to the disk, the application is informed that the transaction is complete and the user can continue working. The actual update to the database happens behind the scenes at a later time. Now, with Delayed Durability, the application is informed that the transaction is complete before the log is written to the disk. This means the end-user doesn't have to wait for the transaction to be hardened to the much slower disk media before he can continue working. I've seen many systems where the log was the bottleneck, and Delayed Durability can definitely help address that.