• Home
• About

Selectively Updating Statistics

Most blogs should have a disclaimer that your mileage may vary. That’s not the case here. I can assure you that your mileage will vary. Test this before you run it against your 12 TB production data warehouse.

I recently inherited a few servers that have autoupdate of statistics disabled. That’s a long story.  In other cases, we want to manually update statistics because it may take a quite some time for SQL Server to detect that it has stale statistics. We were running a script that updates all statistics across all indexes on all tables in all databases. And it was taking forever.

One day it hit me.

We’re running Michelle Ufford‘s script to reorganize and rebuild indexes. What if we could do the same for statistics? Why were we forcing the update of statistics on a static table? With a little bit of help from a script that Kendra Little wrote, I was able to put together a process that will dynamically update statistics only where needed. And we added a little more logic to set the sample rate as well. For small tables, doing a full scan makes more sense. But for large tables, this a smaller sample size is needed.

A lot of the values are hard coded here and should be moved to parameters. Maybe in the next version. This thing looks for five percent or 1000 rows, whichever comes first. It works in my environment. Your mileage will vary.

-- Dynamic Database Statistics Update
 --
 -- Created: Mike Hillwig
 -- 01/26/2012
 --

create table #statsmaint
 (databasename varchar(100),
 schemaname varchar(100),
 tablename varchar(100),
 indexname varchar(100),
 rowsupdated int,
 totalrows int)

--- Stats calculation adapted from Kendra Little's script found at
 --- www.littlekendra.com/2009/04/21/how-stale-are-my-statistics/

exec sp_msforeachdb 'use ?;
 INSERT #statsmaint
 SELECT DISTINCT
 ''?''
 , s.name
 , tablename=object_name(i.object_id)
 ,index_name=i.[name]
 , si.rowmodctr
 , si.rowcnt
 FROM sys.indexes i (nolock)
 JOIN sys.objects o (nolock) on
 i.object_id=o.object_id
 JOIN sys.schemas s (nolock) on
 o.schema_id = s.schema_id
 JOIN sys.sysindexes si (nolock) on
 i.object_id=si.id
 and i.index_id=si.indid
 where
 STATS_DATE(i.object_id, i.index_id) is not null
 and o.type <> ''S''
 and (si.rowmodctr > 1000 OR cast(si.rowmodctr as float) / cast (si.rowcnt+1 as float) > .05)
 and ''?'' <> ''tempdb''
 order by si.rowmodctr desc'

DECLARE @v_dbname varchar(100)
 DECLARE @v_schemaname varchar(100)
 DECLARE @v_tablename varchar(100)
 DECLARE @v_indexname varchar(100)
 DECLARE @v_SQL varchar(1000)
 DECLARE @v_rowsupdated int
 DECLARE @v_percentscan varchar (10)
 DECLARE @v_totalrows int
 DECLARE c_statistics CURSOR FOR
 SELECT databasename, schemaname, tablename, indexname, rowsupdated, totalrows
 FROM #statsmaint

OPEN c_statistics
 FETCH NEXT FROM c_statistics INTO @v_dbname, @v_schemaname, @v_tablename, @v_indexname, @v_rowsupdated, @v_totalrows
 WHILE (@@fetch_status <> -1)
 BEGIN
 IF (@@fetch_status <> -2)
 BEGIN

SELECT @v_percentscan = '100' where @v_totalrows <= 50000
 SELECT @v_percentscan = '75' WHERE @v_totalrows BETWEEN 50000 AND 1000000
 SELECT @v_percentscan = '50' WHERE @v_totalrows BETWEEN 1000000 AND 10000000
 SELECT @v_percentscan = '25' where @v_totalrows > 10000000

select @v_SQL = 'UPDATE STATISTICS ' + @v_dbname + '.' + @v_schemaname + '.' + @v_tablename + ' ' + @v_indexname + ' WITH SAMPLE ' + @v_percentscan + ' PERCENT --' + cast (@v_rowsupdated as varchar) + ' OF ' + cast(@v_totalrows as varchar) + ' ROWS UPDATED. STARTED ' + cast(current_timestamp as varchar)
 print @v_sql
 exec (@v_sql)

END
 FETCH NEXT FROM c_statistics INTO @v_dbname, @v_schemaname, @v_tablename, @v_indexname, @v_rowsupdated, @v_totalrows
 END

CLOSE c_statistics
 DEALLOCATE c_statistics

drop table #statsmaint

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.