How can I automate statistics management?

Occasionally, it can be beneficial to turn off AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, and manage these items yourself.

I’ve created the following stored procedure, which lives in master to allow it to be called from the context of some other database. This stored procedure can be added to a SQL Server Agent Job allowing it to run periodically.

The first time the stored proc runs, it creates a dbo.StatsHistory table in MSDB which tracks updates made by the proc. If you create a “Utilities” filegroup in MSDB prior to running the stored proc the first time, the dbo.StatsHistory is automatically created on that filegroup.

USE master;
IF EXISTS (
	SELECT 1
	FROM sys.schemas s
		INNER JOIN sys.objects o ON s.schema_id = o.schema_id
	WHERE s.name = 'dbo'
		AND o.name = 'sp_UpdateAllStats'
	)
BEGIN
	DROP PROCEDURE dbo.sp_UpdateAllStats;
END
GO
CREATE PROCEDURE dbo.sp_UpdateAllStats
(
	@MinHoursSinceLastUpdate INT = 0
	, @ShowCmd BIT = 1
	, @DebugOnly BIT = 0
)
AS
BEGIN
	/*
		Updates statistics for all objects in the current database.
		Date:	2015-11-25
		By:		Max Vernon

		NOTE: Stored Proc must be created in the [master] database, 
		and must be marked as a "system procedure" to enable proper
		functionality.

		EXEC sys.sp_MS_marksystemobject dbo.UpdateStats; 

		Version History:
		1.0		Initial Version
		1.1		Reduced noise messages
		1.2		creates StatsHistory table on correct filegroup
	*/
	SET NOCOUNT ON;
	SET ANSI_WARNINGS ON;
	DECLARE @cmd NVARCHAR(MAX);
	DECLARE @dsName NVARCHAR(MAX);
	DECLARE @ObjectID INT;
	DECLARE @StatsID INT;
	DECLARE @SchemaName SYSNAME;
	DECLARE @ObjectName SYSNAME;
	DECLARE @StatsName SYSNAME;
	DECLARE @StatsDate DATETIME;
	DECLARE @StartTime DATETIME;
	DECLARE @EndTime DATETIME;
	DECLARE @Rows INT;
	DECLARE @RowsSampled INT;
	DECLARE @ModificationCounter INT;
	DECLARE @DatabaseName SYSNAME;
	DECLARE @Action VARCHAR(50);
	SET @DatabaseName = DB_NAME();

	IF NOT EXISTS(
		SELECT 1
		FROM msdb.sys.schemas s
			INNER JOIN msdb.sys.objects o ON s.schema_id = o.schema_id
		WHERE s.name = 'dbo'
			AND o.name = 'StatsHistory'
			)
	BEGIN
		/* get the data_spaces.name independent of the collation and case */
		SELECT @dsName = ds.name 
		FROM msdb.sys.data_spaces ds
		WHERE ds.name = 'Utilities' COLLATE SQL_Latin1_General_CP1_CI_AS;
		IF @dsName IS NULL SET @dsName = 'PRIMARY';
		SET @cmd = '
		CREATE TABLE dbo.StatsHistory
		(
			StatsHistoryID INT NOT NULL
				CONSTRAINT PK_StatsHistory
				PRIMARY KEY CLUSTERED
				IDENTITY(1,1)
			, StatsHistoryDate DATETIME NOT NULL
				CONSTRAINT DF_StatsHistory_StatsHistoryDate
				DEFAULT (GETDATE())
			, DatabaseName SYSNAME NOT NULL
			, SchemaName SYSNAME NOT NULL
			, ObjectName SYSNAME NOT NULL
			, StatsName SYSNAME NOT NULL
			, UpdateDate DATETIME NULL
			, UpdateDurationMs INT NULL
			, Rows INT NOT NULL
			, RowsSampled INT NULL
			, ModificationCounter INT NULL
			, Action VARCHAR(50) NOT NULL
		) ON [' + @dsName + '];';
		EXEC msdb.sys.sp_executesql @cmd;
	END

	SET @cmd = '';

	DECLARE cur CURSOR LOCAL FORWARD_ONLY
	FOR 
	WITH StatsDetails AS
	(
		SELECT ObjectID = o.object_id
			, SchemaName = sch.name
			, ObjectName = o.name
			, StatsName = st.name
			, StatsID = st.stats_id
			, StatsDate = dsp.last_updated
			, Rows = dsp.rows
			, RowsSampled = dsp.rows_sampled
			, ModificationCounter = dsp.modification_counter /* number of modifications to the stat's 
																leading column that have been modified
																since the last stats update
															 */
			, UpdateStatement = 'UPDATE STATISTICS ' + quotename(sch.name) 
				+ '.' + quotename(o.name) 
				+ '(' + quotename(st.name) + ') WITH FULLSCAN' 
				+ CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END + ';'
		FROM sys.schemas sch 
			INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
			INNER JOIN sys.stats st ON o.object_id = st.object_id
		OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
		WHERE o.is_ms_shipped = 0
	) 
	SELECT sd.ObjectID
		, sd.StatsID
		, sd.SchemaName
		, sd.ObjectName
		, sd.StatsName
		, sd.UpdateStatement
		, sd.StatsDate
		, sd.Rows
		, sd.RowsSampled
		, sd.ModificationCounter
	FROM StatsDetails sd
	ORDER BY sd.SchemaName
		, sd.ObjectName
		, sd.StatsID;

	OPEN cur;

	FETCH NEXT FROM cur
	INTO @ObjectID
		, @StatsID
		, @SchemaName
		, @ObjectName
		, @StatsName
		, @cmd
		, @StatsDate
		, @Rows
		, @RowsSampled
		, @ModificationCounter;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @ShowCmd = 1
		BEGIN
			PRINT ('Processing Stats: ' + @SchemaName + '.' + @ObjectName + '.' + @StatsName);
		END
		SET @Action = '';
		IF COALESCE(@Rows, 0) = 0
		BEGIN
			/* 
				sys.dm_db_stats_properties may return NULL values if the stats
				have never been updated.
				Get the current row count for the object.
			*/
			SET ANSI_WARNINGS OFF;
			SELECT @Rows = SUM(ips.record_count)
			FROM (
				SELECT record_count = ips.record_count
				FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, NULL, NULL, 'SAMPLED') ips
				UNION ALL SELECT 0 /* ensure NULL is never returned to @Rows */
			) ips
			SET ANSI_WARNINGS ON;
		END
		IF COALESCE(@Rows, 0) > 0 
			AND ((@StatsDate IS NULL) OR DATEDIFF(HOUR, @StatsDate, GETDATE()) > @MinHoursSinceLastUpdate)
		BEGIN
			SET @StartTime = GETDATE();

			/* 
				only update the statistics if rows have been modified.
			   @ModificationCounter will be NULL if the statistics
			   object has never been updated via UPDATE STATISTICS
			*/

			IF @ShowCmd = 1 AND COALESCE(@ModificationCounter, 1) > 0 
			BEGIN
				PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
				PRINT (CHAR(9) + 'Modifications since last stats update: ' + CONVERT(VARCHAR(50), @ModificationCounter));
				PRINT (CHAR(9) + 'Will update stats using:');
				PRINT (CHAR(9) + @cmd);
			END

			IF COALESCE(@ModificationCounter, 1) = 0 
			BEGIN
				SET @Action = 'ZeroModificationsSinceLastUpdate';
				IF @ShowCmd = 1
				BEGIN
					PRINT (CHAR(9) + 'Zero modifications since last update on ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
				END
			END

			IF @DebugOnly = 0 AND COALESCE(@ModificationCounter, 1) > 0 
			BEGIN
				EXEC sp_executesql @cmd;
				SET @Action = 'Updated';
			END
			SET @EndTime = GETDATE();

			IF @DebugOnly = 0
			BEGIN
				SELECT @StatsDate = COALESCE(dsp.last_updated, GETDATE())
					, @Rows = COALESCE(dsp.rows, 0)
					, @RowsSampled = COALESCE(dsp.rows_sampled, 0)
					/*
						Leave this out since it will be zero at this point.  
						The value from prior to executing UPDATE STATISTICS
						shows the number of rows modified since the last time
						stats were updated, and is useful to save in the 
						dbo.StatsHistory table; the below code clobbers that
						value.
						, @ModificationCounter = COALESCE(dsp.modification_counter, 0)
					*/
				FROM sys.schemas sch 
					INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
					INNER JOIN sys.stats st ON o.object_id = st.object_id
				OUTER APPLY sys.dm_db_stats_properties(o.object_id, st.stats_id) dsp
				WHERE sch.name = @SchemaName
					AND o.name = @ObjectName
					AND st.name = @StatsName;

				INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
				VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
			END
			ELSE
			BEGIN
				SET @Action = 'DebugMode';
				INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
				VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
			END
		END
		ELSE
		BEGIN
			IF COALESCE(@Rows, 0) = 0
			BEGIN
				IF @ShowCmd = 1 
				BEGIN
					PRINT (CHAR(9) + 'Contains zero rows.  Stats will not be updated.');
				END
				SET @Action = 'ContainsZeroRows';
				INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
				VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
			END
			ELSE
			BEGIN
				IF @ShowCmd = 1 
				BEGIN
					PRINT (CHAR(9) + 'Last updated ' + CASE WHEN @StatsDate IS NULL THEN '(never)' ELSE CONVERT(VARCHAR(50), @StatsDate, 120) END + '.');
					PRINT (CHAR(9) + 'SKIPPING');
				END
				SET @Action = 'UpdatedTooRecently';
				INSERT INTO msdb.dbo.StatsHistory (DatabaseName, SchemaName, ObjectName, StatsName, UpdateDate, UpdateDurationMs, Rows, RowsSampled, ModificationCounter, Action)
				VALUES (@DatabaseName, @SchemaName, @ObjectName, @StatsName, @StatsDate, DATEDIFF(MILLISECOND, @StartTime, @EndTime), @Rows, @RowsSampled, @ModificationCounter, @Action);
			END
		END

		IF @ShowCmd = 1 
		BEGIN
			PRINT (''); /* add a blank line between output rows */
		END	

		FETCH NEXT FROM cur
		INTO @ObjectID
			, @StatsID
			, @SchemaName
			, @ObjectName
			, @StatsName
			, @cmd
			, @StatsDate
			, @Rows
			, @RowsSampled
			, @ModificationCounter;
	END

	CLOSE cur;
	DEALLOCATE cur;
END
GO
EXEC sys.sp_MS_marksystemobject 'dbo.sp_UpdateAllStats'; 
GO

Hopefully you’ll find the above code useful. Let me know if you have questions!