Skip to main content

Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
28 votes
1 answer
850 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 ...
Geoff Patterson's user avatar
26 votes
2 answers
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 ...
Joe Obbish's user avatar
  • 32.5k
26 votes
2 answers
2k views

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is ...
Geoff Patterson's user avatar
25 votes
2 answers
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 ...
Fza's user avatar
  • 652
20 votes
2 answers
893 views

Why does Concatenation operator estimate fewer rows than its inputs?

In the following query plan snippet, it seems obvious that the row estimate for the Concatenation operator should be ~4.3 billion rows, or the sum of the row estimates for its two inputs. However, an ...
Geoff Patterson's user avatar
20 votes
2 answers
2k views

Why is this join cardinality estimate so large?

I am experiencing what I think is an impossibly high cardinality estimate for the following query: SELECT dm.PRIMARY_ID FROM ( SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID ...
Joe Obbish's user avatar
  • 32.5k
20 votes
1 answer
15k 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 ...
Mikael Eriksson's user avatar
16 votes
1 answer
655 views

SQL Server 2014 COUNT(DISTINCT x) ignores statistics density vector for column x

For a COUNT(DISTINCT) that has ~1 billion distinct values, I'm getting a query plan with a hash aggregate estimated to have only ~3 million rows. Why is this happening? SQL Server 2012 produces a ...
Geoff Patterson's user avatar
15 votes
2 answers
3k views

Cardinality estimation problem on inner join

I'm struggling to understand why row estimation is so terribly wrong, here is my case: Simple join - using SQL Server 2016 sp2 (same issue on sp1), dbcompatiblity=130. select ...
LeMaciek's user avatar
  • 231
15 votes
1 answer
765 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 ...
Frederik Vanderhaegen's user avatar
15 votes
1 answer
574 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 ...
Josh Darnell's user avatar
  • 29.4k
14 votes
3 answers
6k views

Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?

I have a query that runs in 800 milliseconds in SQL Server 2012 and takes about 170 seconds in SQL Server 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count ...
Geoff Patterson's user avatar
13 votes
1 answer
3k views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN Sales....
8kb's user avatar
  • 2,629
13 votes
2 answers
915 views

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

I am trying to understand how SQL Server try to estimate for 'greater than' and 'greater than equal to' where clauses in SQL Server 2014. I think I do understand the cardinality estimation when it ...
DMDM's user avatar
  • 1,750
13 votes
1 answer
298 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-...
Павел Ковалёв's user avatar
13 votes
1 answer
409 views

Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?

Is there any documentation or research about changes in SQL Server 2016 to how cardinality is estimated for predicates containing SUBSTRING() or other string functions? The reason I'm asking is that ...
James Lupolt's user avatar
  • 4,258
12 votes
2 answers
3k 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 ...
xMRi's user avatar
  • 230
12 votes
1 answer
30k 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 ...
Oxon's user avatar
  • 223
12 votes
1 answer
14k 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 ...
Iain Samuel McLean Elder's user avatar
12 votes
3 answers
12k views

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

I'm using SQL Server 2012 Enterprise. I've come across a SQL Plan that is exhibiting some behavior I don't find entirely intuitive. After a heavy Parallel Index Scan operation, a Parallelism (...
John Eisbrener's user avatar
11 votes
2 answers
1k 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 ...
Voysinmyhead's user avatar
11 votes
1 answer
1k 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 ...
Павел Ковалёв's user avatar
10 votes
3 answers
884 views

Sort spills to tempdb due to varchar(max)

On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables: CREATE TABLE [dbo].[Settings]( [id] [...
Frederik Vanderhaegen's user avatar
10 votes
1 answer
10k 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 ...
arthur's user avatar
  • 888
10 votes
1 answer
712 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 ...
Yannick Motton's user avatar
9 votes
2 answers
804 views

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

I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables. If I have 4 joins to a #temp table where that #temp table is created DURING ...
Mark Adam Proctor's user avatar
9 votes
1 answer
851 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 ...
Justin Grant's user avatar
9 votes
3 answers
2k views

How to hint many-to-many join in SQL Server?

I have 3 "large" tables that join on a pair of columns (both ints). Table1 has ~200 million rows Table2 has ~1.5 million rows Table3 has ~6 million rows Each table has a clustered index on Key1, ...
Steven Hibble's user avatar
8 votes
3 answers
4k 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 ...
Artashes  Khachatryan's user avatar
8 votes
2 answers
10k 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 ...
gbeaven's user avatar
  • 213
8 votes
1 answer
1k 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 ...
Dan Oberlam's user avatar
8 votes
1 answer
256 views

Why does SQL Server estimate less rows will be emitted from a join after inserting some rows?

The below is a simplified version of something I came across in production (where the plan got catastrophically worse on a day where an unusually high number of batches were processed). The repro ...
Martin Smith's user avatar
  • 84.9k
8 votes
2 answers
948 views

SQL Server not Using Index

I am absolutely stumped as to why my query is not using what I think is a selective index. My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many ...
Joshua Grippo's user avatar
7 votes
2 answers
715 views

Wrong estimate on a query on partitioned tables

I wonder why SQL Server makes wrong estimations in such a simple case. There is a scenario. CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT FOR VALUES (20140801, 20140802, 20140803) CREATE ...
Alsin's user avatar
  • 375
7 votes
1 answer
2k 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 ...
SE1986's user avatar
  • 1,990
7 votes
1 answer
207 views

very poor cardinality estimate on a geometry intersection

I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results ...
Andrew Hill's user avatar
6 votes
1 answer
3k views

Rebuilding indexes for a partitioned table having 300 partitions

Scenario Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded. Issue While ...
Rsola's user avatar
  • 73
6 votes
1 answer
657 views

Estimations seem too low/inaccurate for nested loops operator

This question is a follow-up to somebody else's question: Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why? My question is ...
Kuba Wyrostek's user avatar
6 votes
1 answer
3k views

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

Given the following simple query on the StackOverflow2010 database: SELECT u.DisplayName, u.Reputation FROM Users u JOIN Posts p ON u.id = p.OwnerUserId WHERE u....
SE1986's user avatar
  • 1,990
6 votes
1 answer
398 views

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

I am wondering how SQL server can generate a query plan with "Auto create statistics" set to "off". I stumbled on the below context at the below link while searching for an answer(Sorry, I searched, ...
Midhun C N's user avatar
6 votes
1 answer
4k 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 ...
Marcello Miorelli's user avatar
6 votes
1 answer
466 views

Why is an additional filtered statistic being ignored (EAV schema)?

I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values ...
crokusek's user avatar
  • 2,120
6 votes
2 answers
355 views

What are thresholds in SQL Server 2014 "big table join to small table" cardinality estimation optimization?

The SQL Server 2014 Cardinality Estimator white paper says: The new CE, however, uses a simpler algorithm that assumes that there is a one-to-many join association between a large table and a ...
Justin Grant's user avatar
6 votes
2 answers
804 views

How do I effectively handle very skewed data? Statistics up to date but don't seem to help

I have a table, dbo.ClaimBilling, with 130,000 rows. In this table, the column OperatorID is a varchar(max) and is heavily skewed. 125,000 rows are 'user1', and the remaining 5000 rows are split ...
Zaphodb2002's user avatar
6 votes
1 answer
328 views

Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.?

The new cardinality estimator (CE) in SQL Server 2016 has an unfortunate regression which hits our deployment hard. The query in question is a run-of-the-mill Hibernate generated update; however the ...
user avatar
5 votes
2 answers
1k views

Compatibility level of SQL Server

Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
user avatar
5 votes
1 answer
715 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', @...
George.Palacios's user avatar
5 votes
2 answers
2k views

Update statistics on sys.objects

I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on. However, this question in ...
Marcello Miorelli's user avatar
5 votes
1 answer
1k views

Stream Aggregate for Sort?

Trying to understand Couple of things in the above query plan. Why is there a Stream Aggregate in the query when I dont have a group by. I am guess it has something to do with the join being merge ...
DMDM's user avatar
  • 1,750
5 votes
2 answers
855 views

Nested Loop has very low estimates due to skewed data

On SQL Server 2016 SP2 we have a query that has a very low estimate on the nested loop operator. Due to the low estimate this query also spills to tempdb. If I'm correct SQL Server 2014+ uses Coarse ...
Frederik Vanderhaegen's user avatar