Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [cardinality-estimates]

8
votes
3answers
348 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] [...
8
votes
2answers
129 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 ...
6
votes
2answers
98 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 ...
1
vote
1answer
26 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 ...
1
vote
1answer
90 views

Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON`

I am using SQL server 2014, but my database version is SQL server 2012. So I am trying to use trace flag 2312 to force the query optimizer to use cardinality estimator v120. I have noticed through my ...
6
votes
1answer
180 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 ...
1
vote
0answers
41 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 ...
6
votes
0answers
211 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 ...
2
votes
3answers
135 views

Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?

I was working on a query at work, that had a left join like cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x The actual execution plan for this query was fairly close, est ...
1
vote
2answers
105 views

Improving statistics on a large “WHERE IN” SQL Server query

Currently, I'm trying to run the following example query: SELECT [DATA1], [DATA2] FROM TABLE WHERE [DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND [DIMENSION1] IN (5) AND [...
-1
votes
1answer
62 views

Estimation failure in Stored Procedure execution plan

I have the following two tables in different databases: CREATE TABLE [dbo].[UnitPermission] ( [userID] [varchar](10) NOT NULL, [unitID] [nvarchar](10) NOT NULL, CONSTRAINT [...
13
votes
2answers
563 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 ...
0
votes
1answer
30 views

Relations and Sets

From my understanding, One = {1,2,3,4,5...20}, and then if Two = {1}, then One won't map into Two. On that token, how come the answer is d instead of a?
0
votes
1answer
59 views

Implement a two sided zero to many relationship?

How does one implement the following relationship? One car may belong to zero or many persons One person may own zero or many cars Ideally I could have a junction table that has a zero to many ...
3
votes
1answer
71 views

SQL Server 2016 Compatibility Level and Datetime 2

We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but ...
2
votes
1answer
433 views

Estimated Rows Higher than Actual Despite Updating Statistics

I have a (anonymised) query plan There is a table (Object8) which has a high variation on the number of Actual Rows vs Estimated Rows on two columns (Column2 and Column6) which can be seen in the ...
0
votes
0answers
81 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/...
1
vote
1answer
633 views

Cross-database queries are slow

I have two databases/catalogs on the same SQL server and an SQL-user with access to both of them. Collation on Database01 is Danish_Norwegian_CI_AS Collation on Database02 is ...
6
votes
2answers
239 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 ...
4
votes
1answer
250 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 ...
0
votes
1answer
67 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 ...
9
votes
3answers
617 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, ...
5
votes
2answers
546 views

Statistics and row estimation

Hope someone can help me to learn how SQL Server uses stats to estimate the number of records. Test Scripts USE [tempdb] GO CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, c INT CHECK (c ...
1
vote
1answer
109 views

Estimated number of rows is inaccurate

I have a table with a field that has a very large variance on a particular field that is often searched. The number of records associated with any value in the field highly differs. My estimated ...
6
votes
1answer
316 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, ...
7
votes
1answer
130 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 ...
11
votes
3answers
2k 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 (...
1
vote
1answer
38 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() ...
24
votes
2answers
646 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 ...
5
votes
2answers
723 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 ...
6
votes
1answer
221 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 ...
3
votes
1answer
106 views

Estimated vs. Actual Row counts in a trivial case

I'm working on optimizing some queries in a database I've inherited. I'm not permitted to disclose the query, but I can present an anonymized version of a query plan that is showing some very strange ...
3
votes
1answer
482 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 ...
1
vote
1answer
246 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
2answers
411 views

Query slower after upgrade from sql server 2008R2 to 2014sp2

I recently upgraded from SQL Server 2008R2 to 2014SP2 and upgraded the databases to the compatiblity level 120. This query generates a better execution plan with the old cardinality estimator (...
1
vote
0answers
43 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 ...
0
votes
2answers
450 views

Slow query - big difference between estimated and actual rows

At my work, we have a problem with a search query. If I don't include the lookup into Table_39 (see below query), it takes 0 seconds to execute the query. If I include the lookup (..AND (Not Exists(...
16
votes
3answers
994 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 ...
6
votes
2answers
342 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 ...
1
vote
1answer
131 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 ...
25
votes
2answers
2k views

SQL Server - 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 ...
-1
votes
1answer
279 views

Bad estimations in execution plan [closed]

I have a weird problem in regards of cardinal estimation in SQL Server 2012 (11.0.2218). All statistics are updated using sp_updatestats but some operators display just 1 estimated row, even if ...
3
votes
1answer
71 views

Query with Variable not estimating right

I have a query with several joins that is not performing well. After a lot of triage, I decided to take the query apart and look at just the driving table. Below is an example of the query based on ...
14
votes
1answer
288 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 ...
5
votes
1answer
396 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),...
1
vote
1answer
735 views

Despite STATISTICS is updated, Estimated and Actual Row count is not same

I am executing a SELECT statement to retrieve 2 columns with filter on datetime column from a table having 27451373 rows. declare @LastProcessedDate datetime='08-15-2016' select Bill_Code,...
8
votes
2answers
286 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 ...
0
votes
1answer
166 views

How Sql server 2012 join cardinality is calculated?

We have data warehouse based on SQL Server 2012. Often we get poor cardinality estimates for joins impacting downstream operators and slow execution, memory spills, etc. Is there a good article or ...
2
votes
2answers
542 views

Low cardinality estimate in hash match operator

I am trying to fix a performance problem with one of our reporting queries on SQL Server 2008 R2. I have included the part of the query that is causing low estimate. This part is further joined with ...
26
votes
1answer
593 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 ...