Continue to my whats New series, I am going through its new features and the more I am reading it impresses me a lot. and I am feeling that this is it. I really like it.
Other what’s new series are SQL Server 7, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Now its time for SQL Server 2014(I know 2016 is coming on its way). You can get all my Whats New blog @ What’s New Tag(hope this will goes well).
Following are some of the features which I may go much dipper on but I think I need some time to do so.
Initially When I heart about 2014 I though it has on in-Memory as a new feature later realize that it has much more(will try to add more during my learning):
sql server 2014:”
1 >> Memory-optimized tables (formerly known as Hekaton) :are available in Enterprise editions, as well as evaluation and developer editions. In-Memory OLTP is a high performance, memory-optimized engine
2 >>Memory-Optimized Table checkpoint:The automatic checkpoint for memory-optimized tables runs after the log increases 512MB after the previous checkpoint.
3 >>Buffer Pool Extension :Enterprise/ Business Intelligence/ Standard(64 bit)
Buffer Pool Extension Edition:The Buffer Pool Extension feature is supported in Enterprise, standard, Evaluation , and Developer editions only.
The Buffer Pool Extension (BPE) feature of SQL Server 2014 allows SSDs to be used as additional buffer pool memory.
4>> Database Backup Encryption :
Starting in SQL Server 2014, SQL Server has the ability to encrypt the data while creating a backup.
5 >> Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (trace flag 9481 )setting trace flag 9481
The first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process,
XML showplan text
Select TOP (tie/percent) gives worse performance :resolves using new Cardinality Estimator
6 >>SQL Server Backup to URL
SQL Server Backup to URL was introduced in SQL Server 2012 SP1 CU2 supported only by Transact-SQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to back up to or restore from Windows Azure Blob storage service
7>> The 90 compatibility level is not valid in SQL Server 2014(120)
8>>We can define an Availability Group replica that resides in Azure.
9 >> Updateable columnstore indexes: This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables
10>> Resource Governor for I/O : set the minimum and maximum reads or writes per second allowed by a process in a disk volume.
11 >>Delayed durability : Delayed durability allows you to return control back to the application before the log is hardened
Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB.
the facility exists to make the delayed durability choice per transaction too
12 >>Incremental statistics: Incremental statistics in SQL Server 2014 allow you to update just those rows that have changed and merge them with what’s already there.
13 >> Low Priority Wait /Low priority of online operations :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. Will you have it follow traditional behavior and wait indefinitely? Will you have it terminate and move to the next table? Or will you kill the blocking query, so your re-indexing can complete? It’s your choice.
14>>Parallel insert functionality of SELECT INTO :