Post reply

Difference between sp_updatestats and update statistics?

  • November 17, 2009 at 2:17 am

    #216809

    Hi,

    I have a discussion with my collegue about these 2 statements. I know that SP_UPDATESTATS is doing an update on all statistics on a table/index (if needed) and UPDATE STATISTICS is doing an update on a specified table, but is UPDATE STATISTICS doing more on a specified table?

    We have an issue about a statement which is running slow (at 9:00), but SP_UPDATESTATS was running at 7:00 and updated all statistics on that table. After running UPDATE STATISTICS on that table, the speed was ok (but the execution plan did not change) Statement is parameterized.

    My collegue is assuming that UPDATE STATISTICS is doing more on that table than an SP_UPDATESTATS, Although I don't believe that (examined the SP_UPDATESTATS code), can somebody confirm this? Any ideas?

    Thanks!

    Wilfred
    The best things in life are the simple things

  • November 17, 2009 at 2:42 am

    #1080112

    sp_updatestats calls UPDATE STATISTICS. It'll only call it for a stats set if at least one row has changed and by default it does a sampled update.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 17, 2009 at 3:15 am

    #1080118

    Thanks (I saw that in the code).

    Maybe I was not clear enough: if TABLE XYZ is selected by SP_UPDATESTATS for updating, is "UPDATE STATISTICS XYZ" (without any additional parameter) doing more than "SP_UPDATESTATS" ?

    Wilfred
    The best things in life are the simple things

  • November 17, 2009 at 4:09 am

    #1080136

    No. How can it?

    sp_updatestats, if run without parameters, runs UPDATE STATISTICS Schema.Table StatsName if the stats set is set to auto update and UPDATE STATISTICS Schema.Table StatsName NORECOMPUTE if it's not, same as if you ran the statements manually.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply