Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
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 ...
13
votes
1answer
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....
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 ...
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 ...
10
votes
2answers
455 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 ...
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 ...
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 ...
10
votes
3answers
573 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] [...
6
votes
1answer
358 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, ...
6
votes
1answer
434 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 ...
3
votes
1answer
741 views

Estimated rows very wrong when using declared variables

I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723): Declare @StartDate as date = '10/1/2014'; Declare @EndDate as ...
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 ...
9
votes
1answer
632 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 ...
5
votes
1answer
549 views

SQL server 2012 inner join estimated number of rows issue

I have a simple join like the one below: select * from fact_sales f join dim_company d on f.company_SK = d.company_SK The fact table contains a little over 500 million records (with NC columnstore),...
3
votes
1answer
141 views

PostgreSQL + query planner + amount of entities

I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan. I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
3
votes
1answer
82 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 ...
2
votes
2answers
3k views

How to improve row estimate of 1 row in join for newly inserted data

A custom statistic exists for the CacheId column of a table. After an overnight statistics update: Statistics for INDEX 'ST_TableName_CacheId'. -------------------------------------------------------...
1
vote
1answer
194 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 ...
1
vote
1answer
47 views

Understanding Sort Estimates with TOP(variable)

I have the below query and I am trying to understand the sort estimate when I use TOP with a variable. PLAN create table justint ( c1 int ) insert into justint select top 1000000 row_number() ...
1
vote
0answers
53 views

DateTime to Format: Implicit Conversion [duplicate]

I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used: CONVERT(char(26), c1.DateTime, 109) Which works successfully, but at least on SQL Server ...