Mrthfx

The probability of reaching the absorbing states from a particular transient state?

Can 5 Aarakocra PCs summon an Air Elemental?

Which RAF squadrons and aircraft types took part in the bombing of Berlin on the 25th of August 1940?

Am I correct in stating that the study of topology is purely theoretical?

Existence of Riemann surface, holomorphic maps

What game did these black and yellow dice come from?

Can the "Friends" spell be used without making the target hostile?

How do you funnel food off a cutting board?

How can I play a serial killer in a party of good PCs?

Why maximum length of IP, TCP, UDP packet is not suit?

Subsurf on a crown. How can I smooth some edges and keep others sharp?

Has any human ever had the choice to leave Earth permanently?

Is a new boolean field better than null reference when a value can be meaningfully absent?

Website seeing my Facebook data?

Why do all the books in Game of Thrones library have their covers facing the back of the shelf?

Prioritising polygons in QGIS

How do you get out of your own psychology to write characters?

Why do neural networks need so many training examples to perform?

When obtaining gender reassignment/plastic surgery overseas, is an emergency travel document required to return home?

In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?

"Starve to death" Vs. "Starve to the point of death"

Does Skippy chunky peanut butter contain trans fat?

Sprint is 2 week and 40-stories

Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

T-SQL query using completely different plan depending on number of rows I'm updatingSQL Server Index Scan Actual ExecutionsActual Number of rows is too high even with new tablesDespite STATISTICS is updated, Estimated and Actual Row count is not sameSQL server 2012 inner join estimated number of rows issueWrong estimated number of rowsWrong no of actual rows and why did statistics update help here?With OPTION RECOMPILE vs without OPTION RECOMPILEWhy does using Format vs Right to apply padding cause estimated number of rows to dramatically change?Sort spills to tempdb but estimated rows equals to actual rows

2

I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.

I currently have the following result in the execution plan for a very simple SELECT query:

As you can see it is off by 5 rows.

I then run:

UPDATE STATISTICS  Person.Address WITH FULLSCAN

UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN

GO

EXEC sp_recompile 'Person.Address';

GO

SELECT * FROM Person.Address OPTION(RECOMPILE)

However, it is still off by 5 rows.
Why?

I know I shouldn't worry unless there is a performance problem. However, I am trying to understand the actual effect of a complete statistics update

sql-server sql-server-2017

share|improve this question

edited 1 hour ago

k29

asked 1 hour ago

k29k29

12516

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have a look at the Details tab of PK_Address_AddressID statistics properties, and attach printscreen to your question if it is possible. That information must be helpful to find a correct answer.
  
  – Denis Rubashkin
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this the estimated plan or the actual execution plan?
  
  – Alen
  37 mins ago
  

  
  
  
  

add a comment | 

2

I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.

I currently have the following result in the execution plan for a very simple SELECT query:

As you can see it is off by 5 rows.

I then run:

UPDATE STATISTICS  Person.Address WITH FULLSCAN

UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN

GO

EXEC sp_recompile 'Person.Address';

GO

SELECT * FROM Person.Address OPTION(RECOMPILE)

However, it is still off by 5 rows.
Why?

I know I shouldn't worry unless there is a performance problem. However, I am trying to understand the actual effect of a complete statistics update

sql-server sql-server-2017

share|improve this question

edited 1 hour ago

k29

asked 1 hour ago

k29k29

12516

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have a look at the Details tab of PK_Address_AddressID statistics properties, and attach printscreen to your question if it is possible. That information must be helpful to find a correct answer.
  
  – Denis Rubashkin
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this the estimated plan or the actual execution plan?
  
  – Alen
  37 mins ago
  

  
  
  
  

add a comment | 

I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.

I currently have the following result in the execution plan for a very simple SELECT query:

As you can see it is off by 5 rows.

I then run:

UPDATE STATISTICS  Person.Address WITH FULLSCAN

UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN

GO

EXEC sp_recompile 'Person.Address';

GO

SELECT * FROM Person.Address OPTION(RECOMPILE)

However, it is still off by 5 rows.
Why?

I know I shouldn't worry unless there is a performance problem. However, I am trying to understand the actual effect of a complete statistics update

sql-server sql-server-2017

share|improve this question

edited 1 hour ago

k29

asked 1 hour ago

k29k29

12516

UPDATE STATISTICS  Person.Address WITH FULLSCAN

UPDATE STATISTICS  Person.Address [PK_Address_AddressID] WITH FULLSCAN

GO

EXEC sp_recompile 'Person.Address';

GO

SELECT * FROM Person.Address OPTION(RECOMPILE)

share|improve this question

edited 1 hour ago

