Which cardinality estimator each database actually uses and why: compatibility level, LEGACY_CARDINALITY_ESTIMATION, trace flags and query hints, with advice on what to change. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
11_CE_Configuration_Audit.sql - which cardinality estimator (CE) each database uses, and why.
Clive Roberts DBA, 10 October 2026. Read-only. Server-wide. SQL Server 2016 or later.
BACKGROUND
SQL Server has two estimators. The LEGACY CE (model 70) was used up to SQL Server 2012. The NEW CE arrived in
SQL Server 2014 and its version follows the compatibility level (120 = 2014, 130 = 2016, 140 = 2017,
150 = 2019, 160 = 2022). What a database actually uses depends on, in order of precedence:
1. a query hint: OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) or trace flag 9481 on the query
2. the database: ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON
3. the server: trace flag 9481 globally (legacy) / 2312 (new) - set at start-up, affects everything
4. compatibility: below 120 = legacy, 120 and above = new CE of that version
TRADE-OFFS (why the vendor's "use the legacy CE" didn't help)
- The legacy CE assumes columns are INDEPENDENT (multiplies selectivities); the new CE assumes some correlation
(exponential back-off) and handles ascending keys (new rows past the histogram) better.
- Legacy CE suits a few queries written and tuned against it years ago; it doesn't fix bad estimates caused by
lopsided data, missing/stale statistics, implicit conversions or parameter sniffing - the usual real causes.
- Switching a whole database to legacy also gives up the newer optimiser behaviour for every query in it.
- Better: keep the current compatibility level (and its CE), find the few regressed queries (Query Store,
02_Query_Store_Regressions.sql) and fix those - with a hint on just those queries if needed:
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) or a Query Store hint (SQL 2022+) / forced plan.
HOW TO READ IT
Result 1 per database: compatibility level, the CE it should use, and the settings behind it.
Result 2 trace flags on the server that change the CE or optimiser.
Result 3 what cached plans ACTUALLY used, per database (ce_version 70 = legacy). This is the evidence:
e.g. a database at 130 whose plans show 70 has the legacy setting or hints in play.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Result 1: per database
CREATE TABLE #dsc ([database] sysname, legacy_ce sql_variant, legacy_ce_secondary sql_variant, optimizer_hotfixes sql_variant,
parameter_sniffing sql_variant, query_store nvarchar(60));
DECLARE @db sysname, @sql nvarchar(max);
DECLARE d CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE state_desc = N'ONLINE' AND HAS_DBACCESS(name) = 1 AND database_id <> 2;
OPEN d; FETCH NEXT FROM d INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db) + N';
INSERT #dsc SELECT DB_NAME(),
(SELECT value FROM sys.database_scoped_configurations WHERE name = N''LEGACY_CARDINALITY_ESTIMATION''),
(SELECT value_for_secondary FROM sys.database_scoped_configurations WHERE name = N''LEGACY_CARDINALITY_ESTIMATION''),
(SELECT value FROM sys.database_scoped_configurations WHERE name = N''QUERY_OPTIMIZER_HOTFIXES''),
(SELECT value FROM sys.database_scoped_configurations WHERE name = N''PARAMETER_SNIFFING''),
(SELECT actual_state_desc FROM sys.database_query_store_options);';
BEGIN TRY EXEC (@sql); END TRY BEGIN CATCH PRINT N'Skipped ' + @db + N': ' + ERROR_MESSAGE(); END CATCH;
FETCH NEXT FROM d INTO @db;
END;
CLOSE d; DEALLOCATE d;
DECLARE @tf9481 bit = 0, @tf2312 bit = 0;
CREATE TABLE #tf (TraceFlag int, Status int, Global int, Session int);
INSERT #tf EXEC (N'DBCC TRACESTATUS(-1) WITH NO_INFOMSGS');
SELECT @tf9481 = MAX(CASE WHEN TraceFlag = 9481 AND Global = 1 THEN 1 ELSE 0 END), @tf2312 = MAX(CASE WHEN TraceFlag = 2312 AND Global = 1 THEN 1 ELSE 0 END) FROM #tf;
SELECT d.name AS [database], d.compatibility_level,
CASE WHEN CONVERT(int, s.legacy_ce) = 1 OR @tf9481 = 1 THEN N'LEGACY (70)'
WHEN d.compatibility_level < 120 AND @tf2312 = 0 THEN N'LEGACY (70)'
ELSE N'NEW (' + CONVERT(nvarchar(10), CASE WHEN d.compatibility_level < 120 THEN 120 ELSE d.compatibility_level END) + N')' END AS ce_used,
CASE WHEN CONVERT(int, s.legacy_ce) = 1 THEN N'database setting LEGACY_CARDINALITY_ESTIMATION = ON'
WHEN @tf9481 = 1 THEN N'server trace flag 9481'
WHEN d.compatibility_level < 120 AND @tf2312 = 1 THEN N'server trace flag 2312 (forces new CE)'
WHEN d.compatibility_level < 120 THEN N'compatibility level below 120'
ELSE N'compatibility level' END AS because_of,
CONVERT(int, s.legacy_ce) AS legacy_ce_setting, CONVERT(nvarchar(20), s.legacy_ce_secondary) AS legacy_ce_on_secondaries,
CONVERT(int, s.optimizer_hotfixes) AS optimizer_hotfixes, CONVERT(int, s.parameter_sniffing) AS parameter_sniffing,
s.query_store,
CASE WHEN d.compatibility_level < (CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) * 10)
THEN N'below this server''s level (' + CONVERT(nvarchar(10), CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) * 10) + N')' ELSE N'' END AS note
FROM sys.databases AS d LEFT JOIN #dsc AS s ON s.[database] = d.name
WHERE d.database_id <> 2
ORDER BY ce_used, d.name;
-- Result 2: server trace flags that affect the CE / optimiser
SELECT TraceFlag AS trace_flag,
CASE TraceFlag WHEN 9481 THEN N'legacy CE for everything' WHEN 2312 THEN N'new CE for everything'
WHEN 4199 THEN N'optimiser hotfixes' WHEN 4136 THEN N'parameter sniffing off'
WHEN 2371 THEN N'dynamic auto-stats threshold (default from compat 130)' WHEN 4139 THEN N'ascending key fix (legacy CE)'
WHEN 2389 THEN N'ascending key detection' WHEN 2390 THEN N'ascending key detection' ELSE N'' END AS meaning,
Global AS is_global
FROM #tf WHERE TraceFlag IN (9481, 2312, 4199, 4136, 2371, 4139, 2389, 2390);
IF NOT EXISTS (SELECT 1 FROM #tf WHERE TraceFlag IN (9481, 2312, 4199, 4136, 2371, 4139, 2389, 2390))
PRINT N'No server-wide trace flags affecting the cardinality estimator.';
-- Result 3: the CE that cached plans actually used, per database
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plans AS (
SELECT pa.dbid, cp.usecounts, qp.query_plan.value('(//StmtSimple/@CardinalityEstimationModelVersion)[1]', 'int') AS ce_version
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(cp.plan_handle) WHERE attribute = N'dbid') AS pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE cp.objtype IN (N'Adhoc', N'Prepared', N'Proc') AND qp.query_plan IS NOT NULL AND pa.dbid <> 32767)
SELECT DB_NAME(dbid) AS [database], ce_version,
CASE WHEN ce_version = 70 THEN N'legacy' ELSE N'new' END AS estimator,
COUNT(*) AS cached_plans, SUM(CONVERT(bigint, usecounts)) AS executions
FROM plans WHERE ce_version IS NOT NULL
GROUP BY dbid, ce_version
ORDER BY [database], ce_version;
-- Queries in the cache that force the legacy CE themselves (hints)
SELECT TOP (50) DB_NAME(st.dbid) AS [database], OBJECT_NAME(st.objectid, st.dbid) AS [object], cp.usecounts, LEFT(st.text, 2000) AS query_text
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE (st.text LIKE N'%FORCE[_]LEGACY[_]CARDINALITY[_]ESTIMATION%' OR st.text LIKE N'%QUERYTRACEON%9481%')
AND st.text NOT LIKE N'%11[_]CE[_]Configuration[_]Audit%' -- not this script itself
ORDER BY cp.usecounts DESC;
DROP TABLE #dsc; DROP TABLE #tf;