0

Suppose I create a table with 1000 rows but don't gather statistics on the table or run a query that should trigger an automatic statistics creation:

create table test( a int )
go
insert into test select 1
go 1000
select * from test
sp_helpstats 'test','all'

When I check the plan for the select, I can see that the estimate is accurate and it's 1000. Where does that estimate comes from? Does it have anything to do with sys.partitions rows column?

  • 1
    Have a look at the whitepaper by Joe Sack on cardinality for SQL 2014, it helps explain how the optimizer will arrive at statistics in various scenarios: slrwnds.com/SQL-CardinalityEstimator – SQLRockstar Jan 7 '18 at 15:33
  • Thanks @SQLRockstar, Yes, I have gone through that whitepaper a while ago. I was always under that impression that row estimates came from statistics and heretics, but wanted to check were the accurate looking estimate came from for table scan when there were no statistics. – jesijesi Jan 7 '18 at 20:11
0

Try a different question: should I need to create statistics just to see how much space a table uses?

EXEC sp_spaceused 'test';

That query shouldn't just fail, right? Yet it provides the correct row count of 1000 rows. You can look at the code of sp_spaceused quite easily:

SELECT
            @reservedpages = SUM (reserved_page_count),
            @usedpages = SUM (used_page_count),
            @pages = SUM (
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE 0
                END
                ),
            @rowCount = SUM (
                CASE
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
                END
                )
        FROM sys.dm_db_partition_stats
        WHERE object_id = @id;

This doesn't mean that the estimate "comes from the sys.dm_db_partition_stats DMV". DMVs are created for end users to write application code against. The approximate row count of the table is stored in some internal structure which can be used to provide a cardinality estimate when there's no other option available for the query optimizer. If you need to know the details about this internal structure you could try using a debugger, but it's not like there's any guarantee that it wouldn't change between SQL Server versions.

It's worth mentioning that you should almost never have tables without statistics in production.

  • So I am guessing the answer to the question where the estimate comes from is that its from sys.dm_db_partition_stats? – jesijesi Jan 7 '18 at 10:11
  • @jesijesi, the row count is ultimately stored in internally for use by the optimizer to estimate row counts. Look at the sys.dm_db_partition_stats execution plan and you''ll see the row count is retrieved via the internal PARTITIONCOUNTS system TVF. The row count is also exposed via various documented and undocumented DMVs. – Dan Guzman Jan 7 '18 at 12:17
  • @JoeObbish, it is for my understanding and not to solve any issues. – jesijesi Jan 7 '18 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.