Finds where the optimiser’s row estimates are badly wrong, the root of most bad plans. Part 1 works on every version; part 2 (SQL Server 2019 and later) goes operator by operator through the last actual plan and shows the table, index and columns where a filtered statistic would help. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
07_Cardinality_Estimate_Skew.sql - where the optimiser's row estimates are badly wrong. Clive Roberts DBA, 10 October 2026
Read-only. Server-wide (the plan cache covers every database). SQL Server 2016-2025.
WHY: a bad estimate is the root of most bad plans - wrong join type, wrong memory grant, a scan where a seek
was needed. When the estimate is wrong because the data is lopsided (one value holds most of the rows), a new
index often doesn't help but a FILTERED STATISTIC does. This script finds those places systematically.
PART 1 (every version): whole statements whose estimated rows are far from the rows they actually returned
(from sys.dm_exec_query_stats). Good for spotting the queries; it can't say which operator is wrong.
PART 2 (SQL Server 2019+): operator by operator, from the last ACTUAL plan of each query - shows the table,
index and the columns in the predicate, i.e. exactly where a filtered statistic would go.
Needs, in each database you care about (small overhead, safe to leave on):
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
HOW TO READ IT
times_out how many times bigger the larger of estimate/actual is (10 = ten times out). Over 10 matters,
over 100 almost certainly gives a poor plan.
direction UNDER = SQL Server expected fewer rows than it got (too small a memory grant -> spills to tempdb,
nested loops over millions of rows); OVER = expected more (needless scans, big grants, parallelism).
predicate_columns (part 2) - the columns being filtered or sought on. Check them with 08_Filtered_Statistics_Candidates.sql.
Then: look at the column's histogram (DBCC SHOW_STATISTICS), and test a filtered statistic, e.g.
CREATE STATISTICS fs_Orders_Open ON dbo.Orders (CustomerID) WHERE Status = 'Open' WITH FULLSCAN;
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @TopQueries int = 500; -- how many of the most expensive cached statements to examine (by CPU)
DECLARE @MinRows bigint = 1000; -- ignore cases where both the estimate and the actual are below this
DECLARE @MinTimesOut int = 10; -- show only estimates at least this many times out
DECLARE @IncludeSystemDbs bit = 0; -- 1 = also show queries run in master/msdb/model
------------------------------------------------------------------------------------------------------------------
-- PART 1: statement level (all versions)
------------------------------------------------------------------------------------------------------------------
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
top_stmt AS (
SELECT TOP (@TopQueries) qs.sql_handle, qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset,
qs.execution_count, qs.last_rows, qs.min_rows, qs.max_rows, qs.total_worker_time, qs.last_execution_time, pa.dbid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS pa
WHERE @IncludeSystemDbs = 1 OR pa.dbid > 4
ORDER BY qs.total_worker_time DESC),
est AS (
SELECT t.*, x.p.value('(//StmtSimple/@StatementEstRows)[1]', 'float') AS est_rows,
x.p.value('(//StmtSimple/@CardinalityEstimationModelVersion)[1]', 'int') AS ce_version
FROM top_stmt AS t
CROSS APPLY sys.dm_exec_text_query_plan(t.plan_handle, t.statement_start_offset, t.statement_end_offset) AS tp
CROSS APPLY (SELECT TRY_CONVERT(xml, tp.query_plan) AS p) AS x
WHERE x.p IS NOT NULL)
SELECT TOP (100)
DB_NAME(e.dbid) AS [database], OBJECT_NAME(st.objectid, st.dbid) AS [object],
CONVERT(bigint, e.est_rows) AS estimated_rows, e.last_rows AS actual_rows_last, e.min_rows, e.max_rows,
CONVERT(decimal(12,1), (CASE WHEN e.est_rows > e.last_rows THEN e.est_rows ELSE e.last_rows END)
/ NULLIF(CASE WHEN e.est_rows < e.last_rows THEN CASE WHEN e.est_rows < 1 THEN 1 ELSE e.est_rows END
ELSE CASE WHEN e.last_rows < 1 THEN 1 ELSE e.last_rows END END, 0)) AS times_out,
CASE WHEN e.est_rows < e.last_rows THEN 'UNDER' ELSE 'OVER' END AS direction,
e.ce_version, e.execution_count, e.total_worker_time / 1000 AS total_cpu_ms, e.last_execution_time,
SUBSTRING(st.text, e.statement_start_offset / 2 + 1,
(CASE WHEN e.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE e.statement_end_offset END
- e.statement_start_offset) / 2 + 1) AS statement
FROM est AS e
CROSS APPLY sys.dm_exec_sql_text(e.sql_handle) AS st
WHERE (e.est_rows >= @MinRows OR e.last_rows >= @MinRows)
AND st.text NOT LIKE N'%sys.dm[_]%' -- leave out monitoring queries (DMVs), including this one
AND (CASE WHEN e.est_rows > e.last_rows THEN e.est_rows ELSE e.last_rows END)
>= @MinTimesOut * (CASE WHEN e.est_rows < e.last_rows THEN CASE WHEN e.est_rows < 1 THEN 1 ELSE e.est_rows END
ELSE CASE WHEN e.last_rows < 1 THEN 1 ELSE e.last_rows END END)
AND (e.min_rows = e.max_rows OR e.min_rows * 10 >= e.max_rows) -- skip statements whose row count swings by design (parameter-driven)
ORDER BY times_out DESC;
-- Note: last_rows is what the statement RETURNED, so for INSERT/UPDATE/aggregate statements compare with care.
------------------------------------------------------------------------------------------------------------------
-- PART 2: operator level, from the last actual plan (SQL Server 2019+, needs LAST_QUERY_PLAN_STATS)
------------------------------------------------------------------------------------------------------------------
IF CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) < 15
BEGIN
PRINT N'Part 2 needs SQL Server 2019 or later (sys.dm_exec_query_plan_stats). Part 1 above still applies.';
RETURN;
END;
CREATE TABLE #lqps ([database] sysname, is_on bit);
DECLARE @db sysname, @sql nvarchar(max);
DECLARE d CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE database_id > 4 AND state_desc = N'ONLINE' AND HAS_DBACCESS(name) = 1;
OPEN d; FETCH NEXT FROM d INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db) + N'; INSERT #lqps SELECT DB_NAME(), CONVERT(bit, value) FROM sys.database_scoped_configurations WHERE name = N''LAST_QUERY_PLAN_STATS'';';
EXEC (@sql);
FETCH NEXT FROM d INTO @db;
END;
CLOSE d; DEALLOCATE d;
IF NOT EXISTS (SELECT 1 FROM #lqps WHERE is_on = 1)
PRINT N'Part 2: LAST_QUERY_PLAN_STATS is off in every user database, so there are no actual plans to read. Turn it on in the databases to check (see the top of this script), let the workload run, then run this again.';
SET @sql = N'
;WITH XMLNAMESPACES (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan''),
top_plans AS (
SELECT TOP (@TopQueries) qs.plan_handle, MAX(qs.sql_handle) AS sql_handle, SUM(qs.execution_count) AS execution_count,
SUM(qs.total_worker_time) AS total_worker_time, MAX(pa.dbid) AS dbid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N''dbid'') AS pa
WHERE @IncludeSystemDbs = 1 OR pa.dbid > 4
GROUP BY qs.plan_handle ORDER BY SUM(qs.total_worker_time) DESC),
ops AS (
SELECT tp.plan_handle, tp.sql_handle, tp.dbid, tp.execution_count, tp.total_worker_time,
r.op.value(''@NodeId'', ''int'') AS node_id,
r.op.value(''@PhysicalOp'', ''nvarchar(60)'') AS physical_op,
r.op.value(''@EstimateRows'', ''float'') * (1 + ISNULL(r.op.value(''@EstimateRebinds'', ''float''), 0)
+ ISNULL(r.op.value(''@EstimateRewinds'', ''float''), 0)) AS est_rows,
r.op.value(''sum(RunTimeInformation/RunTimeCountersPerThread/@ActualRows)'', ''float'') AS actual_rows,
r.op.value(''(*/Object/@Database)[1]'', ''nvarchar(260)'') AS db_name,
r.op.value(''(*/Object/@Schema)[1]'', ''nvarchar(260)'') AS schema_name,
r.op.value(''(*/Object/@Table)[1]'', ''nvarchar(260)'') AS table_name,
r.op.value(''(*/Object/@Index)[1]'', ''nvarchar(260)'') AS index_name,
r.op.query(''for $c in distinct-values((*/Predicate//ColumnReference/@Column, */SeekPredicates//ColumnReference/@Column)) return concat($c, " ")'').value(''.'', ''nvarchar(1000)'') AS predicate_columns
FROM top_plans AS tp
CROSS APPLY sys.dm_exec_query_plan_stats(tp.plan_handle) AS qps
CROSS APPLY qps.query_plan.nodes(''//RelOp[RunTimeInformation]'') AS r(op))
SELECT TOP (100) DB_NAME(o.dbid) AS [database], OBJECT_NAME(st.objectid, st.dbid) AS [object],
o.physical_op AS operator, o.node_id,
REPLACE(REPLACE(ISNULL(o.schema_name + N''.'', N'''') + ISNULL(o.table_name, N''''), N''['', N''''), N'']'', N'''') AS [table],
REPLACE(REPLACE(o.index_name, N''['', N''''), N'']'', N'''') AS [index],
REPLACE(REPLACE(RTRIM(o.predicate_columns), N''['', N''''), N'']'', N'''') AS predicate_columns,
CONVERT(bigint, o.est_rows) AS estimated_rows, CONVERT(bigint, o.actual_rows) AS actual_rows,
CONVERT(decimal(12,1), (CASE WHEN o.est_rows > o.actual_rows THEN o.est_rows ELSE o.actual_rows END)
/ NULLIF(CASE WHEN o.est_rows < o.actual_rows THEN CASE WHEN o.est_rows < 1 THEN 1 ELSE o.est_rows END
ELSE CASE WHEN o.actual_rows < 1 THEN 1 ELSE o.actual_rows END END, 0)) AS times_out,
CASE WHEN o.est_rows < o.actual_rows THEN ''UNDER'' ELSE ''OVER'' END AS direction,
o.execution_count, o.total_worker_time / 1000 AS total_cpu_ms,
LEFT(st.text, 4000) AS query_text
FROM ops AS o
CROSS APPLY sys.dm_exec_sql_text(o.sql_handle) AS st
WHERE (o.est_rows >= @MinRows OR o.actual_rows >= @MinRows)
AND st.text NOT LIKE N''%sys.dm[_]%'' -- leave out monitoring queries (DMVs), including this one
AND (CASE WHEN o.est_rows > o.actual_rows THEN o.est_rows ELSE o.actual_rows END)
>= @MinTimesOut * (CASE WHEN o.est_rows < o.actual_rows THEN CASE WHEN o.est_rows < 1 THEN 1 ELSE o.est_rows END
ELSE CASE WHEN o.actual_rows < 1 THEN 1 ELSE o.actual_rows END END)
ORDER BY times_out DESC;';
EXEC sys.sp_executesql @sql, N'@TopQueries int, @MinRows bigint, @MinTimesOut int, @IncludeSystemDbs bit', @TopQueries, @MinRows, @MinTimesOut, @IncludeSystemDbs;
DROP TABLE #lqps;