sp_updatestats Is Not Smart

Home / SQL Server 2005 / sp_updatestats Is Not Smart

No, I don’t mean the use of sp_updatestats is not smart. It’s a fine, quick mechanism for getting statistics updated in your system. But the procedure itself is not smart. I keep seeing stuff like “sp_updatestats knows which statistics need to be updated” and similar statements.

Nope.

Not true.

Wanna know how I know? It’s tricky. Ready? I looked at the query. It’s there, in full, at the bottom of the article (2014 CTP2 version, just in case yours is slightly different, like, for example, no Hekaton logic). Let’s focus on just this bit:

The most interesting part is right at the front, @ind_rowmodctr <> 0. That value is loaded with the cursor and comes from sys.sysindexes and the rowmodctr column there. In short, we can know that the “smart” aspect of sp_updatestats is that it assumes if there are any modifications, then updating statistics is good to go. We can argue for hours over how exactly you determine whether or not statistics are sufficiently out of date to warrant an update, but I’d be willing to bet that the sophisticated answers are not going to include just finding everything that’s been touched.

Now, don’t get me wrong. I’m not implying, suggesting or stating that sp_updatestats shouldn’t be used. It should. It’s fine. Just be very clear about what it does and how it does it.

 

Just a reminder, I’m putting on an all day seminar on query tuning in Louisville on June 20th, 2014. Seats are going fast, so please sign up early.

sp_updatestats:

4 Comments

  • Create post! I’d like to add that if you run this proc it will read the default sample size (about 1%) from every index that’s been modified since the last stats update. That’s a lot of physical reads and a big hit to PLE, but will give the query optimizer more accurate information to base execution plans off of. Just something to keep in mind for those new to stats.

    I agree completely with Grant. It should be used. Use it until this becomes a problem, and keep this in mind if you have to write a custom one to stay within your maintenance window.

  • Jason

    Microsoft wrote this line of code:
    or (
    (@is_ver_current is not null) and (@is_ver_current = 0)
    )

    The looks the guy does not know null is not 0

OK, fine, but what do you think?

This site uses Akismet to reduce spam. Learn how your comment data is processed.