Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
28 votes
Accepted

Cardinality Estimate for LIKE operator (Local Variables)

The guess for LIKE in your case is based on: G: The standard 9% guess (sqllang!x_Selectivity_Like) M: A factor of 6 (magic number) D: Average data length in bytes (from statistics), rounded down to ...
Paul White's user avatar
  • 85.4k
24 votes
Accepted

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

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Consistency There is no general guarantee of ...
Paul White's user avatar
  • 85.4k
24 votes

Why does a subquery reduce the row estimate to 1?

This definitely seems like unintended behavior. It is true that cardinality estimates do not need to be consistent at each step of a plan but this is a relatively simple query plan and the final ...
Joe Obbish's user avatar
  • 32.2k
24 votes
Accepted

Why does a subquery reduce the row estimate to 1?

This cardinality estimation (CE) issue surfaces when: The join is an outer join with a pass-through predicate The selectivity of the pass-through predicate is estimated to be exactly 1. Note: The ...
Paul White's user avatar
  • 85.4k
24 votes
Accepted

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

This part of the plan is the problem. Issue The correct behaviour if the subquery brings back any NULL is to return 0 rows from the NOT IN. Even if ID is not nullable (and therefore MIN(ID) cannot ...
Martin Smith's user avatar
  • 84.8k
21 votes
Accepted

Why does Concatenation operator estimate fewer rows than its inputs?

To quote Campbell Fraser on this Connect item: These "cardinality inconsistencies" can arise in a number of situations, including when concat is used. They can arise because the estimation of a ...
Paul White's user avatar
  • 85.4k
17 votes
Accepted

Why is this join cardinality estimate so large?

I know that doing COALESCE on a few columns and joining on them isn't a good practice. Generating good cardinality and distribution estimates is hard enough when the schema is 3NF+ (with keys and ...
Paul White's user avatar
  • 85.4k
16 votes

Cardinality Estimate for LIKE operator (Local Variables)

I tested on SQL Server 2014 with the legacy CE and did not get 9% as a cardinality estimate either. I couldn't find anything accurate online so I did some testing and I found a model that fits all of ...
Joe Obbish's user avatar
  • 32.2k
14 votes
Accepted

Cardinality Estimation for >= and > for intra step statistics value

The only difficulty is in deciding how to handle the histogram step(s) partially covered by the query predicate interval. Whole histogram steps covered by the predicate range are trivial as noted in ...
Paul White's user avatar
  • 85.4k
14 votes

Rebuilding indexes for a partitioned table having 300 partitions

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am ...
Paul White's user avatar
  • 85.4k
14 votes
Accepted

Sort spills to tempdb but estimated rows equals to actual rows

Enable trace flag 7470. FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct As I wrote in answer to Query Plan question: ...
Paul White's user avatar
  • 85.4k
14 votes
Accepted

Сardinality estimation of partially covering range predicates

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step ...
Paul White's user avatar
  • 85.4k
13 votes
Accepted

Cardinality estimate outside the histogram

Based on my testing, the out-of-bounds cardinality estimate is simply the square root of the row count, bounded below by the number of added rows since the last stats update, and bounded above by the ...
Forrest's user avatar
  • 4,169
13 votes
Accepted

How does the optimizer treat yet-unpopulated #temp tables when estimating cardinality?

SQL Server generally builds a complete set of execution plans for the statements in a stored procedure before the procedure begins executing. That approach is slightly modified when temporary tables ...
Paul White's user avatar
  • 85.4k
13 votes

Estimations seem too low/inaccurate for nested loops operator

General consistency SQL Server does not make any general guarantees about the consistency of estimates in execution plans. The estimates do start off being consistent because cardinality estimation is ...
Paul White's user avatar
  • 85.4k
12 votes

Scalar function alters estimate for preceding operator

It's a consequence of a post-optimization rewrite that pushes suitable predicates down into a seek or scan operator as a residual predicate. The optimizer's estimates are based on a tree before this ...
Paul White's user avatar
  • 85.4k
11 votes
Accepted

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

The implicit conversions are caused by the computed column AltKey: CREATE TABLE dbo.Test ( [sessionid] [decimal](18, 0) NOT NULL, [sessionseqnum] [smallint] NOT NULL, [nodeid] [smallint] ...
Paul White's user avatar
  • 85.4k
11 votes
Accepted

Apply cardinality estimation problem in SQL Server

There are often several ways to derive a cardinality estimate, with each method giving a different (but equally valid) answer. That is simply the nature of statistics and estimations. You ask ...
Paul White's user avatar
  • 85.4k
10 votes

Why is this join cardinality estimate so large?

I believe the Compute Scalar operator resulting from the COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) being joined to X_LAST_TABLE.JOIN_ID is the root cause of the issue. Historically, compute ...
Hannah Vernon's user avatar
  • 70.1k
10 votes
Accepted

How SQL server generates a Query plan with Auto Create Statistics set to OFF

Table variables is another story, what the server uses when there are no statistics is estimation for unknown input: So in your case the estimation will be select power(1000000, 0.75) = 31622 ( = ...
sepupic's user avatar
  • 11k
10 votes

Cardinality estimation problem on inner join

Based on your histograms I was able to repro the issue in 2017 CU6. I wouldn't say that you're doing something wrong. Rather, something is going wrong with cardinality estimation. Here's what I get ...
Joe Obbish's user avatar
  • 32.2k
10 votes
Accepted

Sort spills to tempdb due to varchar(max)

There are going to be several possible workarounds here. You can manually adjust the memory grant, though I probably wouldn't go that route. You can also use a CTE and TOP to push the sort lower, ...
Forrest's user avatar
  • 4,169
10 votes
Accepted

When do _WA_Sys_ statistics Get Updated?

SQL Server only automatically updates the statistics it uses. It may not be actively using the _WA_Sys statistics for cardinality estimation, though it may load them as part of the decision. To see ...
Erik Darling's user avatar
10 votes
Accepted

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

The reason there is no "type conversion / cardinality estimate" warning in this query: SELECT LEFT(30500600, 2); ...is because there is no cardinality estimate. It's a constant expression being ...
Josh Darnell's user avatar
  • 29.3k
10 votes
Accepted

No IMPLICIT_CONVERSION warning in this query plan

Your parameters have a lower Data Type Precedence than the columns they are compared to. In this case the parameter values will be implicitly converted. This is a good thing. Converting the column ...
David Browne - Microsoft's user avatar
10 votes
Accepted

Inserting with implicit type conversion causes warning for cardinality estimates

Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about ...
Paul White's user avatar
  • 85.4k
10 votes
Accepted

Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan

A number of factors in play: The index comes with full scan statistics. The auto-created ones were sampled. Different cardinality estimation models and execution modes handle the calculation ...
Paul White's user avatar
  • 85.4k
9 votes
Accepted

Why would the Parallelism (Repartition Streams) Operator Reduce Row Estimates to 1?

Query plans with bitmap filters can sometimes be tricky to read. From the BOL article for repartition streams (emphasis mine): The Repartition Streams operator consumes multiple streams and ...
Joe Obbish's user avatar
  • 32.2k
9 votes
Accepted

Wrong estimate on a query on partitioned tables

The estimates (with the new cardinality estimator) are fine for a normal join, but are less accurate when the optimizer considers the option of a colocated join. A colocated join (aka per-partition ...
Paul White's user avatar
  • 85.4k
9 votes
Accepted

Compatibility level of SQL Server

To know the current compatibility_level of your database you can run: SELECT name,compatibility_level FROM sys.databases WHERE name = 'DatabaseName'; Result for my TEST database: name ...
Randi Vertongen's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible