Questions tagged [cardinality-estimates]
The cardinality-estimates tag has no usage guidance.
127
questions
0
votes
1
answer
49
views
SQL Server 2014 Cardinality Estimator estimate final number of rows after OUTER JOIN is less than number of rows from initial table
I have a [SQL query] for SQL Server 2019.
It works fine with option
(USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
and very bad without this hint.
I found out, that SQL Server 2014 Cardinality ...
3
votes
1
answer
220
views
How does SQL Server estimate cardinality on nested loops index seek
I am trying to understand how SQL Server estimates cardinality on the below Stack Overflow database query
Firstly, I create the index
CREATE INDEX IX_PostId ON dbo.Comments
(
PostId
)
INCLUDE
(
...
- 80.3k
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 ...
- 80.3k
1
vote
1
answer
18
views
PostgreSQL: The actual rows number of Materialized Merge Join Node does not inherit the inner node
I found a plan to perform strangely. The actual rows number of Materialized Merge Join Node does not inherit the inner node, but is consistent with the parent node. The visualization of the plan is ...
- 36.5k
5
votes
1
answer
263
views
SQL server Cardinality Estimation for LIKE query
I have this statistics histogram vector for my non-clustered index made on LastName column of a table named AspNetUsers.
If I run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = 'Baker' it ...
- 81.3k
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,181
0
votes
1
answer
47
views
Average Density & cardinality estimation
In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in SSMS. After checking the basics like the same SET options, we found that there was ...
- 1,795
6
votes
1
answer
509
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 ...
- 80.3k
4
votes
1
answer
341
views
Scalar function alters estimate for preceding operator
Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?
I'm using a copy of the StackOverflow2013 database.
SQL Server 2019 (...
- 179k
1
vote
2
answers
373
views
is it possible to make the postgresql estimate the rows by condition
I have an article table with more than 2000000 rows, because the count was so slow, I am using the estimate rows like this:
select count_estimate('select * from article');
this works for the article ...
- 36.5k
2
votes
0
answers
133
views
Sys.dm_os_performance_counters DMV row counts
We have noticed an odd issue on two servers in a 3 node SQL Server 2017 cluster running Always On Availability Groups.
When checking the DMV sys.dm_os_performance_counters the row counts are totally ...
- 80.3k
4
votes
1
answer
190
views
How does SQL estimate the number of rows in a less than < Predicate
I have been doing some testing to try to better understand how SQL Server uses a histogram to estimate the number of rows that will match an equality predicate and also a < or > predicate
Given ...
- 80.3k
3
votes
1
answer
142
views
Why Does SQL Server not have 200 buckets in the statistics histogram when there are >100k distinct values in the table
Given I am using the AdventureWorks2016 OLTP database why does the statistics histogram for the index PK_TransactionHistory_TransactionID on table Production.TransactionHistory only contain 3 ...
- 1,471
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....
- 80.3k
3
votes
5
answers
737
views
Actual and Estimated rows differ greatly
The full Actual plan is here.
Prior to executing the plan (because I'm debugging a poorly functioning plan) I have this block of variable assignments:
DECLARE @Days INT = 180
DECLARE @DateRangeFrom ...
- 80.3k
3
votes
1
answer
170
views
How to correct row estimates due to join of 2 well-estimated results
The following query runs across ~60 databases in parallel. Without hints there are numerous spills and non-optimal plans in at least 10% of the DBs.
Using a larger DB as a guide, the query was locked ...
- 31.7k
0
votes
1
answer
135
views
Conversion may affect "CardinalityEstimate" CONCAT NVARCHAR and UNIQUEIDENTIFIER
So I have a temporary table with only one column NVARCHAR(400).
In this temp table, I Insert values from various sources.
One of these is
INSERT INTO @permissions
SELECT Concat (N'resourcetype_', ...
- 32.1k
9
votes
2
answers
659
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 ...
- 80.3k
2
votes
1
answer
145
views
Index or query tuning for complex predicates [closed]
I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates:
JOIN on a date from one table being in range - between two date ...
CommunityBot
- 1
5
votes
2
answers
2k
views
Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?
Based on Query Performance and multi-statement table valued functions and other articles, SQL Server assumes that a multi-line table valued function returns one row. This causes the selection of a ...
- 2,181
8
votes
2
answers
592
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
1
vote
0
answers
165
views
Actual Number of Rows Significantly Lower than Estimated
My Problem
I am having an issue with a update query, where the execution plan has a bad cardinality estimate. It is estimated way more rows (14000%) than there should be when it does an Index seek on ...
- 45
0
votes
1
answer
130
views
Table scan estimates when there are no stats on the table
Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation:
create table test( a int )
go
insert into test ...
CommunityBot
- 1
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 (...
- 37.6k
0
votes
2
answers
771
views
Cardinality , Parallel Hint alternative for MS-SQL
UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS
(SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1
WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y');
...
- 15.9k
3
votes
2
answers
2k
views
Postgres prefers slow Seq Scan over fast Index Scan
Tables
I have the following two tables.
books, which contains ~5 million rows:
Table "public.books"
Column | Type | ...
- 133
3
votes
2
answers
551
views
SQL Server Cardinality Estimation Warning [duplicate]
How can I fix the following cardinality estimation warning?
while exists(select 1 from @images)
begin
declare @imageid int
declare @filename varchar(max)
select TOP (1)
@imageid =...
- 32.1k
1
vote
2
answers
731
views
Preventative measures to avoid wrong cardinality estimate statistics
This is a follow-up to Queries randomly getting slow, good preventive practices?, attempting to ask a more specific question about one of the ideas stated there.
A common cause of queries suddenly ...
- 10.9k
0
votes
2
answers
537
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 ...
- 74.9k
0
votes
2
answers
101
views
Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?
I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
- 1,562
11
votes
2
answers
904
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 ...
- 80.3k
1
vote
1
answer
430
views
Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?
The Backstory:
I have a table with the following structure:
CREATE TABLE WideTable1
(
BoringColumn1,
BoringColumn2,
CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
- 37.6k
0
votes
1
answer
355
views
Legacy CE vs current
I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE:
But 5 minutes with CE 150:
https://www....
- 31.7k
3
votes
1
answer
265
views
What Method / Formula does a Nested Loops Operator use for row estimation?
The following, simple query in AdventureWorks:
SELECT *
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
Gives the following ...
- 80.3k
0
votes
1
answer
142
views
How to improve the estimated cardinality of inner join lateral
I'm currently running a query that has the following structure:
select
v1.*,
v2.*,
...
from t1
inner join lateral f1(t1.id1, t1.id2) as v1 on true
inner join lateral f2(t1.id1, t1.id2) ...
- 80.3k
4
votes
1
answer
279
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 ...
- 80.3k
11
votes
1
answer
774
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 ...
- 80.3k
28
votes
1
answer
817
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 ...
- 80.3k
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 ...
- 24.3k
1
vote
0
answers
36
views
How statistics play a major role in legacy CE vs new CE
We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100.
Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
- 2,169
0
votes
1
answer
36
views
Bad estimates when sorting, good estimates without sort
I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1....
- 44.3k
1
vote
2
answers
2k
views
A low cardinality column index and SELECT
Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values.
Suppose also that most (say 99% or even 99.9%) of these values are equal to ...
3
votes
2
answers
235
views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
CommunityBot
- 1
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 ...
- 179k
1
vote
0
answers
85
views
Incorrect Estimates (CE Model 130) on uniqueidentifier columns
We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns.
The select is very ...
- 11
0
votes
1
answer
220
views
How do cardinality estimates affect CPU and Reads in SQL Server?
Trying to understand a concept, not sure where to look for answers. I have been researching cardinality estimates and their affects on memory. In the process of optimizing a query to get better ...
- 28.6k
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 ?
- 16.6k
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 ...
- 80.3k
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 ...
- 80.3k
0
votes
1
answer
42
views
What makes for a good index in this scenario?
I have a pretty big temp table (~5 million rows) that I need to update a few fields on.
Here's an example update statement:
UPDATE T
SET CarModel =
CASE
WHEN CarModel = '350z' AND ...
- 32.1k