Script to get statistics info in SQL Server

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

/*
	Part of PSBlitz - https://github.com/VladDBA/PSBlitz
	License - https://github.com/VladDBA/PSBlitz/blob/main/LICENSE
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @LineFeed NVARCHAR(5);
DECLARE @MinRecords INT;
SET @LineFeed = CHAR(13) + CHAR(10);

SET @MinRecords = 10000;

SELECT @SQL = 
N'SELECT DB_NAME() AS [database],'
+ @LineFeed + N'SCHEMA_NAME([obj].[schema_id]) + ''.'''
+ @LineFeed + N'+ [obj].[name] AS [object_name],'
+ @LineFeed + N'[obj].[type_desc] AS [object_type],'
+ @LineFeed + N'[stat].[name] AS [stats_name],'
+ @LineFeed + N'CASE WHEN [stat].[auto_created] = 1 '
+ N'AND [stat].[user_created] = 0 THEN ''Auto-Created'''
+ @LineFeed + N'WHEN [stat].[user_created] = 1 '
+ N'AND [stat].[auto_created] = 0 THEN ''User-Created'''
+ @LineFeed + N'  ELSE ''Index'' END AS [origin],'
+ @LineFeed + N'[stat].[filter_definition],'
+ @LineFeed + N'[sp].[last_updated],'
+ @LineFeed + N'ISNULL([sp].[rows],0) AS [rows],'
+ @LineFeed + N'ISNULL([sp].[unfiltered_rows],0) AS [unfiltered_rows],'
+ @LineFeed + N'ISNULL([sp].[rows_sampled],0) AS [rows_sampled],'
+ @LineFeed + N'CASE WHEN [sp].[rows] IS NULL THEN 0 ' 
+ @LineFeed + N'ELSE (CAST(CAST([sp].[rows_sampled] AS FLOAT)' 
+ @LineFeed + N'/ CAST([sp].[rows] AS FLOAT)' 
+ @LineFeed + N'* 100.00 AS DECIMAL(5,2))) END AS [sample_percent],'
+ @LineFeed + N'ISNULL([sp].[modification_counter],0) AS [modification_counter],'
+ @LineFeed + N'CASE WHEN [sp].[modification_counter] IS NULL THEN 0 ' 
+ @LineFeed + N'ELSE (CAST(CAST([sp].[modification_counter] AS FLOAT)' 
+ @LineFeed + N'/ CAST([sp].[rows] AS FLOAT)' 
+ @LineFeed + N'* 100.00 AS DECIMAL(38,2))) END AS [modified_percent],'
+ @LineFeed + N'CASE WHEN [stat].[is_incremental] = 1 THEN ''Yes'''
+ @LineFeed + N'ELSE ''No'' END AS [incremental],'
+ @LineFeed + N'CASE WHEN [stat].[is_temporary] = 1 THEN ''Yes'''
+ @LineFeed + N'ELSE ''No'' END AS [temporary],'
+ @LineFeed + N'CASE WHEN [stat].[no_recompute] = 1 THEN ''Yes'''
+ @LineFeed + N'ELSE ''No'' END AS [no_recompute],'
+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 15
  THEN @LineFeed + N'CASE WHEN [stat].[has_persisted_sample]  = 1 THEN ''Yes'''
  + @LineFeed + N'ELSE ''No'' END AS [persisted_sample],'
  ELSE @LineFeed + N'''only available for 2019 and above'' AS [persisted_sample],'
END
+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 13 THEN 
  @LineFeed + N'[sp].[persisted_sample_percent],'
  ELSE @LineFeed + N'0 AS [persisted_sample_percent],'
END
+ @LineFeed + N'ISNULL([sp].[steps],0) AS [steps],'
+ @LineFeed + N'''No'' AS [partitioned], 1 AS [partition_number]' 
+ @LineFeed + N',''DBCC SHOW_STATISTICS ("''+SCHEMA_NAME([obj].[schema_id])+N''.'''
+'+[obj].[name]+N''", ''+[stat].[name]+N'');'' AS [get_details]'
+ @LineFeed + N'FROM   [sys].[stats] AS [stat]'
+ @LineFeed + N'CROSS APPLY [sys].[dm_db_stats_properties]([stat].[object_id],'
+ @LineFeed + N'[stat].[stats_id]) AS [sp]'
+ @LineFeed + N'INNER JOIN [sys].[objects] AS [obj]'
+ @LineFeed + N'ON [stat].[object_id] = [obj].[object_id]'
+ @LineFeed + N'WHERE'
+ @LineFeed + N'[obj].[type] IN ( ''U'', ''V'' )'	/*limit objects to tables and potentially indexed views*/
+ @LineFeed + CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) > 11
		THEN N'AND [stat].[is_incremental] = 0'
		ELSE N'' END				/*limit to non-incremental stats only */
