Post reply

Doubt about Update statistics

  • 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.

  • May 12, 2011 at 7:09 am

    #1323637

    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.....

  • May 12, 2011 at 7:28 am

    #1323650

    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

  • May 12, 2011 at 7:33 am

    #1323654

    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

  • May 12, 2011 at 7:43 am

    #1323660

    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.

  • May 12, 2011 at 8:01 am

    #1323675

    Thank you guys!

    You've clarified my doubt.

    Rafael Melo - Br

  • May 12, 2011 at 8:15 am

    #1323688

    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

  • May 12, 2011 at 8:46 am

    #1323715

    Sorry Gail,

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

    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