Questions tagged [cardinality-estimates]
The cardinality-estimates tag has no usage guidance.
24
questions
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
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
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
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
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
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
4
votes
1
answer
275
views
Badly-formed histogram causes bad estimates on Nested Loop
We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
- 1,994
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
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
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
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
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
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
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
5
votes
1
answer
719
views
SQL server 2012 inner join estimated number of rows issue
I have a simple join like the one below:
select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK
The fact table contains a little over 500 million records (with NC columnstore),...
- 143
3
votes
1
answer
901
views
Estimated rows very wrong when using declared variables
I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723):
Declare @StartDate as date = '10/1/2014';
Declare @EndDate as ...
- 527
3
votes
1
answer
199
views
PostgreSQL + query planner + amount of entities
I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan.
I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
- 131
3
votes
2
answers
1k
views
Improving distinct values estimates in Postgres
Full counts in Postgres can be slow, for reasons that are well-understood and much discussed. So, I've been using estimation techniques instead, where possible. For rows, pg_stats seems fine, for ...
- 829
2
votes
2
answers
3k
views
How to improve row estimate of 1 row in join for newly inserted data
A custom statistic exists for the CacheId column of a table. After an overnight statistics update:
Statistics for INDEX 'ST_TableName_CacheId'.
-------------------------------------------------------...
- 2,036
1
vote
0
answers
57
views
DateTime to Format: Implicit Conversion [duplicate]
I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used:
CONVERT(char(26), c1.DateTime, 109)
Which works successfully, but at least on SQL Server ...
- 465
1
vote
1
answer
50
views
Understanding Sort Estimates with TOP(variable)
I have the below query and I am trying to understand the sort estimate when I use TOP with a variable.
PLAN
create table justint ( c1 int )
insert into justint
select top 1000000 row_number() ...
- 1,728
0
votes
2
answers
535
views
MariaDB 10.4 Query Optimizer Wrong Execution Plan
I have a very strange behavior with some tables and the query optimizer on a MariaDB server.
First of all we have our main table let's call it huge and we have on the same database another table a ...
