How does SQL Server maintain rowcount metadata?

Question

For an example rowstore table...

CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL); 

There are a variety of different methods of retrieving table row counts from metadata in SQL Server - such as the below

SELECT SUM(rows) FROM sys.partitions WHERE object_id = object_id('dbo.T') AND index_id <= 1; SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = object_id('dbo.T') AND index_id <= 1; SELECT SUM(rows) FROM sys.sysindexes WHERE id = object_id('dbo.T') AND indid <= 1; SELECT OBJECTPROPERTYEX(object_id('dbo.T'), 'Cardinality') 

The execution plans apparently show a variety of different objects being used - such as the below.

• sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT
• sysidxstats CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS
• sysidxstats i CROSS APPLY OpenRowSet(TABLE INDEXPROP

What is going on here? Does SQL Server really maintain this metadata in multiple places? If so which is the most reliable method?

Answer 1

Does SQL Server really maintain this metadata in multiple places? If so which is the most reliable method?

All methods listed in the question ultimately retrieve the row count via a call to sqlmin!GrabRowsetCounts:

_{sys.partitions}

_{sys.dm_db_partition_stats}

_{sys.sysindexes}

_{OBJECTPROPERTYEX}

As you have noted, the information is only saved in one internal table.

As a matter of routine, rowset counts are maintained in memory for each HoBt. For per-row operations, this literally means a counter is incremented or decremented for each row modification e.g. via a call to sqlmin!BaseSharedHoBt::DeltaRowCount.

LOP_COUNT_DELTA log records for net changes are written at checkpoints via sqlmin!FlushHoBtsDeltaCounts:

Notice that many of the method names state they are non-transactional.

Answer 2

To look into this I created a new database as below

CREATE DATABASE RowCountTest GO USE RowCountTest SELECT physical_name FROM sys.database_files WHERE type_desc = 'ROWS' CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL); INSERT T(Id) SELECT TOP 100 object_id FROM sys.all_objects CHECKPOINT 

This created a database with a single data file and I used process monitor to monitor WriteFile operations on that. And then ran

INSERT T(Id) VALUES (-1000),(-1001),(-1002) DELETE FROM T WHERE Id = -1001; INSERT T(Id) VALUES (-1003),(-1004); CHECKPOINT 

ProcMon showed the following pages written to disc

Taking the offsets from that to see the objects they belong to

SELECT offset, page_id, CAST(page_id AS BINARY(4)) AS page_id_hex, page_type_desc, OBJECT_NAME(object_id) AS object_name FROM (VALUES (8192), (1171456), (1318912), (1572864), (2293760), (73728) )V(offset) CROSS APPLY sys.dm_db_page_info(db_id(), 1, offset/8192, 'DETAILED') 

Returned

offset	page_id	page_id_hex	page_type_desc	object_name
8192	1	0x00000001	PFS_PAGE	NULL
1171456	143	0x0000008F	DATA_PAGE	sysallocunits
1318912	161	0x000000A1	DATA_PAGE	sysrowsets
1572864	192	0x000000C0	DATA_PAGE	sysrscols
2293760	280	0x00000118	DATA_PAGE	T
73728	9	0x00000009	BOOT_PAGE	NULL

sysallocunits has columns relevant to page counts but nothing about row count, sysrscols contains the per-column modification counts used for cardinality based execution plan recompiles but again no row count column. The only one of these system base tables that has a row count column is rcrows in sysrowsets.

So I conclude that all of these methods are ultimately getting the value from the same place as it is only persistently stored in one place.

I also ran the below

/*Undocumented trace flag to return inactive records in transaction log */ DBCC TRACEON(2537) SELECT Operation, AllocUnitName, Description, [Page Id] FROM sys.fn_dblog(NULL, NULL) 

The relevant portion of the transaction log after the first manual CHECKPOINT is below

The various LOP_COUNT_DELTA entries show a few interesting aspects

• The "Description" column shows what exactly they are setting
• They all happen after the LOP_COMMIT_XACT for the user transactions (i.e. are not part of these transactions)
• The values set are combined from multiple different transactions (e.g. the insert 3 rows, delete 1 row, insert 2 rows on the 100 row table ultimately just led to a single LOP_COUNT_DELTA entry setting Row count: 104)

So, if the row count in sysrowsets is only updated periodically does this mean that the metadata values returned are incorrect?

All of the methods given in the question still returned the correct row count before it was written to sysrowsets. I presume the count is updated for in memory structure(s) used by the various internal DMFs (ALUCOUNT / PARTITIONCOUNTS / INDEXPROP).

I even killed the SQL Server process before the changes to sysrowsets had been written to disc and they still returned the correct values after service restart so I assume the non checkpointed deltas are recalculated as part of database recovery.

The rowcount is documented as potentially something that can go awry and need correction but I'm not sure what the circumstances are that can cause this.