The SQL Server Query Optimizer’s
purpose is to find an efficient execution plan that fulfills
a query request. It attempts this by assigning estimated costs to
various query execution plan alternatives and then choosing the plan
alternative with the lowest estimated cost. One key factor for
determining operator cost is the estimation of rows that will be
processed for each operator within a query execution plan. This row
estimation process is commonly referred to as cardinality
estimation (CE). SQL Server 2014 introduces the first
major redesign of the SQL Server Query Optimizer CE process since version 7.0. Use of the new CE can result in an
overall improvement in average query performance for a wide range of
application workloads.
Estimates are calculated using input
from statistics associated with objects referenced in the query.
Statistics objects used for estimation can be associated with
an index or they can exist independently. You can create statistics
objects manually or the query optimization process can generate them
automatically.
The database context (USE <whichDB?>;) of a SQL Server
session determines the CE version. If you connect to a database that
is set to the SQL Server 2014 database compatibility level, the query
request will use the new CE. If the database compatibility level is
for an earlier version, the legacy CE will be used. You can verify the compatibility level
of a database by querying sys.databases.
SELECT [name],
[compatibility_level]
FROM
sys.[databases];
To move a database to the SQL Server
2014 database compatibility level, alter the database compatibility
level to the latest version, which is “120”. E.g.
USE
[master];
GO
--
SQL Server 2014 compatibility level
ALTER
DATABASE
[AdventureWorks2012] SET
COMPATIBILITY_LEVEL
=
120;
GO
In addition to checking the database’s
compatibility level, you can also verify which CE version was used
for a specific query. To verify the CE version, inspect the query
execution plan. E.g.
In the Properties Window of the Root (leftmost) operator, for the CardinalityEstimationModelVersion attribute, value of 120 means that the new SQL
Server 2014 CE functionality generated the plan. A value of 70, means that the legacy CE functionality generated the
plan.
