RSS Feed

Selectively Updating Statistics

20

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]


20 comments »

  1. You can certainly see your expertise within the work you write. The sector hopes for even more passionate writers like you who aren’t afraid to mention how they believe. All the time go after your heart.

  2. erjilopterin says:

    You made some nice points there. I looked on the internet for the issue and found most guys will go along with with your site.

  3. Really enjoyed this blog.Thanks Again. Great.

  4. Great, thanks for sharing this blog article.Really looking forward to read more. Really Cool.

  5. I truly appreciate this article post.Thanks Again.

  6. Wow, great article post.Much thanks again. Will read on…

  7. Appreciate you sharing, great article post.Thanks Again.

  8. Thank you ever so for you blog. Keep writing.

  9. MIDE-796 says:

    I really like and appreciate your blog article.Much thanks again. Much obliged.

  10. Appreciate you sharing, great blog article.Thanks Again. Really Great.

  11. Really informative blog.Thanks Again. Fantastic.

  12. I really like and appreciate your blog article.Much thanks again. Cool.

  13. I loved your blog post.Really thank you! Want more.

  14. movie2free says:

    Very informative blog.Really thank you! Much obliged.

  15. I am so grateful for your article.Much thanks again. Much obliged.

  16. Youre so cool! I dont suppose Ive read anything like this before. So good to seek out somebody with some unique thoughts on this subject. realy thanks for starting this up. this website is one thing that is wanted on the internet, someone with slightly originality. helpful job for bringing something new to the web!

  17. Hello there, You have done a great job. I will certainly digg it and personally recommend to my friends. I’m confident they will be benefited from this site.

  18. I went over this site and I conceive you have a lot of fantastic info , bookmarked (:.

  19. Great, thanks for sharing this blog post.Thanks Again. Cool.

  20. wow, awesome post. Much obliged.

Leave a Reply

Your email address will not be published. Required fields are marked *

Search

Pages