As of May 31, 2023, we have updated our Code of Conduct.

Questions tagged [cardinality-estimates]

13 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
2 answers
232 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,169
2 votes
0 answers
129 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
  • 427
1 vote
1 answer
17 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
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 ...
Bo Anderson's user avatar
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. ...
BeginnerDBA's user avatar
  • 2,169
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 ...
Lucien's user avatar
  • 11
1 vote
0 answers
100 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
1 vote
0 answers
177 views

Converting an XML field to nvarchar causes warning on main SELECT in explain plan

I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following: SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p This ...
shawnyshawny's user avatar
1 vote
0 answers
57 views

What is the cardinality in this situation?

I need to find the cardinality for a few tables in an Oracle database. I have been given a word document that has a list of tables described with details as shown in the image: I have been reading ...
Willie's user avatar
  • 11
1 vote
0 answers
142 views

bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...
Marcello Miorelli's user avatar
0 votes
1 answer
46 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
0 votes
0 answers
415 views

Add foreign key constraint for denormalized columns in detail tables?

When a detail table contains denormalized data, should denormalized columns be included in foreign key relationships between the master table and detail table? Here's more details: We have a master/...
Justin Grant's user avatar
-3 votes
1 answer
257 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 ...
Dothertechie's user avatar