Questions tagged [cardinality-estimates]

12 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
2answers
156 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 ...
1
vote
1answer
103 views

Index or query tuning for complex predicates

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 ...
1
vote
0answers
34 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. ...
1
vote
0answers
66 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 ...
1
vote
0answers
31 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 ...
1
vote
0answers
110 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 ...
1
vote
0answers
47 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 ...
1
vote
0answers
122 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 ...
0
votes
0answers
19 views

Why do tempdb spills still occur even with good row and data size estimates (better than actuals)?

We're seeing tempdb DB spills for some hashing operations. If the estimates are indeed good as shown what would be the next thing(s) to look for? Looking for a generic answer without having to ...
0
votes
0answers
268 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/...
0
votes
1answer
106 views

Table scan estimates when there are no stats on the table

Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation: create table test( a int ) go insert into test ...
-3
votes
1answer
169 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 ...