How often should sp_updatestats be called?
A question of mine which dealt with a slowly executing query introduced me to the sp_updatestats() function. I want to take pro-active steps to call it on a regular basis rather that wait for my queries to randomly start timing out during business hours. I was considering running it at midnight each night. My questions are:
Is there a useful way to determine when this procedure should be called instead of blindly running it each night? The documentation says that SQL Server updates these stats on its own. But with what frequency? How do I know when I need to step in and execute it manually?
Does the database lock up while this procedure is being run? It seems to take about four or five minutes to do its thing. Will the database be accessible during this period or will attempted queries be blocked? My tests indicate that I can still run queries but I’m not sure if this is just because I got lucky, querying against a table for which stats weren't being calculated at that precise moment.
Answer
Here is a quote from books on line:
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
Thus, you could run UPDATE STATS every day and it might not do anything.
Paul Randal's - How rowmodctr works
Kendra Little's article - Stale Statistics
From SQL Server Internals Book 2008 by Kalen Delany. If you do not have a copy of this book, you should get one.
Before 2008, rowmodctr was used. After 2008, colmodctr is being used.
These statistics are being used to determine when the recompile threshold (RT) is exceeded for a table and statistics are deemed staled and need to be updated.
For small tables, at least 500 changes have to occur. For large tables, at least 500 changes plus 20% of number of rows. For very tiny tables, at least 6 changes.
Last but not least, there are FULL, SAMPLE N %, and RE-SAMPLE options which determine how many rows to scan to create the new statistics.
So ...
What does this mean in a nutshell?
I run my update stats when I re-organize my indexes once a week. I do this on the weekend at some early time so that no one complains if the system gets slow. So far, this has worked for me and I hardly get any issues.