Sql-server – sys.dm_db_stats_properties seems to be misbehaving for a small table – how to get the number of records of a table in a different way

dmvscriptingsql serversql-server-2016statistics

I have this table in one of my databases:

IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL 
DROP TABLE [dbo].[repl_Gender_Type] 
GO
CREATE TABLE [dbo].[repl_Gender_Type] ( 
[Gender_TypeID]  CHAR(1)                          NOT NULL,
[Gender_Desc]    VARCHAR(20)                      NOT NULL,
[Create_Date]    DATETIME                         NOT NULL,
[Create_Userid]  VARCHAR(20)                      NOT NULL,
CONSTRAINT   [PK__Gender__46DD686B]  PRIMARY KEY NONCLUSTERED ([Gender_TypeID] asc))

there are only 3 records on it.

even after running the following command:

update statistics dbo.repl_Gender_Type with fullscan

I get no results when running the following query:

select SP.*
from  dbo.sysarticles A 
OUTER APPLY [sys].[dm_db_stats_properties](a.objid,1) sp
where a.objid = OBJECT_ID('dbo.repl_Gender_Type')

so dm_db_stats_properties does not keep track of small tables?
what is the work around to find the number of records in the table?

sys.dm_db_stats_properties returns an empty rowset under any of the
following conditions:

object_id or stats_id is NULL.
The specified object is not found or
does not correspond to a table or indexed view. The specified

statistics ID does not correspond to existing statistics for the
specified object ID.

The current user does not have permissions to
view the statistics object. This behavior allows for the safe usage of
sys.dm_db_stats_properties when cross applied to rows in views such as
sys.objects and sys.stats.

none of the above is right, me thinks.

I like to use this script below, that comes from this question:

sp_updatestats vs Update statistics

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

Best Answer

Looking for row counts in stats objects is problematic. Especially since stats are not updated automatically every time a row is inserted, deleted, or modified.

Use sys.dm_db_partition_stats instead. It returns row count information for every partition in the database.

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)
    , dbps.partition_number
    , dbps.row_count
    , dbps.*
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.dm_db_partition_stats dbps ON o.object_id = dbps.object_id
WHERE o.is_ms_shipped = 0
    AND (dbps.index_id = 0 OR dbps.index_id = 1); --heap or clustered indexes only

I wrote a blog post on SQLServerScience.com that shows some of the problems with the stats properties DMV in relation to row counts. That post uses this query to obtain row counts, and shows when the stats object was last updated:

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name)
    , ddsp.stats_id
    , last_updated = CONVERT(datetime2(1), ddsp.last_updated)
    , ddsp.rows
    , mod_count = ddsp.modification_counter
    , ddsp.unfiltered_rows
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.stats s ON o.object_id = s.object_id
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0;
Related Question