13

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:

  1. 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?

  2. 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.

Community
  • 1
  • 1
Chad Decker
  • 5,997
  • 8
  • 27
  • 31

1 Answers1

17

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

White Paper for 2008

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.

Community
  • 1
  • 1
CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
  • 1
    I just wanted to add that in Write intensive DB, for example daily ETL is altering large number of records in to DB tables, one should schedule update stats on those specific tables after ETL so that subsequent read (it could be another ETL) does not suffer from outdated stats. post ETL updating stats may take time so it is also depends on if taking more time by ETL is ok for end user. – Anup Shah Jan 02 '14 at 20:40
  • Thanks for info on stat updates! You mentioned that you run index maintenance each week. Question about that: I selected a table, right clicked, and chose Rebuild Indexes. It said 66% fragmentation so I clicked ok. The thing ran for at least five hours. I eventually gave up and aborted (then restored the database). Should it take that long? My *entire* database is only 33gb. Thanks for your help. – Chad Decker Jan 02 '14 at 21:02
  • 1
    Hi Chad, check out the scripts from http://ola.hallengren.com/. If you did right click and rebuild the clustered index during the busy day time, you might have issues. Also, depending upon version, you can rebuild offline. Other things to try to speed up such as task is to SORT IN TEMPDB. In short, these scripts are way better than the maintenance plans ... – CRAFTY DBA Jan 02 '14 at 22:30
  • Anup, you are right. However, the larger the table, the more changes have to occur before your stats are stale. – CRAFTY DBA Jan 02 '14 at 22:33
  • I can second the weekly approach, post-index rebuild. We do this on the 300 or so dbs that we have under our umbrella. Works a treat. Make sure you anticipate a long execution time though, as it can take a fair amount of time to run on large & write-heavy systems. – pim Dec 06 '16 at 18:45