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
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.