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).
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:
Script results detailed description:
Column name | Data type | Description |
---|---|---|
object_id | int | ID of the object to which these statistics belong. |
object_name | sysname | Object name. |
object_type | nvarchar(60) | Description of the object type |
stats_id | int | ID 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_name | sysname | Name of the statistics. Is unique within the object. |
filter_definition | nvarchar(max) | Expression for the subset of rows included in filtered statistics. NULL = Non-filtered statistics. |
last_updated | datetime2 | Date and time the statistics object was last updated. For more information, see the Remarks section in this page. |
rows | bigint | Total 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_sampled | bigint | Total number of rows sampled for statistics calculations. |
steps | int | Number of steps in the histogram. For more information, see DBCC SHOW_STATISTICS (Transact-SQL). |
unfiltered_rows | bigint | Total 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_counter | bigint | Total 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. |