Questions tagged [cardinality-estimates]

Filter by
Sorted by
Tagged with
0
votes
2answers
72 views

Cardinality , Parallel Hint alternative for MS-SQL

UPDATE MARK M SET ARCHIVE_FLAG = 'N' WHERE EXISTS (SELECT /*+ cardinality(S1, 10) parallel(S1,8)*/ 1 FROM SHFASG S, SHIFT S1 WHERE S.ID = S1.ID AND M.ID = S.MARKID AND ARCHIVE_FLAG <> 'Y'); ...
1
vote
2answers
78 views

Postgres prefers slow Seq Scan over fast Index Scan

Tables I have the following two tables. books, which contains ~5 million rows: Table "public.books" Column | Type | ...
3
votes
2answers
218 views

SQL Server Cardinality Estimation Warning [duplicate]

How can I fix the following cardinality estimation warning? while exists(select 1 from @images) begin declare @imageid int declare @filename varchar(max) select TOP (1) @imageid =...
1
vote
2answers
160 views

Preventative measures to avoid wrong cardinality estimate statistics

This is a follow-up to Queries randomly getting slow, good preventive practices?, attempting to ask a more specific question about one of the ideas stated there. A common cause of queries suddenly ...
0
votes
2answers
64 views

MariaDB 10.4 Query Optimizer Wrong Execution Plan

I have a very strange behavior with some tables and the query optimizer on a MariaDB server. First of all we have our main table let's call it huge and we have on the same database another table a ...
1
vote
1answer
87 views

Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

The Backstory: I have a table with the following structure: CREATE TABLE WideTable1 ( BoringColumn1, BoringColumn2, CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
0
votes
2answers
77 views

Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?

I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
0
votes
1answer
89 views

Legacy CE vs current

I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE: But 5 minutes with CE 150: https://www....
3
votes
1answer
111 views

What Method / Formula does a Nested Loops Operator use for row estimation?

The following, simple query in AdventureWorks: SELECT * FROM Person.Person p JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID Gives the following ...
0
votes
1answer
39 views

How to improve the estimated cardinality of inner join lateral

I'm currently running a query that has the following structure: select v1.*, v2.*, ... from t1 inner join lateral f1(t1.id1, t1.id2) as v1 on true inner join lateral f2(t1.id1, t1.id2) ...
2
votes
1answer
117 views

Index or query tuning for complex predicates

I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates: JOIN on a date from one table being in range - between two date ...
3
votes
1answer
119 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 ...
1
vote
0answers
34 views

How statistics play a major role in legacy CE vs new CE

We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100. Yes i am aware of few hints to make use of legacy CE and have implemented as success. ...
0
votes
1answer
26 views

Bad estimates when sorting, good estimates without sort

I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2. I needed to anonymize the query due to security policies. The query is like this: SELECT TOP 1000 Object1....
3
votes
2answers
159 views

Changing database compatibility from legacy CE to 120

Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008) From DEV point of ...
0
votes
0answers
24 views

Why do tempdb spills still occur even with good row and data size estimates (better than actuals)?

We're seeing tempdb DB spills for some hashing operations. If the estimates are indeed good as shown what would be the next thing(s) to look for? Looking for a generic answer without having to ...
10
votes
1answer
348 views

Apply cardinality estimation problem in SQL Server

Now, I am faced with the problem of the logic of cardinality estimation that is not quite clear for me in a seemingly rather simple situation. I encountered this situation at my work, therefore, for ...
1
vote
0answers
68 views

Incorrect Estimates (CE Model 130) on uniqueidentifier columns

We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns. The select is very ...
0
votes
1answer
80 views

How do cardinality estimates affect CPU and Reads in SQL Server?

Trying to understand a concept, not sure where to look for answers. I have been researching cardinality estimates and their affects on memory. In the process of optimizing a query to get better ...
5
votes
2answers
896 views

Compatibility level of SQL Server

Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
0
votes
1answer
39 views

What makes for a good index in this scenario?

I have a pretty big temp table (~5 million rows) that I need to update a few fields on. Here's an example update statement: UPDATE T SET CarModel = CASE WHEN CarModel = '350z' AND ...
2
votes
1answer
419 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 ...
13
votes
1answer
215 views

Сardinality estimation of partially covering range predicates

At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step. On the Internet, at cardinality-estimation-for-and-for-...
8
votes
1answer
388 views

Inserting with implicit type conversion causes warning for cardinality estimates

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning: Type ...
3
votes
1answer
252 views

Why can't SQL Server optimizer use foreign key [duplicate]

I have the following tables(See below for complete reproducible SQL): ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...) CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...) ...
15
votes
1answer
456 views

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
1
vote
0answers
54 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
272 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
133 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
191 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
121 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 ...
5
votes
2answers
5k 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 ...
7
votes
1answer
753 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
2k 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 ...
15
votes
1answer
536 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
588 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
607 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] [...
11
votes
2answers
514 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
353 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
2answers
1k 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
417 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 ...
1
vote
0answers
122 views

Converting an XML field to nvarchar causes warning on main SELECT in explain plan

I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following: SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p This ...
6
votes
1answer
1k 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
48 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 ...
10
votes
1answer
528 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 ...
5
votes
1answer
542 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
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
197 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 ...