[statistics] Get the last 4 stat update time for every index on every table in a database.

Quickie, based off an earlier post. (http://thebakingdba.blogspot.com/2012/02/tuning-statistics-when-were-they.html)


Get the last 4 stat updates for every statistic based on an index. The filter is on the auto_created; flip that to get all the system-created (aka _WA_Sys_00000003_53D770D6) stats, or index-based.

	--Written by mbourgon 2016 as per http://thebakingdba.blogspot.com/2016/08/statistics-get-last-4-stat-update-time.html
	--CC, noncommerical with attribution. Use it as you want noncommercially, but this notice must remain.
	--run in the database you need to evaluate.
	DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @stats_name NVARCHAR(128), @tablename sysname, @the_schema_name sysname, @db_name sysname
	if object_id('tempdb..#stats_info') is not null
	DROP TABLE #stats_info
	CREATE TABLE #stats_info (updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,12), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))
	if object_id('tempdb..#stats_info2') is not null
	DROP TABLE #stats_info2
	CREATE TABLE #stats_info2 (the_schema_name sysname, table_name sysname, stat_name VARCHAR(128), updated DATETIME, [Table cardinality] BIGINT, [snapshot ctr] BIGINT, steps INT, density DECIMAL(19,16), [rows above] INT, [rows below] INT, [squared variance error] DECIMAL (19,16), [inserts since last update] MONEY, [deletes since last update] MONEY, [leading column type] VARCHAR(50))
	declare @listofstats TABLE (id int identity, the_schema_name sysname, table_name sysname, stats_name sysname)
	INSERT INTO @listofstats
	SELECT OBJECT_SCHEMA_NAME(object_id) AS the_schema_name,
	OBJECT_NAME(object_id) AS table_name,
	name AS stats_name
	FROM sys.stats
	WHERE auto_created = 1 --1 is automatically created (aka _WA_Sys_00000003_53D770D6), 0 is index-based.
	AND OBJECT_ID >100 --this should exclude any system processes
	SELECT @MIN = MIN(ID), @max = MAX(id) FROM @LISTOFSTATS
	DBCC TRACEON (2388) --needed to get the last 4 rebuild dates, not just the most current, as per Nayan Raval.
	WHILE @min <= @max
	begin
	TRUNCATE TABLE #stats_info
	SELECT @sql = NULL, @stats_name = NULL, @the_schema_name = NULL, @tablename = NULL
	SELECT @the_schema_name = QUOTENAME(the_schema_name),
	@tablename = QUOTENAME(table_name),
	@stats_name = stats_name,
	@db_name = DB_NAME(DB_ID())
	FROM @listofstats WHERE id = @min
	SELECT @sql = N'DBCC SHOW_STATISTICS ("' + @db_name + '.' + @the_schema_name+ '.' + @tablename+ '",'''+ @stats_name +''')'
	--if not including the dbname, you need
	PRINT @sql
	INSERT INTO #stats_info
	EXEC master..sp_executesql @sql
	INSERT INTO #stats_info2 SELECT @the_schema_name, @tablename, @stats_name, * FROM #stats_info
	SET @min = @min + 1
	end
	DBCC TRACEOFF (2388)
	SELECT * FROM #stats_info2 ORDER BY the_schema_name, table_name, stat_name

view raw last_4_stats_updates_for_a_database hosted with ❤ by GitHub