Legacy Cardinality Estimator

sp_BlitzCache™ Result: Legacy Cardinality Estimator

SQL Server 2014 introduced a brand new cardinality estimator. Unfortunately, the old cardinality estimator is still present. Queries will use the legacy cardinality estimator when the database is in an old compatibility level (less than 120 for SQL Server 2014) or a trace flag is being used.

Using the old cardinality estimator can lead to poorly performing T-SQL, bad execution plans, missed estimates, and potentially excessive parallel queries.

To learn more about the new cardinality estimator, here’s a few of our posts:

The SQL Server 2014 CE Eats Bad Queries for Breakfast – showing a sample bad query and how the cardinality estimator makes it better.
Where to Learn More About the New CE – we link to our favorite white papers.
New Cardinality Estimator, New Missing Index Requests – since the new CE changes the way your queries are executed, it can even lead to different index recommendations.
Careful Testing the New CE with Trace Flags – you can put a plan into cache and affect the rest of your users.

How to Fix the Problem

Verify that all databases are in the current compatibility level. If a database is in a legacy compatibility level, make sure that there’s a really good reason for it.

If all databases are in the current compatibility level, you’ll need to check for queries using trace flags to force SQL Server to use the legacy query optimizer.

Use the following query to find execution plans using a legacy cardinality estimator.

CREATE TABLE #checkversion (
version nvarchar(128),
maj_version AS SUBSTRING(version, 1,CHARINDEX('.', version) + 1 ),
build AS PARSENAME(CONVERT(varchar(32), version), 2)
);

DECLARE @v DECIMAL(6,2);

INSERT INTO #checkversion (version)
SELECT CAST(SERVERPROPERTY('ProductVersion') as nvarchar(128))
OPTION (RECOMPILE);

SELECT @v = maj_version
FROM #checkversion
OPTION (RECOMPILE);

SELECT st.text,
qp.query_plan
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; min(//p:StmtSimple/@CardinalityEstimationModelVersion)', 'int') < @v * 10
OPTION (RECOMPILE) ;

CREATE TABLE #checkversion (

version nvarchar(128),

maj_version AS SUBSTRING(version, 1,CHARINDEX('.', version) + 1 ),

build AS PARSENAME(CONVERT(varchar(32), version), 2)

);

DECLARE @v DECIMAL(6,2);

INSERT INTO #checkversion (version)

SELECT CAST(SERVERPROPERTY('ProductVersion') as nvarchar(128))

OPTION (RECOMPILE);

SELECT @v = maj_version

FROM #checkversion

OPTION (RECOMPILE);

SELECT st.text,

qp.query_plan

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

) AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; min(//p:StmtSimple/@CardinalityEstimationModelVersion)', 'int') < @v * 10

OPTION (RECOMPILE) ;