Home » Script to get statistics info in SQL Server

Script to get statistics info in SQL Server

by Vlad Drumea

While working on updating the PSBlitz script that retrieves statistics info for a SQL Server database, I’ve decided I should also make it available in a blog post and add some explanations about what it returns.

The script has been tested with SQL Server 2014 and above.

What it returns

  • database – the name of the database
  • object_name – name of the table or indexed view
  • object_type – USER_TABLE in case of a table, VIEW in case of an indexed view
  • stats_name – the name of the statistics object
  • origin – specifies if the statistics originates from an index, was auto-create by SQL Server or was created by a user.
  • filter_definition – in case of filtered statistics – the expression used to filter the; NULL for unfiltered stats
  • last_updated – date and time the statistics object was last updated
  • rows – total number of rows in the table when statistics were last updated, might less the actual number of rows in case of filtered statistics.
  • unfiltered_rows – total number of rows in the table before applying the filter expression (for filtered stats); for unfiltered stats the value in unfiltered rows is equal to the one in the rows column
  • rows_sampled – total number of rows sampled for statistics calculations
  • sample_percent – percentage of rows sampled from the total number of rows for statistics calculation
  • modification_counter – total number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated
  • modified_percent – the percent of the modified data compared to the total number of rows since the last statistics update
  • incremental – specifies whether the statistics are created as incremental statistics or not
  • temporary – indicates whether the statistics are created as temporary or not. Temporary statistics support Always On availability groups secondary databases that are enabled for read-only access
  • no_recompute – indicates whether the statistics were created with the NORECOMPUTE option
  • persisted_sample – indicates whether the statistics were created or updated with the PERSIST_SAMPLE_PERCENT option
  • persisted_sample_percent – if PERSIST_SAMPLE_PERCENT was used, it will indicate the percentage used, otherwise it will be 0
  • steps – number of steps in the histogram
  • partitioned – indicates whether the statistics belongs to a partition or not – will be yes for incremental statistics info retrieved from sys.dm_db_incremental_stats_properties, and no for non-incremental statistics info retrieved from sys.dm_db_stats_properties
  • partition_number – the partition number to which the statistics belongs, will be 1 for non-incremental statistics
  • get_details – the DBCC SHOW_STATISTICS command that can be used to see stats header, density vector, and histogram information for a specific statistics object

The script filters out tables with less than 10k records, if you need to retrieve info for smaller tables, just change the value of @MinRecords from 10000 to 0.

Result set example

script statistics info SQL Server
Click for larger version

The script

You may also like


Vikas September 28, 2023 - 06:33

Msg 207, Level 16, State 1, Line 29
Invalid column name ‘has_persisted_sample’.
Msg 207, Level 16, State 1, Line 75
Invalid column name ‘has_persisted_sample’.

Vlad Drumea September 28, 2023 - 08:31

Hi Vikas,
Good catch!
Looks like that column is only available in sys.stats for 2019 and above, I’ve updated the script to take that into consideration.


Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.