Stack Exchange Network

Stack Exchange network consists of 175 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]

-3
votes
1answer
66 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
102 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
269 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
354 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
485 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
475 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
189 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
143 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
59 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
156 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
318 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 ...
7
votes
0answers
257 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
128 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
772 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
64 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
87 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
656 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
128 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
251 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
332 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
77 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
781 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
717 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
136 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
335 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
136 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
42 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
726 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
906 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
236 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
124 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
618 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
286 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
471 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
44 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
578 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
411 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
159 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 ...
-1
votes
1answer
314 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 ...
2
votes
1answer
75 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 ...
13
votes
1answer
295 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 ...