sys.dm_db_stats_properties

sys.dm_db_stats_properties#

Returns the properties of one statistics object of a table: when its statistics were last calculated, how many rows they describe, and how many writes the table has taken since. Use it the way SQL Server maintenance scripts do - to decide which statistics are worth recalculating.

Syntax#

sys.dm_db_stats_properties ( object_id , stats_id )

Arguments#

object_id

The identifier of a table in the current database, as returned by OBJECT_ID.

stats_id

The identifier of a statistics object of that table, as listed in sys.stats.

Tables Returned#

One row, with the columns SQL Server returns:

Column name	Data type	Description
object_id	int	The table the statistics object belongs to.
stats_id	int	The statistics object.
last_updated	datetime2	When the statistics were last calculated, in the server’s time zone. NULL until they have been calculated at least once.
rows	bigint	The number of rows in the table when the statistics were calculated.
rows_sampled	bigint	The number of rows the calculation read. Querona counts every row, so this equals `rows`.
steps	int	The number of histogram steps. Querona builds no histogram, so this is always NULL.
unfiltered_rows	bigint	The number of rows before any filter. Equal to `rows`.
modification_counter	bigint	How many statements have written to the table since the statistics were last calculated. NULL until they have been calculated at least once.
persisted_sample_percent	float	Always 0: Querona counts rather than samples, so no sample percentage is persisted.

The function returns no rows - not an error - when the object does not exist, is not visible to the caller, carries no statistics (views never do), or has no statistics object with the given identifier, and when either argument is NULL.

Example#

SELECT p.stats_id, p.last_updated, p.rows, p.modification_counter
FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.Customers'), 1) AS p;