Questions tagged [cardinality-estimates]
136 questions
28
votes
1 answer
SQL Server 2014: any explanation for inconsistent self join cardinality estimate?
Consider the following query plan in SQL Server 2014:
In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 rows and just one distinct value of fId (accurately…
Geoff Patterson
- 8,447
- 2
- 28
- 53
26
votes
2 answers
Why does a subquery reduce the row estimate to 1?
Consider the following contrived but simple query:
SELECT
ID
, CASE
WHEN ID <> 0
THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE)
ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2)
END AS ID2
FROM X_HEAP;
I would expect the final row estimate…
Joe Obbish
- 32,976
- 4
- 74
- 153
26
votes
2 answers
Why does LEN() function badly underestimate cardinality in SQL Server 2014?
I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is yielding very poor plans because there are…
Geoff Patterson
- 8,447
- 2
- 28
- 53
25
votes
2 answers
Cardinality Estimate for LIKE operator (Local Variables)
I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to…
Fza
- 652
- 1
- 9
- 19
20
votes
1 answer
Warning in query plan "Cardinality Estimate"
create table T(ID int identity primary key)
insert into T default values
insert into T default values
go
select cast(ID as varchar(10)) as ID
from T
where ID = 1
The query above has a warning in the query plan.
Mikael Eriksson
- 22,295
- 5
- 63
- 106
20
votes
2 answers
Why is this join cardinality estimate so large?
I am experiencing what I think is an impossibly high cardinality estimate for the following query:
SELECT dm.PRIMARY_ID
FROM
(
SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN…
Joe Obbish
- 32,976
- 4
- 74
- 153
20
votes
2 answers
Why does Concatenation operator estimate fewer rows than its inputs?
In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs.
However, an estimate of~238 million rows is produced, leading…
Geoff Patterson
- 8,447
- 2
- 28
- 53
16
votes
1 answer
SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x
For a COUNT(DISTINCT) that has ~1 billion distinct values, I'm getting a query plan with a hash aggregate estimated to have only ~3 million rows.
Why is this happening? SQL Server 2012 produces a good estimate, so is this a bug in SQL Server 2014…
Geoff Patterson
- 8,447
- 2
- 28
- 53
15
votes
1 answer
Cardinality estimate outside the histogram
Setup
I'm having some trouble understanding a cardinality estimate. Here's my test setup:
the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new CE (compatibility level 140)
I have this…
Josh Darnell
- 30,133
- 5
- 70
- 124
15
votes
1 answer
Sort spills to tempdb but estimated rows equals to actual rows
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.
When you take a look at the query plan…
Frederik Vanderhaegen
- 2,122
- 1
- 17
- 36
15
votes
2 answers
Cardinality estimation problem on inner join
I'm struggling to understand why row estimation is so terribly wrong, here is my case:
Simple join - using SQL Server 2016 sp2 (same issue on sp1), dbcompatiblity=130.
select Amount_TransactionCurrency_id, CurrencyShareds.id
from CurrencyShareds
…
LeMaciek
- 231
- 2
- 9
14
votes
1 answer
How does SQL Server's optimizer estimate the number of rows in a joined table?
I am running this query in the AdventureWorks2012 database:
SELECT
s.SalesOrderID,
d.CarrierTrackingNumber,
d.ProductID,
d.OrderQty
FROM Sales.SalesOrderHeader s
JOIN Sales.SalesOrderDetail d
ON s.SalesOrderID =…
8kb
- 2,639
- 2
- 32
- 36
14
votes
3 answers
Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?
I have a query that runs in 800 milliseconds in SQL Server 2012 and takes about 170 seconds in SQL Server 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count Spool operator. I've read a bit about spool…
Geoff Patterson
- 8,447
- 2
- 28
- 53
13
votes
1 answer
Сardinality estimation of partially covering range predicates
At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step.
On the Internet, at cardinality-estimation-for-and-for-intra-step-statistics-value I came across a similar…
Павел Ковалёв
- 194
- 9
13
votes
1 answer
Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?
Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions?
The reason I'm asking is that I was looking at a query whose performance…
James Lupolt
- 4,278
- 5
- 31
- 46