Skip to main content

Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
2 votes
0 answers
60 views

SQL Server Underestimating cardinality on a Filter operator in a Left Anti-Join

I am tuning a query which is slow, I have narrowed the root of the problem to be at the very beginning of the execution plan, where SQL Server makes a bad estimate on a WHERE IS NULL filter that ...
SE1986's user avatar
  • 2,000
1 vote
2 answers
493 views

How can I optimize a recursive CTE inside a IVTF?

I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
James McGrath's user avatar
1 vote
1 answer
91 views

Wrong cardinality estimation after gathering statistics ORACLE

We have a large table range partitioned by month. Incremental statistics turned on. After scheduled statistics gathering cardinality estimation become weird, like select count(*) from my_table where ...
Andy DB Analyst's user avatar
0 votes
1 answer
191 views

Cardinality miscalculations leads to ridiculous execution plans

I'm not talking about stale statistics, or just simply "bad"/"non-optimal" plans. We have a lot of complicated queries running in our database. Normally all works as expected. But ...
Andy DB Analyst's user avatar
0 votes
1 answer
106 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 ...
Андрей Ерёмин's user avatar
3 votes
1 answer
319 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 ( ...
SE1986's user avatar
  • 2,000
1 vote
1 answer
107 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 ...
jiazhen peng's user avatar
5 votes
1 answer
304 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 ...
Dhanuka Jayasinghe's user avatar
0 votes
1 answer
67 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 ...
Hugo Delsing's user avatar
6 votes
1 answer
664 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 ...
Kuba Wyrostek's user avatar
4 votes
1 answer
360 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 (...
FrugalShaun's user avatar
1 vote
2 answers
890 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 ...
Dolphin's user avatar
  • 785
2 votes
0 answers
161 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 ...
Stockburn's user avatar
  • 431
4 votes
1 answer
281 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 ...
SE1986's user avatar
  • 2,000
3 votes
1 answer
209 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 ...
SE1986's user avatar
  • 2,000
6 votes
1 answer
3k 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....
SE1986's user avatar
  • 2,000
4 votes
4 answers
2k 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 ...
Daniel Bragg's user avatar
3 votes
1 answer
276 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 ...
crokusek's user avatar
  • 2,120
0 votes
1 answer
228 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_', ...
Cătălin Rădoi's user avatar
9 votes
2 answers
822 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 ...
Mark Adam Proctor's user avatar
8 votes
2 answers
969 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 ...
Joshua Grippo's user avatar
1 vote
0 answers
289 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 ...
Bo Anderson's user avatar
0 votes
2 answers
1k 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'); ...
Luv Gupta's user avatar
3 votes
2 answers
3k 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 | ...
runeks's user avatar
  • 133
3 votes
2 answers
734 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 =...
kyletme's user avatar
  • 141
1 vote
2 answers
2k 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 ...
liori's user avatar
  • 289
0 votes
2 answers
822 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 ...
Michael Giann.'s user avatar
1 vote
1 answer
779 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 ...
J.D.'s user avatar
  • 38.3k
0 votes
2 answers
137 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 ...
J.D.'s user avatar
  • 38.3k
0 votes
1 answer
727 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....
Peter's user avatar
  • 1
3 votes
1 answer
307 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 ...
SE1986's user avatar
  • 2,000
0 votes
1 answer
213 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) ...
jmartori's user avatar
  • 103
2 votes
1 answer
149 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 ...
JShark's user avatar
  • 278
4 votes
1 answer
400 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 ...
Frederik Vanderhaegen's user avatar
1 vote
0 answers
39 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. ...
BeginnerDBA's user avatar
  • 2,180
0 votes
1 answer
39 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....
Frederik Vanderhaegen's user avatar
3 votes
2 answers
275 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 ...
BeginnerDBA's user avatar
  • 2,180
11 votes
1 answer
1k 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 ...
Павел Ковалёв's user avatar
1 vote
0 answers
90 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 ...
Lucien's user avatar
  • 11
0 votes
1 answer
429 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 ...
DRT's user avatar
  • 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 ?
user avatar
0 votes
1 answer
45 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 ...
J.D.'s user avatar
  • 38.3k
3 votes
2 answers
2k 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 ...
Morris de Oryx's user avatar
13 votes
1 answer
302 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-...
Павел Ковалёв's user avatar
8 votes
1 answer
1k 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 ...
Dan Oberlam's user avatar
3 votes
1 answer
278 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...) ...
Henrik Alstad's user avatar
15 votes
1 answer
577 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 ...
Josh Darnell's user avatar
  • 29.4k
1 vote
0 answers
151 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 ...
Felipe's user avatar
  • 161
2 votes
2 answers
729 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. ...
Felipe's user avatar
  • 161
4 votes
1 answer
147 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 ...
Hannah Vernon's user avatar
  • 70.2k