Questions tagged [cardinality-estimates]
The cardinality-estimates tag has no usage guidance.
127
questions
28
votes
1
answer
816
views
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 ...
- 8,277
26
votes
2
answers
1k
views
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 ...
- 31.7k
26
votes
2
answers
2k
views
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 ...
- 8,277
25
votes
2
answers
2k
views
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 ...
- 650
20
votes
2
answers
847
views
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 ...
- 8,277
20
votes
2
answers
2k
views
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
...
- 31.7k
20
votes
1
answer
15k
views
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 ...
- 21.9k
16
votes
1
answer
648
views
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 ...
- 8,277
15
votes
2
answers
2k
views
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 ...
- 231
15
votes
1
answer
671
views
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 ...
- 1,994
15
votes
1
answer
544
views
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 ...
- 28.6k
14
votes
3
answers
6k
views
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 ...
- 8,277
13
votes
1
answer
3k
views
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....
- 2,609
13
votes
2
answers
796
views
Cardinality Estimation for >= and > for intra step statistics value
I am trying to understand how SQL Server try to estimate for 'greater than' and 'greater than equal to' where clauses in SQL Server 2014.
I think I do understand the cardinality estimation when it ...
- 1,728
13
votes
1
answer
274
views
С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-...
- 194
13
votes
1
answer
400
views
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 ...
- 4,238
12
votes
2
answers
2k
views
Why does a DELETE query run in one format much longer than in another?
I have specific cleanup code that tries to remove some duplicates.
This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query:
DELETE ...
- 230
12
votes
1
answer
13k
views
Type conversion in expression may affect "CardinalityEstimate" in query plan choice?
I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.
We constraint the events on ...
- 2,298
12
votes
3
answers
10k
views
Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1?
I'm using SQL Server 2012 Enterprise. I've come across a SQL Plan that is exhibiting some behavior I don't find entirely intuitive. After a heavy Parallel Index Scan operation, a Parallelism (...
- 9,242
11
votes
1
answer
29k
views
Type conversion in expression try_cast may affect cardinality estimate in query plan choice
I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan ...
- 213
11
votes
2
answers
902
views
Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?
I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
- 257
11
votes
1
answer
769
views
Apply cardinality estimation problem in SQL Server
Now, I am faced with the problem of the logic of cardinality estimation that is not quite clear for me in a seemingly rather simple situation.
I encountered this situation at my work, therefore, for ...
- 194
10
votes
3
answers
788
views
Sort spills to tempdb due to varchar(max)
On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables:
CREATE TABLE [dbo].[Settings](
[id] [...
- 1,994
10
votes
1
answer
8k
views
Estimating the size (of the records) and the overhead in Postgres
Consider the following table in Postgres 9.4:
CREATE TABLE t
(
a1 bigserial,
a2 bigint NOT NULL,
a3 bigint NOT NULL,
a4 integer,
a5 timestamp with time zone NOT NULL,
a6 timestamp with ...
- 848
10
votes
1
answer
660
views
Cardinality estimation on tables with large cardinality variance between keys
Context
I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates.
We are querying a master table that is joined with a number of detail ...
- 201
9
votes
2
answers
655
views
How does the optimizer treat yet-unpopulated #temp tables when estimating cardinality?
I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables.
If I have 4 joins to a #temp table where that #temp table is created DURING ...
9
votes
1
answer
796
views
Hash join between master/detail tables produces too-low cardinality estimate
When joining a master table to a detail table, how can I encourage SQL Server 2014 to use the cardinality estimate of the larger (detail) table as the cardinality estimate of the join output?
For ...
- 751
9
votes
3
answers
2k
views
How to hint many-to-many join in SQL Server?
I have 3 "large" tables that join on a pair of columns (both ints).
Table1 has ~200 million rows
Table2 has ~1.5 million rows
Table3 has ~6 million rows
Each table has a clustered index on Key1, ...
- 720
8
votes
3
answers
4k
views
Warning about implicit convert
I have table with 2 name columns:
CREATE TABLE Test
(
TestID int identity primary key clustered
, Name_Eng nvarchar(50)
, Name_Nat nvarchar(50)
)
Now I need a query to get this Name column ...
- 1,403
8
votes
2
answers
9k
views
What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice
Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have ...
- 203
8
votes
1
answer
901
views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
- 183
8
votes
1
answer
227
views
Why does SQL Server estimate less rows will be emitted from a join after inserting some rows?
The below is a simplified version of something I came across in production (where the plan got catastrophically worse on a day where an unusually high number of batches were processed).
The repro ...
- 81.2k
8
votes
2
answers
584
views
SQL Server not Using Index
I am absolutely stumped as to why my query is not using what I think is a selective index.
My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many ...
- 140
7
votes
2
answers
560
views
Wrong estimate on a query on partitioned tables
I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario.
CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT
FOR VALUES (20140801, 20140802, 20140803)
CREATE ...
- 345
7
votes
1
answer
1k
views
When do _WA_Sys_ statistics Get Updated?
I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table)
The rule of thumb appears to be in ...
- 1,741
7
votes
1
answer
180
views
very poor cardinality estimate on a geometry intersection
I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results ...
- 259
6
votes
1
answer
2k
views
Rebuilding indexes for a partitioned table having 300 partitions
Scenario
Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded.
Issue
While ...
- 73
6
votes
1
answer
508
views
Estimations seem too low/inaccurate for nested loops operator
This question is a follow-up to somebody else's question: Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?
My question is ...
- 171
6
votes
1
answer
382
views
How SQL server generates a Query plan with Auto Create Statistics set to OFF
I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off".
I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, ...
- 355
6
votes
1
answer
1k
views
Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan
Given the following simple query on the StackOverflow2010 database:
SELECT u.DisplayName,
u.Reputation
FROM Users u
JOIN Posts p
ON u.id = p.OwnerUserId
WHERE u....
- 1,741
6
votes
1
answer
4k
views
Type conversion in expression may affect “CardinalityEstimate” - on a computed column?
I had a look at this question:
Type conversion in expression may affect “CardinalityEstimate” in query plan choice?
But that was related to collation and not with computed columns.
I have the ...
- 15.6k
6
votes
1
answer
462
views
Why is an additional filtered statistic being ignored (EAV schema)?
I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values ...
- 2,036
6
votes
2
answers
342
views
What are thresholds in SQL Server 2014 "big table join to small table" cardinality estimation optimization?
The SQL Server 2014 Cardinality Estimator white paper says:
The new CE, however, uses a simpler algorithm that assumes that there is a one-to-many join association between a large table and a ...
- 751
6
votes
2
answers
755
views
How do I effectively handle very skewed data? Statistics up to date but don't seem to help
I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed. 125,000 rows are 'user1', and the remaining 5000 rows are split ...
- 389
6
votes
1
answer
310
views
Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.?
The new cardinality estimator (CE) in SQL Server 2016 has an unfortunate regression which hits our deployment hard. The query in question is a run-of-the-mill Hibernate generated update; however the ...
user22674
5
votes
2
answers
1k
views
Compatibility level of SQL Server
Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
- 189
5
votes
1
answer
667
views
Bad row estimate following Compute Scalar operator in plan
I'm struggling to understand where a row estimate is coming from in an execution plan.
Paste the plan link
declare
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
@...
- 5,529
5
votes
2
answers
2k
views
Update statistics on sys.objects
I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on.
However, this question in ...
- 15.6k
5
votes
1
answer
1k
views
Stream Aggregate for Sort?
Trying to understand Couple of things in the above query plan.
Why is there a Stream Aggregate in the query when I dont have a group
by. I am guess it has something to do with the join being merge ...
- 1,728
5
votes
2
answers
768
views
Nested Loop has very low estimates due to skewed data
On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb.
If I'm correct SQL Server 2014+ uses Coarse ...
- 1,994