New features in SQL Server 2014
It introdue lots of new features to improve performance 40 times better compare to older version of SQL Server for heavy workload queries. Top features of SQL Server 2014 are listed below.
In-Memory OLTP Engine
You can store tables and stored procedure in memory to improve query peformance, This approach introduce in SQL server 2014 as In-Memory OLTP engine which is integrated with Database engine.
Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2014.
AlwaysOn Availability Groups were first introduced with SQL Server 2012. AlwaysOn Availability Groups enable high availability and disaster recovery for multiple SQL Server databases.
SQL server 2014 supports 8 replicas in Enterprise Edition
SSD Buffer Pool Extension
SQL Server 2014 provides a new solid state disk that lets you use SSDs to expand the SQL Server 2014 Buffer Pool size up to 32 times the size of RAM. SSD is much faster disk compare to spining disk, paging is much quicker.
Microsoft recommend adjusting its value in concordance with the “Max Server Memory” parameter.
Updateable Column store Indexes
Columnstore indexes are another of Microsoft’s high performance in-memory technologies.
Microsoft introduced the Columnstore index in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, Columnstore indexes can provide up to 10x performance improvements.
Storage I/O control
SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well.
The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool
Power View for Multidimensional Models
Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including
tables, matrices, bubble charts, and geographical maps.
Enhancements to Backups
SQL Server 2014 also provides new Windows Azure integration to SQL Server’s backup capabilities. You can specify a Windows Azure URL as the target for your SQL Server 2014 database backups. This new Windows
Azure backup feature is fully integrated into SSMS.
Resource Governor for 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. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME set the minimum and
maximum reads or writes per second allowed by a process in a disk volume.
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.
Incremental statistics in SQL Server 2014 allow you to update just those rows that have changed and merge them with what’s already there. This can have a big impact on query performance in some configurations.