k29

asked 1 hour ago

k29k29

12516

share|improve this question

edited 1 hour ago

k29

asked 1 hour ago

k29k29

12516

asked 1 hour ago

k29k29

12516

asked 1 hour ago

k29k29

12516

1 Answer
1

active

oldest

votes

4

Row count appears to only hold 6 digits of information (I'm sure there's a technical term for this, but it escapes me for the moment).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB

GO



DROP TABLE IF EXISTS dbo.stattest

GO

CREATE TABLE dbo.stattest (ID int primary key, junk char(1))



INSERT dbo.stattest

SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'

FROM master..spt_values a

CROSS JOIN master..spt_values b

CROSS JOIN master..spt_values c



SELECT COUNT(*)

FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN

GO

SELECT *

FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)

FROM dbo.stattest

OPTION(

QUERYTRACEON 3604,

QUERYTRACEON 2363

)

CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)

share|improve this answer

edited 1 hour ago

answered 1 hour ago

ForrestForrest

2,2211719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for editing in that Query Trace so I can see the Cardinality Estimate rounding! That is exactly what is going on in my query
  
  – k29
  59 mins ago
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230765%2fupdating-statistics-estimated-number-of-rows-not-equal-to-actual-for-index-scan%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

4

Row count appears to only hold 6 digits of information (I'm sure there's a technical term for this, but it escapes me for the moment).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB

GO



DROP TABLE IF EXISTS dbo.stattest

GO

CREATE TABLE dbo.stattest (ID int primary key, junk char(1))



INSERT dbo.stattest

SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'

FROM master..spt_values a

CROSS JOIN master..spt_values b

CROSS JOIN master..spt_values c



SELECT COUNT(*)

FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN

GO

SELECT *

FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)

FROM dbo.stattest

OPTION(

QUERYTRACEON 3604,

QUERYTRACEON 2363

)

CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)

share|improve this answer

edited 1 hour ago

answered 1 hour ago

ForrestForrest

2,2211719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for editing in that Query Trace so I can see the Cardinality Estimate rounding! That is exactly what is going on in my query
  
  – k29
  59 mins ago
  
  
  

  
  
  
  

add a comment | 

4

Row count appears to only hold 6 digits of information (I'm sure there's a technical term for this, but it escapes me for the moment).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB

GO



DROP TABLE IF EXISTS dbo.stattest

GO

CREATE TABLE dbo.stattest (ID int primary key, junk char(1))



INSERT dbo.stattest

SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'

FROM master..spt_values a

CROSS JOIN master..spt_values b

CROSS JOIN master..spt_values c



SELECT COUNT(*)

FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN

GO

SELECT *

FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)

FROM dbo.stattest

OPTION(

QUERYTRACEON 3604,

QUERYTRACEON 2363

)

CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)

share|improve this answer

edited 1 hour ago

answered 1 hour ago

ForrestForrest

2,2211719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for editing in that Query Trace so I can see the Cardinality Estimate rounding! That is exactly what is going on in my query
  
  – k29
  59 mins ago
  
  
  

  
  
  
  

add a comment | 

Row count appears to only hold 6 digits of information (I'm sure there's a technical term for this, but it escapes me for the moment).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB

GO



DROP TABLE IF EXISTS dbo.stattest

GO

CREATE TABLE dbo.stattest (ID int primary key, junk char(1))



INSERT dbo.stattest

SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'

FROM master..spt_values a

CROSS JOIN master..spt_values b

CROSS JOIN master..spt_values c



SELECT COUNT(*)

FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN

GO

SELECT *

FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)

FROM dbo.stattest

OPTION(

QUERYTRACEON 3604,

QUERYTRACEON 2363

)

CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)

share|improve this answer

edited 1 hour ago

answered 1 hour ago

ForrestForrest

2,2211719

USE TestDB

GO



DROP TABLE IF EXISTS dbo.stattest

GO

CREATE TABLE dbo.stattest (ID int primary key, junk char(1))



INSERT dbo.stattest

SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'

FROM master..spt_values a

CROSS JOIN master..spt_values b

CROSS JOIN master..spt_values c



SELECT COUNT(*)

FROM dbo.stattest

UPDATE STATISTICS dbo.stattest WITH FULLSCAN

GO

SELECT *

FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

SELECT COUNT(*)

FROM dbo.stattest

OPTION(

QUERYTRACEON 3604,

QUERYTRACEON 2363

)

share|improve this answer

edited 1 hour ago

answered 1 hour ago

ForrestForrest

2,2211719

answered 1 hour ago

ForrestForrest

2,2211719

answered 1 hour ago

ForrestForrest

2,2211719