Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
1
vote
0answers
19 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
72 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
122 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 ...
-3
votes
1answer
71 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
111 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 ...
6
votes
1answer
280 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 ...
11
votes
2answers
1k 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 ...
14
votes
1answer
374 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 ...
5
votes
2answers
491 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 ...
10
votes
3answers
483 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] [...
9
votes
2answers
206 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 ...
7
votes
2answers
165 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
98 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
182 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
356 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
43 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 ...
7
votes
0answers
268 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
137 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
135 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
65 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
847 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
31 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
65 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
95 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
730 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
147 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
1k 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
253 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
359 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
80 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
838 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
793 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
140 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
340 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
142 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 ...
12
votes
3answers
3k 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
43 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
754 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
963 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
244 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 ...
2
votes
1answer
127 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 ...
4
votes
1answer
658 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 ...
2
votes
1answer
308 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 ...
2
votes
2answers
497 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
45 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
631 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
1k 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
431 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
173 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 ...
24
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 ...