Questions tagged [cardinality-estimates]
The cardinality-estimates tag has no usage guidance.
13 questions with no upvoted or accepted answers
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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/...
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 ...