FORCE_DEFAULT_CARDINALITY_ESTIMATION: A Simple Explanation

FORCE_DEFAULT_CARDINALITY_ESTIMATION is a query hint that gives one query the default estimator. It works even when the database is set to the legacy one. The hint is the narrowest switch you have for this problem, because it touches one query and nothing else.

What the Hint Does

Cardinality estimation is the guess SQL Server makes about how many rows each step of a query will return. The plan depends on that guess. SQL Server 2014 introduced a new estimator with compatibility level 120. Databases that were upgraded can keep the old one, called the legacy estimator, with a database setting.

Sometimes that setting is right for 99 queries and wrong for one. The old estimator makes the one query slow, and you want the newer model for that query only. FORCE_DEFAULT_CARDINALITY_ESTIMATION does exactly that. It forces the model of the database’s compatibility level, whatever the legacy setting says.

Build a Test Case

The demo creates a database named CardinalityHintDemo. Its Stores table holds 100,000 rows, 5,000 for each of 20 cities. Two cities share each state, so the city decides the state. That link between two columns is where the two estimators disagree.

IF DB_ID(N'CardinalityHintDemo') IS NULL CREATE DATABASE CardinalityHintDemo;
GO
USE CardinalityHintDemo;
GO
DROP TABLE IF EXISTS dbo.Stores;
CREATE TABLE dbo.Stores (
    StoreID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    City    nvarchar(30) NOT NULL,
    State   nchar(2)     NOT NULL
);
WITH c AS (
    SELECT ROW_NUMBER() OVER (ORDER BY v.City) - 1 AS rn, v.City, v.State
    FROM (VALUES (N'Austin', N'TX'), (N'Dallas', N'TX'), (N'Denver', N'CO'), (N'Boulder', N'CO'), (N'Portland', N'OR'),
                 (N'Salem', N'OR'), (N'Boise', N'ID'), (N'Nampa', N'ID'), (N'Reno', N'NV'), (N'Sparks', N'NV'),
                 (N'Tucson', N'AZ'), (N'Mesa', N'AZ'), (N'Provo', N'UT'), (N'Ogden', N'UT'), (N'Tulsa', N'OK'),
                 (N'Norman', N'OK'), (N'Omaha', N'NE'), (N'Lincoln', N'NE'), (N'Fargo', N'ND'), (N'Minot', N'ND')) AS v (City, State)
), n AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS k
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.Stores (City, State)
SELECT c.City, c.State FROM n INNER JOIN c ON c.rn = n.k % 20;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SELECT d.compatibility_level, c.value AS LegacyEstimation
FROM sys.databases AS d
CROSS JOIN sys.database_scoped_configurations AS c
WHERE d.database_id = DB_ID() AND c.name = N'LEGACY_CARDINALITY_ESTIMATION';

The database runs at level 170, and the legacy estimator is on. Every query here starts with the old model unless it says otherwise.

Run the Query Twice

The query looks for one city in one state. The first run uses the database setting. The second run adds the hint. Each statement sits in its own batch, because SQL Server keeps one plan for each batch. The queries save their rows into temporary tables, so the results stay out of the way.

DROP TABLE IF EXISTS #LegacyRows, #HintRows;
GO
SELECT StoreID INTO #LegacyRows FROM dbo.Stores WHERE City = N'Austin' AND State = N'TX';
GO
SELECT StoreID INTO #HintRows FROM dbo.Stores WHERE City = N'Austin' AND State = N'TX'
OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));

The plan cache remembers which model each plan used. This query reads the model version and the estimated rows from the plan XML. It puts the real row count next to them.

SELECT CASE WHEN st.text LIKE N'%FORCE_DEFAULT%' THEN N'With the hint' ELSE N'No hint' END AS Query,
       qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:StmtSimple/@CardinalityEstimationModelVersion)[1]', 'int') AS CeModel,
       CAST(qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:StmtSimple/@StatementEstRows)[1]', 'float') AS decimal(12,2)) AS EstimatedRows,
       qs.last_rows AS ActualRows
FROM sys.dm_exec_query_stats 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 st.text LIKE N'%INTO #%Rows FROM dbo.Stores%' AND st.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY Query DESC;

Model 70 is the legacy estimator, and model 170 is the default for this database. The hint changed the model for this one query, and the database setting stayed on. Both models guess too low, because the city decides the state and neither one knows it. The default model guesses closer.

Where the Numbers Come From

A city holds 5 percent of the rows, and a state holds 10 percent. The legacy estimator treats the two filters as independent and multiplies them: 100,000 times 0.05 times 0.10 gives 500. The default estimator softens the second filter with a square root. It computes 100,000 times 0.05 times the square root of 0.10, which is 1,581. The real answer is 5,000, because every Austin row is also a TX row.

Check Without the Hint

Switch the database setting off and the same query needs no hint. This proves the hint only overrides the setting for one query.

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DROP TABLE IF EXISTS #DefaultRows;
GO
SELECT StoreID INTO #DefaultRows FROM dbo.Stores WHERE City = N'Austin' AND State = N'TX';
GO
SELECT qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:StmtSimple/@CardinalityEstimationModelVersion)[1]', 'int') AS CeModel,
       CAST(qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:StmtSimple/@StatementEstRows)[1]', 'float') AS decimal(12,2)) AS EstimatedRows
FROM sys.dm_exec_query_stats 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 st.text LIKE N'%INTO #DefaultRows FROM dbo.Stores%' AND st.text NOT LIKE N'%dm_exec_query_stats%';

With the setting off, the plain query gets model 170 and the same 1,581 rows. The setting decides the default for the whole database. The hint decides it for one query.

Check the Model in SSMS

You can read the same value in the plan window. Run the query with the actual execution plan switched on, click the root operator, and open the Properties pane. The line CardinalityEstimationModelVersion shows 70 for the legacy model and 170 for the default model of this database. In a saved plan file, the value is an attribute of the statement element.

When to Use It

Use the hint when a database has to stay on the legacy estimator, and one query suffers from it. The opposite hint, FORCE_LEGACY_CARDINALITY_ESTIMATION, covers the reverse case. Both hints need SQL Server 2016 SP1 or later. USE HINT needs no sysadmin rights, unlike the older QUERYTRACEON trace flags. QUERYTRACEON 2312 is the older equivalent of this hint.

You could argue that a hint hides a deeper problem. Fixing the statistics or rewriting the query is cleaner. That is true when you control the code. When you do not, such as a report from a vendor, the hint is a small change. The undo is to remove it. Compare the estimated rows with the actual rows before and after, as the demo does.

What to Remember

FORCE_DEFAULT_CARDINALITY_ESTIMATION sets the model for one query, and the plan proves which model ran. Read the CardinalityEstimationModelVersion value, and compare the estimate with the real count. The setting for a whole database is covered in Performance Regression After a SQL Server Upgrade: First Aid. When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE CardinalityHintDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE CardinalityHintDemo;

A cardinality hint is not a speed trick, it is a way to choose which guess the optimizer makes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.