Difference in method for updating statistics

• toniupstny

  Hall of Fame

  Points: 3986

  More actions

  November 14, 2007 at 8:02 pm

  #126988

  Hi. First I am new to this so pardon if this is a dumb question.

  Trying to understand the difference between updating statistics using these two commands:

  UPDATE STATISTICS tblname WITH FULLSCAN ALL

  and

  EXEC sp_updatestats 'resample'

  I understand the first would do a full scan of the table and take more resources (right?) and the second would do a resampling; however, can you please explain when would it make sense to use one versus the other method?

  Thanks!

• Clive Strong

  SSChampion

  Points: 11222

  More actions

  November 16, 2007 at 12:58 pm

  #751963

  You're correct.

  If you use UPDATE STATISTICS WITH FULLSCAN ALL you will update all statistics (column & index) for that particular table. Every row will be used for sampling.

  Using sp_updatestats 'resample' will only use the a sample of rows within the table. The resample figure (I believe) is a calculation based on number of rows and frequency of modifications.

  Sampling is generally fine to do and thats what a lot of my tables use. However, I have had cases where the query plan changes due to the sampling. Running a fullscan gets the original query plan back and actually reduced IO.