XTIVIA is highlighting the following new features in SQL Server 2014 and can assist you with the implementation of these features, as well as all other new features available with the 2014 version. We are excited to discuss how implementing new features, can help benefit your SQL Server environment.
Managing Locks in Online Index Rebuilds
You can now specify how long an index rebuild should wait for the locks required. This option also allows us to selectively terminate blocking processes caused by online index rebuilds. This feature requires Enterprise edition of SQL Server 2014.
Buffer Pool Extension
In previous versions of SQL Server the easy way to fix IO issues was to add memory. This was easy and effective but not always practical and often expensive. The Buffer pool extension allows us to extend the buffer pool to storage (you’ll want this to be ssd since we’re trying to speed things up). Because ssd has much better IO performance than normal disk we get the benefits of a very large buffer pool without adding more memory. This feature requires a 64 bit installation and works with Enterprise or Standard edition of SQL Server 2014.
In Memory OLTP
The new Memory-Optimized table feature of SQL Server 2014 allows us to safely create tables in memory for improved performance. A second copy of the table exists on disk and is updated shortly after transactions are updated. These tables are also versioned so that concurrent reads and writes are supported with reduced locking. A non-durable, memory only table is also supported – but in case of a crash all data in these tables will be lost. This feature can improve performance substantially but there is a small window where a crash may cause data loss. This feature is only available in Enterprise edition of SQL Server 2014 and require a 64 bit installation of SQL Server.
Partition Enhancements
Individual partitions may now be rebuilt individually. If your most recent partitions become fragmented due to activity it’s no longer necessary to rebuild indexes on the entire table. This feature requires Enterprise Edition of SQL Server 2014.
Resource Governor Improvements
Resource Governor now supports management of IO as well as CPU and Memory that were previously supported. Resource Governor requires Enterprise Edition of SQL Server 2014.
Columnstore Indexes
Columnstore indexes are no longer read-only. This allows ETL processes for data warehouses to write, update and delete records during processing. Additional compression of Columnstore indexes is now available by using Alter Index Rebuild with the COLUMNSTORE_ARCHIVE option. The use of Columnstore indexes requires Enterprise Edition of SQL Server 2014.
Improved Cardinality Estimation
Improved cardinality estimation is a behind the scenes process that improves SQL Server’s ability to create good execution plans. Better execution plans mean improved performance. This feature is supported on all editions of SQL Server 2014.
Statistics by Partition
For a system that uses partitions – we can now create unique statistics for each partition. This allows for improved performance when distribution of data varies between partitions. Partitions are only supported on Enterprise Edition of SQL Server 2014.
Always On Improvements
Always On Failover Cluster instances now support two nodes on the Standard Edition as well as the Business Intelligence Edition of SQL Server 2014.
For info on SQL Server 2012, check out our article!