The latest version of SQL Server is targeted at becoming the leading database solution from Microsoft. Microsoft has focused on many of the OLTP performance problems from many angles: slow disk performance issues with in-memory tables, slow log performance issues with delayed durability, and maintenance concurrency issues with lock priority. This new SQL Server release stops excessive I/O with new Resource Governor I/O controls, addresses some memory issues with an SSD buffer pool extension, and availability issues with the Azure cloud integration.
1. In-Memory OLTP
For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP, which allows you to move individual tables to special in-memory structures. The performance boost is reported as big as 30x. There are a number of limitations and special requirements for these tables, so they won’t work under every circumstance. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You can read a whitepaper from Microsoft Research here.
2. Backup to Azure
Quite often small shops without a full-time Database Administrator don’t discover that their backups aren’t being handled properly until it’s too late. The Managed Backup feature automatically backs up your database (or your instance) based on your defined recovery interval and workload patterns. When the system determines the data has changed significantly enough, it takes a backup to Azure. This feature only works with Azure blob storage. But since your backups are already offsite, there’s no need to worry about tapes. This may not solve all your incident recovery needs or be 100% effective, but for those small shops it is a better solution than doing nothing.
3. SQL Server Data Files in Azure
Data Files in Azure is just what it sounds like: Your database runs locally in your data center, while the database files themselves live in an Azure blob container. This can offer advantages in DR and migration. But depending on the size of the database and its workload, the potential performance cost of pushing the data for every transaction across the Internet could be prohibitive. A better use of this feature may be to store the data files in an Azure VM in the same data center. This can also get you around the current limitation of having only 16 mounted disks in an Azure VM.
4. Updateable columnstore indexes
Updatable columnstore indexes in SQL Server 2014 bring a dramatic boost to data warehouse performance. The caveat is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren’t supported.
5. Resource Governor for I/O
Disk I/O is typically the most constrained resource of a database system, and often a large or rogue query will take up more precious I/O resources than you can afford. Microsoft has finally given us some control over runaway I/O. With Resource Governor for I/O, you can now put queries into their own resource pool and limit the amount of I/O per volume they’re allowed.
MAX_IOPS_PER_VOLUME set the minimum and maximum reads or writes per second allowed by a process in a disk volume.
MIN_IOPS_PER_VOLUME reserves a minimum number of I/O transactions per second
MAX_IOPS_PER_VOLUME provides a maximum number of I/O transactions per second.
This maximum doesn’t limit the number of I/O operations a query can perform, but merely keeps it from monopolizing a disk. This way your large queries can still run, but other things will run as well.
A good use of I/O control is to reserve some IOPS for administrators to be able to investigate issues when the disks are overloaded.
6. Delayed durability
In SQL Server, changes to data are written to the log first. This is called write ahead logging (WAL). Control isn’t returned to the application until the log record has been written to disk (a process referred to as “hardening”). Delayed durability allows you to return control back to the application before the log is hardened. This can speed up transactions if you have issues with log performance. Nothing is free, though, and here you sacrifice recoverability. Should the database go down before the log is committed to disk, then you lose those transactions forever. It may be worth the risk if your log performance is severely degrading application response times.
7. SSD buffer pool extension
Creating a buffer pool extension for SQL Server 2014 is like being able to define a different page file in Windows. As data pages move into memory, they begin to fill up the buffer pool. If the buffer pool fills up, the less frequently used pages will be paged to disk. Then when they’re needed again, they’ll be swapped with something else in the buffer pool and moved back into memory. The buffer pool extension option allows you to define an SSD as a buffer file location. Because SSD is so much faster than spinning disk, the paging is considerably quicker, which increases performance dramatically in some cases. You can define a buffer pool extension file up to 32 times the size of your memory.
8. Backup Encryption
One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.
9. Lock priority of online operations
You can now specify a lock priority for online re-indexing. In previous versions of SQL Server, long-running queries could block re-indexing operations, chewing up your maintenance window while your re-index sits waiting. In SQL Server 2014, you can specify how your re-index operation will handle being blocked. You specify how long it will wait and what to do when the wait is over.
10. AlwaysOn Enhancements
Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.