+ @LineFeed + N'AND [sp].[rows] >= ' + CAST(@MinRecords AS NVARCHAR(10))
+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) > 11
		THEN + @LineFeed + N'UNION'
+ @LineFeed + N'SELECT DB_NAME() AS [database],'
+ @LineFeed + N'SCHEMA_NAME([obj].[schema_id]) + ''.'''
+ @LineFeed + N'+ [obj].[name] AS [object_name],'
+ @LineFeed + N'[obj].[type_desc] AS [object_type],'
+ @LineFeed + N'[stat].[name] AS [stats_name],'
+ @LineFeed + N'CASE WHEN [stat].[auto_created] = 1 '
+ N'AND [stat].[user_created] = 0 THEN ''Auto-Created'''
+ @LineFeed + N'WHEN [stat].[user_created] = 1 '
+ N'AND [stat].[auto_created] = 0 THEN ''User-Created'''
+ @LineFeed + N'  ELSE ''Index'' END AS [origin],'
+ @LineFeed + N'[stat].[filter_definition],'
+ @LineFeed + N'[sip].[last_updated],'
+ @LineFeed + N'ISNULL([sip].[rows],0) AS [rows],'
+ @LineFeed + N'ISNULL([sip].[unfiltered_rows],0) AS [unfiltered_rows],'
+ @LineFeed + N'ISNULL([sip].[rows_sampled],0) AS [rows_sampled],'
+ @LineFeed + N'CASE WHEN [sip].[rows] IS NULL THEN 0 ' 
+ @LineFeed + N'ELSE (CAST(CAST([sip].[rows_sampled] AS FLOAT)' 
+ @LineFeed + N'/ CAST([sip].[rows] AS FLOAT)' 
+ @LineFeed + N'* 100.00 AS DECIMAL(5,2)))' 
+ @LineFeed + N'END AS [sample_percent],'
+ @LineFeed + N'ISNULL([sip].[modification_counter],0)	AS [modification_counter],'
+ @LineFeed + N'CASE WHEN [sip].[modification_counter] IS NULL THEN 0 '
+ @LineFeed + N'ELSE (CAST(CAST([sip].[modification_counter] AS FLOAT)'
+ @LineFeed + N'/ CAST([sip].[rows] AS FLOAT)'
+ @LineFeed + N'* 100.00 AS DECIMAL(5,2)))'
+ @LineFeed + N'END AS [modified_percent],'
+ @LineFeed + N'CASE WHEN [stat].[is_incremental] = 1 THEN ''Yes'' '
+ @LineFeed + N'ELSE ''No'' END AS [incremental],'
+ @LineFeed + N'CASE WHEN [stat].[is_temporary] = 1 THEN ''Yes'' '
+ @LineFeed + N'ELSE ''No'' END AS [temporary],'
+ @LineFeed + N'CASE WHEN [stat].[no_recompute] = 1 THEN ''Yes'''
+ @LineFeed + N'ELSE ''No'' END AS [no_recompute],'
+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 15
  THEN @LineFeed + N'CASE WHEN [stat].[has_persisted_sample]  = 1 THEN ''Yes'''
  + @LineFeed + N'ELSE ''No'' END AS [persisted_sample],'
  ELSE @LineFeed + N'''only available for 2019 and above'' AS [persisted_sample],'
END
+ @LineFeed + N'0 AS [persisted_sample_percent],'
+ @LineFeed + N'ISNULL([sip].[steps],0) AS [steps],'
+ @LineFeed + N'''Yes'' AS [partitioned],'
+ @LineFeed + N'[sip].[partition_number]'
+ @LineFeed + N',''DBCC SHOW_STATISTICS ("''+SCHEMA_NAME([obj].[schema_id])+N''.'''
+'+[obj].[name]+N''", ''+[stat].[name]+N'');'' AS [get_details]'
+ @LineFeed + N'FROM [sys].[stats] AS [stat]'
+ @LineFeed + N'CROSS APPLY [sys].[dm_db_incremental_stats_properties]([stat].[object_id],'
+ @LineFeed + N'[stat].[stats_id]) AS [sip]'
+ @LineFeed + N'INNER JOIN [sys].[objects] AS [obj]'
+ @LineFeed + N'ON [stat].[object_id] = [obj].[object_id]'
+ @LineFeed + N'WHERE'
+ @LineFeed + N'[obj].[type] IN ( ''U'', ''V'' )'	/*limit objects to tables and potentially indexed views*/
+ @LineFeed + N'AND [stat].[is_incremental] = 1'	/*limit to incremental stats only */
+ @LineFeed + N'AND [sip].[rows] >= ' + CAST(@MinRecords AS NVARCHAR(10))
+ @LineFeed + N'ORDER BY [modified_percent] DESC OPTION(RECOMPILE);'
		ELSE 
		+ @LineFeed + N'ORDER BY [modified_percent] DESC OPTION(RECOMPILE);'
