Helper function for determining the update threshold for statistics

In customer meetings, I am often asked about the formula that Microsoft SQL Server uses to automatically update statistics. I always have to look it up myself because this formula depends on a number of factors. For this reason, I decided to develop my own user-defined inline function, which I install in the customer databases. This UDF then gives me the threshold for all statistics when these statistics are automatically updated. I hope that this function is useful for you too.

When is an automatic update performed?

When determining automatic update of statistics follows a calculation formula that depends on three factors:

SQL Server BEFORE 2016 and no TraceFlag 2371 enabled

500 + (rows * 0.2)

SQL Server BEFORE 2016 and TraceFlag 2371 enabled

MIN(500 + (rows * 0.2), SQRT(1000 * rows)

If the old formula returns a smaller number than the new formula than this value is the threshold for SQL Server for the automatic update.

If the new formula returns a smaller number than the new formula than this value is the threshold for SQL Server for the automatic update.

SQL Server >= 2016

Same calculation as above!

Code

CREATE OR ALTER FUNCTION dbo.statistics_update
(
	@object_id		INT,
	@stats_id		INT,
	@tf2371_enabled	BIT = 1
)
RETURNS TABLE
AS
RETURN
(
	SELECT	s.name							AS	schema_name,
			t.name							AS	table_name,
			st.name							AS	statistics_name,
			st.no_recompute					AS	no_recompute,
			CASE WHEN db.is_auto_update_stats_on = 1
					THEN
						CASE WHEN st.no_recompute = 1
							 THEN 'no automatic update'
							 ELSE 'updated automatic '
						END +
						CASE WHEN db.is_auto_update_stats_async_on = 1
							 THEN 
								CASE WHEN st.no_recompute = 0
									 THEN 'asynchronously'
									 ELSE 'synchronous'
								END
						ELSE ''
					END
				 ELSE 'no automatic update'
			END								AS	automatic_update_status,
			dsp.stats_id,
			dsp.last_updated,
			dsp.rows,
			dsp.rows_sampled,
			dsp.steps,
			dsp.unfiltered_rows,
			dsp.modification_counter,
			CAST
			(
				/* The TF is obsolet since Microsoft SQL Server 2016 */
				CASE WHEN db.sql_server_version < 13 AND @tf2371_enabled = 0
					 THEN 500 + (0.20 * dsp.rows)
					 ELSE CASE WHEN 500 + (0.20 * dsp.rows) < SQRT(1000 * dsp.rows)
							   THEN 500 + (0.20 * dsp.rows)
							   ELSE SQRT(1000 * dsp.rows)
						  END
				END AS BIGINT
			)								AS	required_update_rows,
			FORMAT
			(
				dsp.modification_counter /
				CASE WHEN db.sql_server_version < 13 AND @tf2371_enabled = 0
						THEN 500 + (0.20 * dsp.rows)
						ELSE CASE WHEN 500 + (0.20 * dsp.rows) < SQRT(1000 * dsp.rows)
								THEN 500 + (0.20 * dsp.rows)
								ELSE SQRT(1000 * dsp.rows)
							END
				END,
				'#0.00%'
			)			AS update_counter_percentage
	FROM	sys.schemas AS s INNER JOIN sys.tables AS t
			ON (s.schema_id = t.schema_id)
			INNER JOIN sys.stats AS st
			ON (t.object_id = st.object_id)
			CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS dsp
			CROSS APPLY
			(
				SELECT	CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) AS sql_server_version,
						is_auto_update_stats_on,
						is_auto_update_stats_async_on
				FROM	sys.databases
				WHERE	name = DB_NAME()
			) AS db
	WHERE	(
				@object_id IS NULL
				OR st.object_id = @object_id
			)
			AND
			(
				@stats_id IS NULL
				OR st.stats_id = @stats_id
			)
);
GO

Notes

The function must be installed in the database in which the statistics are to be analyzed
It is advisable to import the function into the model database so that it is automatically available in all new databases
Unfortunately, activated trace flags cannot be read. For this reason, there is a parameter in the function that specifies whether trace flag 2371 is activated.
This parameter is ignored if the version of Microsoft SQL Server is >= 2016.
The default value for the parameter is 1

Example

SELECT	su.schema_name,
        su.table_name,
        su.statistics_name,
        su.no_recompute,
        su.automatic_update_status,
        su.stats_id,
        su.last_updated,
        su.rows,
        su.rows_sampled,
        su.steps,
        su.unfiltered_rows,
        su.modification_counter,
        su.required_update_rows,
        su.update_counter_percentage
FROM	sys.tables AS t
		INNER JOIN sys.stats AS st
		ON (t.object_id = st.object_id)
		CROSS APPLY info.statistics_update(st.object_id, st.stats_id, DEFAULT) AS su
ORDER BY
		su.schema_name,
		su.table_name,
		su.stats_id;

To make the result easier to read, I have divided the presentation into two figures.

If [no_recompute] is active the [automatic_update_status] will be „no automatic update“
If the database option „Auto Update Statistics“ is false the [automatic_update_status] will be „no automatic update“
If the database option „“Auto Update Statistics Asynchronously“ is true, the [automatic_update_status] will be [automatic update asynchronously“
The [modification_counter] returns the actual number of changes for the statistics object
The [required_update_rows] returns the threshold value when automatic update of statistics will start.
in [update_counter_percentage] you the the actual percentage to reach the threshold

Thank you for reading!