SQL2014PerformanceImprovements

There have been many new changes which have been incorporated into SQL 2014 which would be the game changer in terms of the adoptability of SQL server. Some of the new noticeable changes which we have in SQL 2014 are :

ϠNew cardinality estimate :

₰Cardinality estimate is totally re-written.

₰Database compatibility level should be 120.

₰Query_optimizer_force_both_cardinality_estimation_behaviors Xevent for monitoring.

 

Ϡ Plan on the go :

 

₰Monitor real time query progress.

₰Use DMV sys.dm_exec_query_profiles

₰Easy to debug long running queries.

 

ϠParallel Select into :

 

₰Parallel operator can be used .

₰Compatibility level of 110 and above.

₰Can achieve very high performance gain.

 

ϠIncremental Statistics :

 

₰Statistic statements now permits per partition statistics.

₰New partition don’t require stats update for the entire table.

 

 

ϠSPOIR

 

₰One or more partitions can be rebuilt online.

₰Can be used with Managed Lock Priority feature.

₰Increases database availability.