RYO Maintenance Plan – Update Statistics

Another key component of any good maintenance plan is updating statistics. Statistics are what help the SQL Server optimizer choose the best execution plan for your queries. Let your stats get too far out of date, and watch your query performance tank.

For me, the biggest shortcoming of the update statistics maintenance plan task is that it’s pretty much all-or-nothing. It will update stats for all of the objects you specify, whether the data in those objects has changed or not. And this may be fine for smaller databases. But when you start getting into larger amounts of data, updating statistics on everything, even static tables, can be a very time-consuming process. So when I set out to script this particular task, I wanted to include the capability to only update statistics on objects where data has changed since statistics were last updated.

Other than that one enhancement, the script I ended up with is a very straightforward solution, in adherence with the KISS ideology. As with my backup scripts, I wanted to be able to specify a database, or use wildcards to specify multiple databases. Just as the maintenance plan task would, I also wanted to be able to update column statistics, index statistics, or all statistics, and specify a sample size. Unlike the original task, however, the sample size would be a percentage only. Personally, I’ve never had any use for the “rows” sample size option, so I didn’t bother including it here. And lastly, my solution would also include the ability to specify changed objects or all objects. That’s it.

To do all this, the procedure accepts four parameters:

@dbs – This is the database name, i.e. ‘MyDatabase’, ‘My%’. The default is ‘%’ for all databases.
@whatstats – This specifies column statistics only (‘C’), index statistics only (‘I’) or all statistics (‘A’). The default is ‘A’.
@sample – This is the sample size as a percentage, 1 to 100. The default value is 100 for a fullscan.
@changedonly – This is a ‘Y’ or ‘N’ value to specify whether I want to only update statistics on objects where the data has changed since the last time statistics were updated.

So, to update statistics on all objects in all of my databases, with a full scan, I would run

exec maint.sp_update_stats 

If I want to update stats on changed objects in MyDatabase, I would run

exec maint.sp_update_stats @dbs='MyDatabase', @changedonly='Y'

Or maybe I want to update just index statistics on changed objects in MyDatabase using a 50% sample size:

exec maint.sp_update_stats @dbs='MyDatabase', @whatstats='I', @sample=50, @changedonly='Y'

One more thing before I lay the procedure code on you. To determine what objects have changed data, I used the rowmodctr column in sys.sysindexes. Yes, I know that table is deprecated. Yes, I know Microsoft doesn’t recommend using it. But it’s the best option available right now and the table is still there in SQL 2012 RC0. So until it goes away, I’ll continue to use it.

