Questions tagged [cardinality-estimates]
The cardinality-estimates tag has no usage guidance.
125
questions
1
vote
1
answer
9
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 ...
- 11
5
votes
1
answer
244
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 ...
0
votes
1
answer
43
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 ...
- 113
6
votes
1
answer
494
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
4
votes
1
answer
339
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 (...
- 391
1
vote
2
answers
256
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 ...
- 621
2
votes
0
answers
124
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 ...
- 427
4
votes
1
answer
174
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 ...
- 1,809
3
votes
1
answer
130
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,809
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,809
3
votes
5
answers
557
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 ...
- 173
3
votes
1
answer
144
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 ...
- 1,996
0
votes
1
answer
114
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_', ...
- 103
9
votes
2
answers
622
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 ...
8
votes
2
answers
506
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
137
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
2
answers
682
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');
...
1
vote
2
answers
1k
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 | ...
- 113
3
votes
2
answers
512
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 =...
- 141
1
vote
2
answers
612
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 ...
- 289
0
votes
2
answers
481
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 ...
1
vote
1
answer
383
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 ...
- 30.5k
0
votes
2
answers
89
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 ...
- 30.5k
0
votes
1
answer
311
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....
- 1
3
votes
1
answer
236
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 ...
- 1,809
0
votes
1
answer
127
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) ...
- 103
2
votes
1
answer
143
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 ...
- 278
4
votes
1
answer
254
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
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,149
0
votes
1
answer
35
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....
- 1,994
3
votes
2
answers
216
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 ...
- 2,149
11
votes
1
answer
734
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
1
vote
0
answers
78
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
195
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 ...
- 41
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
0
votes
1
answer
41
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 ...
- 30.5k
3
votes
1
answer
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
13
votes
1
answer
273
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
8
votes
1
answer
858
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
3
votes
1
answer
270
views
Why can't SQL Server optimizer use foreign key [duplicate]
I have the following tables(See below for complete reproducible SQL):
ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)
...
- 271
15
votes
1
answer
538
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.5k
1
vote
0
answers
86
views
Understanding where/how to use the "selectivity of an equality predicate between two columns"
This lecture on "Result size estimation" defines a formula of equi-joins based on Selinger paper.
result cardinality = max number of tuples possible * product of all selectivities
1 - max number of ...
- 161
2
votes
2
answers
491
views
Example of query execution plan where the cardinality estimation makes a difference
I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost.
...
- 161
4
votes
1
answer
141
views
No IMPLICIT_CONVERSION warning in this query plan
Take the following minimally complete and verifiable example1 code:
USE tempdb;
DROP TABLE IF EXISTS dbo.t;
GO
CREATE TABLE dbo.t
(
t_id int NOT NULL
CONSTRAINT t_pk
PRIMARY KEY
...
- 68.5k
-3
votes
1
answer
249
views
Setting Cardinality Estimator in SQL Server 2016 for performance & using old CE of SQL Server 2012
Post upgrade to SQL Server 2016, we saw a lot of I/O issues followed by slowness which affected the whole system.
Upon investigation, we found that top queries are delete statements.
Execution plans ...
- 37
3
votes
1
answer
133
views
Direct values on predicate produce not-so-good plan
I'm using StackOverflow dump to run some tests.
In particular, I'm querying this table:
I have created this index:
I'm running the following query (just forcing an index to test alternatives)
I ...
- 420
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
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,809
11
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 ...
- 220
15
votes
1
answer
661
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