November 14, 2007 at 8:02 pm
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!
November 16, 2007 at 12:58 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply