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 )
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 Answer 1


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:

            @reservedpages = SUM (reserved_page_count),
            @usedpages = SUM (used_page_count),
            @pages = SUM (
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE 0
            @rowCount = SUM (
                    WHEN (index_id < 2) THEN row_count
                    ELSE 0
        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?
    – DMDM
    Jan 7, 2018 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, 2018 at 12:17
  • @JoeObbish, it is for my understanding and not to solve any issues.
    – DMDM
    Jan 7, 2018 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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