SQL Server - Database statistics information

Query information about statistics in current database. You will get nice overview about all statistics and their properties in one table. It is important to keep statistics up to date and reflecting actual data most as possible. This query will give you the overview about statistics updates, sampling and histogram steps.

You can use Management Studio to show details for every single statistics object, but this table will show you all of them in one place, which is goo starting point.

TIP: Use filtering to show only SYSTEM, USER or INTERNAL tables if needed (row 16). You can also include filtering for some particular object only (row 17).

Script source: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql?view=sql-server-2017

SELECT 
	o.object_id,
	o.name AS [object_name],
	o.type_desc AS [object_type],
	sp.stats_id, 
	stat.name AS [statistics_name], 
	stat.filter_definition, 
	sp.last_updated, 
	sp.rows, 
	sp.rows_sampled, 
	sp.steps, 
	sp.unfiltered_rows, 
	sp.modification_counter   
FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
	INNER JOIN sys.objects o ON o.object_id = stat.object_id
--WHERE o.type_desc = 'USER_TABLE'
--	AND stat.object_id = object_id('object name here')

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
object_idintID of the object to which these statistics belong.
object_namesysnameObject name.
object_typenvarchar(60)Description of the object type
stats_idintID of the statistics. Is unique within the object. If statistics correspond to an index, the stats_id value is the same as the index_id value in the sys.indexes catalog view.
statistics_namesysnameName of the statistics. Is unique within the object.
filter_definitionnvarchar(max)Expression for the subset of rows included in filtered statistics. NULL = Non-filtered statistics.
last_updateddatetime2Date and time the statistics object was last updated. For more information, see the Remarks section in this page.
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. Memory-optimized tables: starting SQL Server 2016 (13.x) and in Azure SQL Database this column contains: total number of modifications for the table since the last time statistics were updated or the database was restarted.

Leave a Reply