Compatibility Level

https://msdn.microsoft.com/en-us/library/bb510680.aspx?f=255&MSPPError=-2147217396

Compatibility-level setting of 120 SQL Server 2014

SQL Server 2014 includes substantial improvements to the component that creates and optimized query plans. This new query optimizer feature is dependent upon use of the database compatibility level 120. New database applications should be developed using database compatibility level 120 to take advantage of these improvements. Applications that are migrated from earlier versions of SQL Server should be carefully tested to confirm that good performance is maintained or improved. If performance degrades, you can set the database compatibility level to 110 or earlier to use the older query optimizer methodology.
Database compatibility level 120 uses a new cardinality estimator that is tuned for modern data warehousing and OLTP workloads. Before setting database compatibility level to 110 because of performance issues, see the recommendations in the Query Plans section of the SQL Server 2014 What’s New in Database Engine topic.
ompatibility-level setting of 120 or lower (SQL 2014 or lower)
Compatibility-level setting of 130 (SQL 2016)
The Insert in an Insert-select statement is single-threaded..
The Insert in an Insert-select statement is multi-threaded or can have a parallel plan.
Memory Optimized Table queries execute single-threaded.
Memory Optimized Table queries can now have parallel plans
Introduced the SQL 2014 Cardinality estimatorCardinalityEstimationModelVersion=”120″
Further cardinality estimation ( CE) Improvements with the Cardinality Estimation Model 130 which is visible from a Query plan. CardinalityEstimationModelVersion=”130″
Batch mode v/s Row Mode changes with Columnstore indexes
  • Sorts on a table with Columnstore index are in Row mode
  • Windowing function aggregates operate in row mode such as LAG/LEAD
  • Queries on Columnstore tables with Multiple distinct clauses operated in Row mode
  • Queries running under MAXDOP 1 or with a serial plan executed in Row mode
Batch mode v/s Row Mode changes with Columnstore indexes
  • Sorts on a table with a Columnstore index are now in batch mode
  • Windowing aggregates now operate in batch mode such as LAG/LEAD
  • Queries on Columnstore tables with Multiple distinct clauses operate in Batch mode
  • Queries running under Maxdop1 or with a serial plan execute in Batch Mode

Leave a Reply

Your email address will not be published.