SQL Server 2014 Cardinality Estimator
The SQL Server 2014 cardinality estimator has been improved to increase the quality of SQL Server query plans in both OLTP and data warehousing solutions. The cardinality of a field refers to the number of unique values that are populated in the table. Gender would be a low cardinality field. Storing the driver license number for each customer would be a high cardinality field. In SQL Server the cardinality estimator predicts the number of rows required for the query result. When a database is newly created in SQL Server 2014, the new cardinality estimator is enabled. However, if you upgrade you would need to run an Alter Database statement.
Alter Database db_name
Set Compatibility_Level = 120
If you have never looked at the cardinality estimator, the best source is MSDN – http://msdn.microsoft.com/en-us/library/dn600374.aspx
Microsoft White Paper on SQL Server 2014 – http://msdn.microsoft.com/en-us/library/dn673537.aspx
SQL Server Microsoft Team Blog – http://blogs.technet.com/b/dataplatforminsider/archive/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014.aspx
An older article on troubleshooint poor query performance on SQL Server 2008R2 – http://technet.microsoft.com/en-us/library/ms181034(v=sql.105).aspx
The post SQL Server 2014 Cardinality Estimator appeared first on Derek E Wilson - Blog.
