Ggthjy

How would an AI self awareness kill switch work?

Cookies - Should the toggles be on?

Explain the objections to these measures against human trafficking

How to prevent cleaner from hanging my lock screen in Ubuntu 16.04

Can I write a book of my D&D game?

How to explain planetary rings pulsating?

Why is "points exist" not an axiom in geometry?

Can we use the stored gravitational potential energy of a building to produce power?

Program that converts a number to a letter of the alphabet

Would these multi-classing house rules cause unintended problems?

Avoiding morning and evening handshakes

How to avoid Replace substituting subscripts?

Broken patches on a road

Why does lambda auto& parameter choose const overload?

Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?

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

Calculate Contact age in a Drupal view

Are there any modern advantages of a fire piston?

Strange Sign on Lab Door

Can a hotel cancel a confirmed reservation?

Typing Amharic inside a math equation?

Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?

Injecting creativity into a cookbook

Why do stocks necessarily drop during a recession?

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 ExecutionsUnderstanding statistics, execution plans, and 'ascending key problem'Actual Number of rows is too high even with new tablesDespite STATISTICS is updated, Estimated and Actual Row count is not sameTable statistic vs estimated rows query planSQL 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 RECOMPILE

4

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 execution-plan sql-server-2017 statistics

share|improve this question

edited 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Feb 26 at 14:34
  

  
  
  
  

add a comment | 

4

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 execution-plan sql-server-2017 statistics

share|improve this question

edited 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Feb 26 at 14:34
  

  
  
  
  

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 execution-plan sql-server-2017 statistics

share|improve this question

edited 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

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 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

share|improve this question

edited 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

edited 2 days ago

Paul White♦

52.8k14281456

edited 2 days ago

Paul White♦

52.8k14281456

asked Feb 26 at 14:01

k29k29

13516

asked Feb 26 at 14:01

k29k29

13516

1 Answer
1

active

oldest

votes

7

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

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 2 days ago

answered Feb 26 at 14:34

ForrestForrest

2,3111719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  Feb 26 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Perhaps you were looking for "significant figures"?
  
  – Joe Obbish
  2 days 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

7

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

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 2 days ago

answered Feb 26 at 14:34

ForrestForrest

2,3111719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  Feb 26 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Perhaps you were looking for "significant figures"?
  
  – Joe Obbish
  2 days ago
  

  
  
  
  

add a comment | 

7

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

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 2 days ago

answered Feb 26 at 14:34

ForrestForrest

2,3111719

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Interesting. I can replicate that as well. I wonder what that technical term is...
  
  – k29
  Feb 26 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Perhaps you were looking for "significant figures"?
  
  – Joe Obbish
  2 days ago
  

  
  
  
  

add a comment | 

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

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 2 days ago

answered Feb 26 at 14:34

ForrestForrest

2,3111719

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 2 days ago

answered Feb 26 at 14:34

ForrestForrest

2,3111719

answered Feb 26 at 14:34

ForrestForrest

2,3111719

answered Feb 26 at 14:34

ForrestForrest

2,3111719