END;
BEGIN
	EXEC(@SQL);
END;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

Part of PSBlitz - https://github.com/VladDBA/PSBlitz

License - https://github.com/VladDBA/PSBlitz/blob/main/LICENSE

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @SQL NVARCHAR(MAX);

DECLARE @LineFeed NVARCHAR(5);

DECLARE @MinRecords INT;

SET @LineFeed = CHAR(13) + CHAR(10);

SET @MinRecords = 10000;

SELECT @SQL =

N'SELECT DB_NAME() AS [database],'

+ @LineFeed + N'SCHEMA_NAME([obj].[schema_id]) + ''.'''

+ @LineFeed + N'+ [obj].[name] AS [object_name],'

+ @LineFeed + N'[obj].[type_desc] AS [object_type],'

+ @LineFeed + N'[stat].[name] AS [stats_name],'

+ @LineFeed + N'CASE WHEN [stat].[auto_created] = 1 '

+ N'AND [stat].[user_created] = 0 THEN ''Auto-Created'''

+ @LineFeed + N'WHEN [stat].[user_created] = 1 '

+ N'AND [stat].[auto_created] = 0 THEN ''User-Created'''

+ @LineFeed + N' ELSE ''Index'' END AS [origin],'

+ @LineFeed + N'[stat].[filter_definition],'

+ @LineFeed + N'[sp].[last_updated],'

+ @LineFeed + N'ISNULL([sp].[rows],0) AS [rows],'

+ @LineFeed + N'ISNULL([sp].[unfiltered_rows],0) AS [unfiltered_rows],'

+ @LineFeed + N'ISNULL([sp].[rows_sampled],0) AS [rows_sampled],'

+ @LineFeed + N'CASE WHEN [sp].[rows] IS NULL THEN 0 '

+ @LineFeed + N'ELSE (CAST(CAST([sp].[rows_sampled] AS FLOAT)'

