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.