Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
5
votes
2answers
565 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
580 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] [...
7
votes
2answers
313 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 ...
3
votes
1answer
1k 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 ...
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....
1
vote
1answer
369 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
799 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
47 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 ...
2
votes
3answers
140 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
188 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
71 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
2k 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 ...
5
votes
2answers
1k views

Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued ...
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
93 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
331 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 ...
0
votes
0answers
270 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
2k 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
298 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 ...
9
votes
3answers
1k 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
1k 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 ...
13
votes
3answers
4k 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 ...
1
vote
1answer
166 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
359 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
167 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 ...
26
votes
2answers
1k 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 ...
12
votes
3answers
6k 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
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() ...
2
votes
1answer
1k 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,CLIENT_ID_6,...
20
votes
2answers
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 ...
16
votes
1answer
614 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 ...
5
votes
2answers
1k 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 ...
10
votes
2answers
460 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 ...
6
votes
1answer
277 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
151 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 ...
5
votes
1answer
917 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
2answers
695 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
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 ...
6
votes
2answers
539 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
2answers
1k 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(...
0
votes
1answer
285 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 ...
13
votes
1answer
353 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 ...
-1
votes
1answer
382 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 ...
5
votes
1answer
550 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),...
2
votes
1answer
76 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 ...
3
votes
2answers
1k 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 ...
7
votes
1answer
147 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 ...
20
votes
2answers
759 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 ...
3
votes
1answer
744 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 ...
4
votes
1answer
1k views

Alternative for WHERE clause in SQL query

We have SQL Server 2012 Enterprise Edition running following query via a stored procedure: declare @TopX int = 1000 declare @stores Table (Store varchar(5), LastDate datetime, LastId int, RangeEnd ...