Doubt about Update statistics

Question

Doubt about Update statistics

rafael_si

Ten Centuries

Points: 1059
More actions
May 12, 2011 at 6:52 am

#240094

Hi all,
I'd like to know what happen when I ran the command update statistics with no option informed, for example : update statistics <mytable>. What is the default option in this case? full scan, sample...?
Thanks in advance!
Rafael Melo - Br.

Viewing 8 posts - 1 through 7 (of 7 total)

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

BLOB EATER SSChampion Points: 14609 More actions · Answer 1

If i am reading your question correctly i believe it will update the stats for all the indexes for the table in question

i.e. ( testcustomerdetails table)

use < DB>

GO

UPDATE STATISTICS test.testcustomerdetails;

GO

Sorry if i have mis understood your question.....

rafael_si Ten Centuries Points: 1059 More actions · Answer 2

Ok... Thanks for you quick response... (and sorry my English, because it isn't my first language!!!:-))

I'll change my question: What is the difference when I execute the command UPDATE STATISTCS <MYTABLE> and

UPDATE STATISTCS <MYTABLE> WITH FULL SCAN.

regards,

Rafael Melo - Br

Shilpi Balan SSC Rookie Points: 31 More actions · Answer 3

Update statistics by default samples 25 percent of the data in the table to generate statistics full scan generates statistics based on your entire data(all rows).

Hope this helps

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

I think that the default behavior is that sql will perform a sample scan on the target table or indexed view. SQL Server automatically computes the required sample size, and obviously full scan - says use all rows in table or view to gather the statistics. FULLSCAN provides the same behavior as SAMPLE 100 PERCENT.

rafael_si Ten Centuries Points: 1059 More actions · Answer 5

Thank you guys!

You've clarified my doubt.

Rafael Melo - Br

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Did you try looking in Books Online before asking? Because the page on Update Statistics clearly states which option is default.

http://msdn.microsoft.com/en-us/library/ms187348.aspx

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

rafael_si Ten Centuries Points: 1059 More actions · Answer 7

Sorry Gail,

You are right!. I didn't search enough before asking.

Rafael Melo - Br