+ @LineFeed + N'/ CAST([sp].[rows] AS FLOAT)'

+ @LineFeed + N'* 100.00 AS DECIMAL(5,2))) END AS [sample_percent],'

+ @LineFeed + N'ISNULL([sp].[modification_counter],0) AS [modification_counter],'

+ @LineFeed + N'CASE WHEN [sp].[modification_counter] IS NULL THEN 0 '

+ @LineFeed + N'ELSE (CAST(CAST([sp].[modification_counter] AS FLOAT)'

+ @LineFeed + N'/ CAST([sp].[rows] AS FLOAT)'

+ @LineFeed + N'* 100.00 AS DECIMAL(38,2))) END AS [modified_percent],'

+ @LineFeed + N'CASE WHEN [stat].[is_incremental] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [incremental],'

+ @LineFeed + N'CASE WHEN [stat].[is_temporary] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [temporary],'

+ @LineFeed + N'CASE WHEN [stat].[no_recompute] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [no_recompute],'

+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 15

THEN @LineFeed + N'CASE WHEN [stat].[has_persisted_sample] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [persisted_sample],'

ELSE @LineFeed + N'''only available for 2019 and above'' AS [persisted_sample],'

END

+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 13 THEN

@LineFeed + N'[sp].[persisted_sample_percent],'

ELSE @LineFeed + N'0 AS [persisted_sample_percent],'

END

+ @LineFeed + N'ISNULL([sp].[steps],0) AS [steps],'

+ @LineFeed + N'''No'' AS [partitioned], 1 AS [partition_number]'

+ @LineFeed + N',''DBCC SHOW_STATISTICS ("''+SCHEMA_NAME([obj].[schema_id])+N''.'''

+'+[obj].[name]+N''", ''+[stat].[name]+N'');'' AS [get_details]'

+ @LineFeed + N'FROM [sys].[stats] AS [stat]'

+ @LineFeed + N'CROSS APPLY [sys].[dm_db_stats_properties]([stat].[object_id],'

+ @LineFeed + N'[stat].[stats_id]) AS [sp]'

+ @LineFeed + N'INNER JOIN [sys].[objects] AS [obj]'

+ @LineFeed + N'ON [stat].[object_id] = [obj].[object_id]'

+ @LineFeed + N'WHERE'

+ @LineFeed + N'[obj].[type] IN ( ''U'', ''V'' )' /*limit objects to tables and potentially indexed views*/

+ @LineFeed + CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) > 11

THEN N'AND [stat].[is_incremental] = 0'

ELSE N'' END /*limit to non-incremental stats only */

+ @LineFeed + N'AND [sp].[rows] >= ' + CAST(@MinRecords AS NVARCHAR(10))

+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) > 11

THEN + @LineFeed + N'UNION'

+ @LineFeed + N'SELECT DB_NAME() AS [database],'

+ @LineFeed + N'SCHEMA_NAME([obj].[schema_id]) + ''.'''

+ @LineFeed + N'+ [obj].[name] AS [object_name],'

+ @LineFeed + N'[obj].[type_desc] AS [object_type],'

+ @LineFeed + N'[stat].[name] AS [stats_name],'

+ @LineFeed + N'CASE WHEN [stat].[auto_created] = 1 '

+ N'AND [stat].[user_created] = 0 THEN ''Auto-Created'''

+ @LineFeed + N'WHEN [stat].[user_created] = 1 '

+ N'AND [stat].[auto_created] = 0 THEN ''User-Created'''

+ @LineFeed + N' ELSE ''Index'' END AS [origin],'

+ @LineFeed + N'[stat].[filter_definition],'

+ @LineFeed + N'[sip].[last_updated],'

+ @LineFeed + N'ISNULL([sip].[rows],0) AS [rows],'

+ @LineFeed + N'ISNULL([sip].[unfiltered_rows],0) AS [unfiltered_rows],'