As I mentioned earlier, this is a very simple procedure, therefore I’m not going to step through it like I did with the backup procedures. I’ve documented it pretty thoroughly, so it should be clear to everyone (including myself later on) what I’m doing.

 /* maint.sp_update_stats.sql
Purpose: update statistics for one or more databases
Author: Colleen M. Morrow
Last Edited: 2012-02-17
Instructions: exec maint.sp_update_stats [database|%], [C|I|A], [1-100], [Y|N]
Parameters:
	@dbs: database name, accepts wildcards (i.e. 'son%')
	@whatstats: what statistics to update, column stats, index stats, or all
	@sample: sample size as a percent, 100 for fullscan
	@changedonly: update statistics for objects where data has changed since stats last updated
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [maint].[sp_update_stats] (@dbs VARCHAR(128) = '%', @whatstats char(1) = 'A', @sample tinyint = '100', @changedonly char(1) = 'N')
AS
BEGIN
	DECLARE

		 @tablename varchar(128)
		,@schemaname varchar(128)
		,@indexname varchar(128)
		,@dbname varchar(128)
		,@dbid smallint
		,@sqlstmt nvarchar(max)

	SET NOCOUNT ON

	BEGIN TRY

		IF @whatstats NOT IN ('C', 'I', 'A')
			RAISERROR (
				 N'Invalid value for parameter @whatstats.  Please specify C (column stats only), I (index stats only) or A (all  stats).'
				,16
				,1
				);

		IF @sample NOT BETWEEN 1 and 100
			RAISERROR (
				 N'Invalid value for parameter @sample.  Please a value between 1 and 100.'
				,16
				,1
				);

		IF @changedonly NOT IN ('Y', 'N')
			RAISERROR (
				 N'Invalid value for parameter @changedonly.  Please specify Y (only update stats where data has changed) or N (update  stats on all objects).'
				,16
				,1
				);

		--loop through all the databases we're covering
		DECLARE getdbs CURSOR FOR
			SELECT d.database_id,d.name FROM sys.databases d
			WHERE d.name <> 'tempdb'
			AND d.state = 0
			AND d.name LIKE @dbs
		OPEN getdbs
		FETCH getdbs INTO @dbid, @dbname
		WHILE @@FETCH_STATUS = 0
		BEGIN

			--build the base query; put the objects into a temp table to loop through after
			SET @sqlstmt = 'USE '+quotename(@dbname, '[')+'; select s.name as schemaname,t.name as tablename, i.name as indexname '
				+'into ##dbtables '
				+'from sys.sysindexes i '
				+'join sys.tables t on t.object_id = i.id and t.type in (''U'', ''V'', ''TF'') '
				+'join sys.schemas s on s.schema_id = t.schema_id '
				+'where i.indid > 0 '

			--get only changed objects
			IF @changedonly = 'y'
				SET @sqlstmt = @sqlstmt + 'and i.rowmodctr > 0 '

			--exclude indexes
			IF @whatstats = 'C'
				SET @sqlstmt = @sqlstmt + 'and not exists (select 1 from sys.indexes si where si.object_id = i.id and si.index_id =  i.indid)'
			--include only indexes
			IF @whatstats = 'I'
				SET @sqlstmt = @sqlstmt + 'and exists (select 1 from sys.indexes si where si.object_id = i.id and si.index_id =  i.indid)'

			EXEC (@sqlstmt)

			--now loop through all our objects and build the update stats statement
			DECLARE gettabs CURSOR FOR
				SELECT schemaname, tablename, indexname FROM ##dbtables
			OPEN gettabs
			FETCH gettabs INTO @schemaname, @tablename, @indexname
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @sqlstmt = NULL
				SET @sqlstmt = 'USE '+quotename(@dbname, '[')+'; UPDATE STATISTICS '+quotename(@schemaname, '[')+'.'+quotename(rtrim (@tablename), '[')+' '+
					quotename(@indexname, '[')+' WITH '

				IF @sample < 100
					SET @sqlstmt = @sqlstmt + 'SAMPLE '+CAST(@sample AS VARCHAR(3))+' PERCENT'
				ELSE
					SET @sqlstmt = @sqlstmt + 'FULLSCAN'

				SET @sqlstmt = @sqlstmt + ';'
				EXEC (@sqlstmt)
				--PRINT @sqlstmt

				FETCH gettabs INTO @schemaname, @tablename, @indexname
			END
			CLOSE gettabs
			DEALLOCATE gettabs
			DROP TABLE ##dbtables

			FETCH getdbs INTO @dbid, @dbname
		END
		CLOSE getdbs
		DEALLOCATE getdbs

	END TRY
	BEGIN CATCH

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT @ErrorMessage = ERROR_MESSAGE(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE();

		-- Use RAISERROR inside the CATCH block to return
		-- error information about the original error that
		-- caused execution to jump to the CATCH block.
		RAISERROR (@ErrorMessage, -- Message text.
				   @ErrorSeverity, -- Severity.
				   @ErrorState -- State.
				   );

	END CATCH

END
GO
/* End maint.sp_update_stats.sql */ 

And with that, we’ve covered backups, backup cleanup, and now statistics. We still need to take care of integrity checks, index maintenance and job history cleanup, and those will be covered in future posts.

Also recommended: