Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
0
votes
2answers
61 views
+50

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 ...
11
votes
2answers
424 views

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?

I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
1
vote
1answer
58 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 ...
0
votes
1answer
72 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....
3
votes
1answer
103 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 ...
0
votes
1answer
37 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) ...
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
81 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 ...
10
votes
1answer
298 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 ...
28
votes
1answer
715 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
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 ...
10
votes
1answer
493 views

Cardinality estimation on tables with large cardinality variance between keys

Context I have stumbled on an issue in SQL Azure where the Sort-operator is spilling over into TempDB due to poor row estimates. We are querying a master table that is joined with a number of detail ...
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. ...
0
votes
1answer
25 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....
1
vote
2answers
761 views

A low cardinality column index and SELECT

Suppose that a long (1000000 entries for example) MySQL table t has a column c with less than 10 distinct TINYINT values. Suppose also that most (say 99% or even 99.9%) of these values are equal to ...
3
votes
2answers
155 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 ...
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 ...
7
votes
3answers
3k views

Warning about implicit convert

I have table with 2 name columns: CREATE TABLE Test ( TestID int identity primary key clustered , Name_Eng nvarchar(50) , Name_Nat nvarchar(50) ) Now I need a query to get this Name column ...
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 ...
0
votes
1answer
55 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 ...
5
votes
2answers
845 views

Compatibility level of SQL Server

Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
26
votes
2answers
1k views

Why does a subquery reduce the row estimate to 1?

Consider the following contrived but simple query: SELECT ID , CASE WHEN ID <> 0 THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) END AS ...
24
votes
2answers
2k views

Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and ...
0
votes
1answer
39 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 ...
1
vote
1answer
192 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 ...
13
votes
1answer
210 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-...
7
votes
1answer
223 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 ...
11
votes
1answer
11k views

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data. We constraint the events on ...
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 ...
2
votes
1answer
678 views

Datetime to formatted Date Implicit Conversion

I am looking at implicit conversions in my stored procedures and the biggest one I have in my prod database is a warning by the Cardinality Estimator in SQL 2014 for converting datetime to formatted ...
5
votes
1answer
503 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
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 ...
4
votes
1answer
1k views

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

I had a look at this question: Type conversion in expression may affect “CardinalityEstimate” in query plan choice? But that was related to collation and not with computed columns. I have the ...
7
votes
1answer
20k views

Type conversion in expression try_cast may affect cardinality estimate in query plan choice

I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan ...
3
votes
2answers
3k views

What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice

Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have ...
15
votes
1answer
433 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 ...
3
votes
1answer
240 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...) ...
9
votes
1answer
631 views

Hash join between master/detail tables produces too-low cardinality estimate

When joining a master table to a detail table, how can I encourage SQL Server 2014 to use the cardinality estimate of the larger (detail) table as the cardinality estimate of the join output? For ...
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 ...
2
votes
2answers
162 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. ...
4
votes
1answer
131 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 ...
7
votes
1answer
5k views

Estimating the size (of the records) and the overhead in Postgres

Consider the following table in Postgres 9.4: CREATE TABLE t ( a1 bigserial, a2 bigint NOT NULL, a3 bigint NOT NULL, a4 integer, a5 timestamp with time zone NOT NULL, a6 timestamp with ...
2
votes
1answer
215 views

Histogram Skew on Clustered Index

Problem I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the ...
2
votes
1answer
376 views

Different plans on Readonly Secondary Replica

We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose. One of the reports intermittently runs for much longer than normal. I can see when it runs ...
-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 ...
3
votes
1answer
120 views

Direct values on predicate produce not-so-good plan

I'm using StackOverflow dump to run some tests. In particular, I'm querying this table: I have created this index: I'm running the following query (just forcing an index to test alternatives) I ...
7
votes
1answer
562 views

When do _WA_Sys_ statistics Get Updated?

I have some of the auto generated _WA_Sys_ statistics in my database which have not been updated in a while (by comparison to other statistics in the same table) The rule of thumb appears to be in ...
18
votes
1answer
12k views

Warning in query plan “Cardinality Estimate”

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
11
votes
2answers
2k views

Why does a DELETE query run in one format much longer than in another?

I have specific cleanup code that tries to remove some duplicates. This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query: DELETE ...
15
votes
1answer
505 views

Sort spills to tempdb but estimated rows equals to actual rows

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of ...