+ @LineFeed + N'ISNULL([sip].[rows_sampled],0) AS [rows_sampled],'

+ @LineFeed + N'CASE WHEN [sip].[rows] IS NULL THEN 0 '

+ @LineFeed + N'ELSE (CAST(CAST([sip].[rows_sampled] AS FLOAT)'

+ @LineFeed + N'/ CAST([sip].[rows] AS FLOAT)'

+ @LineFeed + N'* 100.00 AS DECIMAL(5,2)))'

+ @LineFeed + N'END AS [sample_percent],'

+ @LineFeed + N'ISNULL([sip].[modification_counter],0) AS [modification_counter],'

+ @LineFeed + N'CASE WHEN [sip].[modification_counter] IS NULL THEN 0 '

+ @LineFeed + N'ELSE (CAST(CAST([sip].[modification_counter] AS FLOAT)'

+ @LineFeed + N'/ CAST([sip].[rows] AS FLOAT)'

+ @LineFeed + N'* 100.00 AS DECIMAL(5,2)))'

+ @LineFeed + N'END AS [modified_percent],'

+ @LineFeed + N'CASE WHEN [stat].[is_incremental] = 1 THEN ''Yes'' '

+ @LineFeed + N'ELSE ''No'' END AS [incremental],'

+ @LineFeed + N'CASE WHEN [stat].[is_temporary] = 1 THEN ''Yes'' '

+ @LineFeed + N'ELSE ''No'' END AS [temporary],'

+ @LineFeed + N'CASE WHEN [stat].[no_recompute] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [no_recompute],'

+ CASE WHEN CAST(SERVERPROPERTY('ProductMajorVersion') AS TINYINT) >= 15

THEN @LineFeed + N'CASE WHEN [stat].[has_persisted_sample] = 1 THEN ''Yes'''

+ @LineFeed + N'ELSE ''No'' END AS [persisted_sample],'

ELSE @LineFeed + N'''only available for 2019 and above'' AS [persisted_sample],'

END

+ @LineFeed + N'0 AS [persisted_sample_percent],'

+ @LineFeed + N'ISNULL([sip].[steps],0) AS [steps],'

+ @LineFeed + N'''Yes'' AS [partitioned],'

+ @LineFeed + N'[sip].[partition_number]'

+ @LineFeed + N',''DBCC SHOW_STATISTICS ("''+SCHEMA_NAME([obj].[schema_id])+N''.'''

+'+[obj].[name]+N''", ''+[stat].[name]+N'');'' AS [get_details]'

+ @LineFeed + N'FROM [sys].[stats] AS [stat]'

+ @LineFeed + N'CROSS APPLY [sys].[dm_db_incremental_stats_properties]([stat].[object_id],'

+ @LineFeed + N'[stat].[stats_id]) AS [sip]'

+ @LineFeed + N'INNER JOIN [sys].[objects] AS [obj]'

+ @LineFeed + N'ON [stat].[object_id] = [obj].[object_id]'

+ @LineFeed + N'WHERE'

+ @LineFeed + N'[obj].[type] IN ( ''U'', ''V'' )' /*limit objects to tables and potentially indexed views*/

+ @LineFeed + N'AND [stat].[is_incremental] = 1' /*limit to incremental stats only */

+ @LineFeed + N'AND [sip].[rows] >= ' + CAST(@MinRecords AS NVARCHAR(10))

+ @LineFeed + N'ORDER BY [modified_percent] DESC OPTION(RECOMPILE);'

ELSE

+ @LineFeed + N'ORDER BY [modified_percent] DESC OPTION(RECOMPILE);'

END;

BEGIN

EXEC(@SQL);

END;

2 comments

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.

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Script to get statistics info in SQL Server

What it returns

Result set example

The script

Calculate checksum for entire rows in SQL Server

Grant permissions on all functions and stored procedures

You may also like

2 comments

Leave a Comment Cancel Reply