Questions tagged [cardinality-estimation]
19 questions
19
votes
3 answers
New cardinality estimator (SQL Server 2014) is way off
I have a data warehouse database and I'm facing problems with the new cardinality estimator of SQL Server 2014.
After upgrading the database server to SQL Server 2014 I have observed a big difference in query performance. Some queries are executing…
saso
- 754
- 7
- 18
14
votes
2 answers
Applying HyperLogLog to a sample of the population
The HyperLogLog
algorithm by Flajolet et al describes a clever way to estimate the cardinality
of a set using only a tiny amount of memory. However, it does take into
account all N elements of the original set in the calculation. What if
we had…
Jon Smark
- 2,528
- 24
- 31
2
votes
0 answers
Predicting Future Location of Soccer Ball Rolling on ground
the question might seem simple, but I am unable to find the best reference for it. Using ODE force function, I am able to apply force to a ball's COM and the ball starts rolling. It stops after a few seconds. The distance travelled depends upon the…
Saman
- 21
- 1
2
votes
0 answers
Oracle Compound Join Predicate Causes Row Estimate to be Incorrect
In the example below Oracle's optimizer's estimated rows is incorrect by two orders of magnitude. How do I improve the estimated rows?
Table A has rows with numbers 1 through 1,000 for each of the 10 letters A through J.
Table C has 100 copies of…
Alex Bartsmon
- 471
- 4
- 9
2
votes
1 answer
SQL Server 2014 - some queries very slow (cardinality estimator)
In our production environment we had several servers with SQL server 2012 SP2+Windows Server 2008R2. 3 month ago we migrate all the servers to the SQL Server 2014 SP1+Windows Server 2012 R1. We created new servers with new configuration (more RAM,…
Yusif Yusifov
- 98
- 5
2
votes
1 answer
Testing SQL Server 2014 new Cardinality estimator
I've upgraded a Pre-Prod server from SQL Server 2012 to SQL server 2014 Sp1.
It has been largely good however, In our brief manual testing we have found some issues where the new cardinality estimator has made significant decreases in performance on…
James Anderson
- 21
- 1
1
vote
1 answer
Is 100 a magic number for cardinality estimation of SELECT * FROM [Multi-Statement-Table-Valued-Function] queries?
I'm running on SQL Server 2019. Whenever I write a query of the form
SELECT *
FROM [Multi-Statement-Table-Valued-Function]
the execution plan shows that it expected to get 100 rows from this process. Is this a consistent and documented pattern? Or…
J. Mini
- 1,868
- 1
- 9
- 38
1
vote
1 answer
SQL Server: Cardinality estimation for table variable
SQL Server does not maintain statistics for table variable and it always estimates 1 output row. Microsoft changed this behavior post release of SQL Server 2019. Though I am not able to find much information about those changes.
Below is my sample…
KnowledgeSeeeker
- 620
- 1
- 9
- 14
1
vote
1 answer
Will setting cardinality estimator trace flag 9481 on/off automatically recompile affected plans?
Currently, we are using -T9481. If we run DBCC TRACEOFF 9481 to use a new cardinality estimator, will effect immediately.
Suppose I have a query that runs frequently and has an execution plan in the cache before turning off T9481. if the same query…
user19231705
- 15
- 2
1
vote
1 answer
Client-Side Pre-Computed Hashes for ElasticSearch Cardinality Aggregation
In the ElasticSearch documentation for the Cardinality Aggregation under the heading "Pre-computed hashes" I see the following:
On string fields that have a high cardinality, it might be faster to
store the hash of your field values in your index…
knpwrs
- 15,691
- 12
- 62
- 103
1
vote
2 answers
Oracle: Inner Row Source of Nested Loop - Incorrect Estimated Rows?
My understanding is that the estimated number of rows in an explain plan for the inner row source of a nested loop join reflects the number of rows for just one iteration of that nested loop.
In the following example, step 6 of the explain plan is…
Alex Bartsmon
- 471
- 4
- 9
1
vote
0 answers
Query with conditions on dictionary's keys
In a database I have a column (B) that has dictionaries in each element.
Eg. (from an element of the column B):
{
"table": 12,
"chair": 34,
"cat": 65,
}
I want to calculate its cardinality using hll.
In a query:
select hll(A, B, C) as card
from…
Andrea Vettorino
- 13
- 2
1
vote
2 answers
What is the intuition behind the Flajolet-Martin algorithm?
I have tried to understand why Flajolet-Martin Algorithm (FM) works for too long. The description of the algorithm here (section 4.4.2) is promising but not perfect.
Why does the the maximum tail length (# of trailing zeros) of any element work as…
Avocado
- 67
- 6
0
votes
0 answers
Slow query performance SQL 2016/SQL219 with new Cardinality Estimator
We have some of our queries that are ten times slower than with the old CE ... there are a lot of sites explaining this "problem".
But here we have an example where all the time is passed is the "SQL Server parse and compile time" part ...
Plans are…
steevi2307
- 9
- 2
0
votes
0 answers
Query that used to work in prior v2014 of SQL Server, dies in new v2019, and need Legacy Cardinality Estimator to fix. Is there a better way?
We've upgraded a SQL Server to 2019 from 2014.
In 2014 this query was fine:
INSERT INTO [schx].[US_New]
SELECT *
FROM [schx].[US_New_Stg]
WHERE FormattedID NOT IN (SELECT FormattedID
FROM [schx].[DLT]
…
A.G.
- 2,089
- 3
- 30
- 52