RSS Feed

Selectively Updating Statistics

23

February 3, 2012 by Mike Hillwig

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.

[SQL]– 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
— http://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[/SQL]


Search

Pages