Sql-server – Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why

execution-plansql serversql-server-2017statistics

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:
enter image description here

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

Best Answer

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)

Related Question