sys.dm_db_stats_properties (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.dm_db_stats_properties (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database.

sys.dm_db_stats_properties (object_id, stats_id)  

object_id
Is the ID of the object in the current database for which properties of one of its statistics is requested. object_id is int.

stats_id
Is the ID of statistics for the specified object_id. The statistics ID can be obtained from the sys.stats dynamic management view. stats_id is int.

Column nameData typeDescription
object_idintID of the object (table or indexed view) for which to return the properties of the statistics object.
stats_idintID of the statistics object. Is unique within the table or indexed view. For more information, see sys.stats (Transact-SQL).
last_updateddatetime2Date and time the statistics object was last updated.
rowsbigintTotal number of rows in the table or indexed view when statistics were last updated. If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table.
rows_sampledbigintTotal number of rows sampled for statistics calculations.
stepsintNumber of steps in the histogram. For more information, see DBCC SHOW_STATISTICS (Transact-SQL).
unfiltered_rowsbigintTotal number of rows in the table before applying the filter expression (for filtered statistics). If statistics are not filtered, unfiltered_rows is equal to the value returns in the rows column.
modification_counterbigintTotal number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated.

This column does not contain information for memory-optimized tables.

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.

Requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

A. Returning all statistics properties for a table

The following example returns properties of all statistics that exist for the table TEST.

SELECT  
    sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter   
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('TEST');  

B. Returning statistics properties for frequently modified objects

The following example returns all tables, indexed views, and statistics in the current database for which the leading column was modified more than 1000 times since the last statistics update.

SELECT   
    obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter  
FROM sys.objects AS obj   
JOIN sys.stats stat ON stat.object_id = obj.object_id  
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE modification_counter > 1000;  

DBCC SHOW_STATISTICS (Transact-SQL)
sys.stats (Transact-SQL)
Object Related Dynamic Management Views and Functions (Transact-SQL)
Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft