Post reply

updating statistics

  • November 2, 2011 at 12:08 pm

    #245440

    I know updating statistics improves the performance of the queries(selects the best execution plan)...but my question is, i have not created any indexes or any sort of other query changes. In this case how does the update statistics improves the performance of the queries

    Thanks in advance

  • November 2, 2011 at 12:40 pm

    #1402367

    SQL Server doesn't just keep stats on indexes. It can also keep stats on columns used in queries, even if they aren't indexed.

    There's a good article on it here: http://technet.microsoft.com/en-us/library/cc966419.aspx

    It's specifically about SQL 2005, but 2008 fits in it too.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 3, 2011 at 7:27 am

    #1402628

    My guess is that you might benefit in joins where the optimizer can choose the base table based on the statistics even if you're not using indexes.

  • November 3, 2011 at 2:39 pm

    #1402940

    sandyinfowave (11/2/2011)

    I know updating statistics improves the performance of the queries(selects the best execution plan)...but my question is, i have not created any indexes or any sort of other query changes. In this case how does the update statistics improves the performance of the queries

    Thanks in advance

    It can at least help the optimizer choose the type of join based on the estimated volume of data. You probably don't want a nested loop join if the outer table returns 10 million rows. That's just one way it can help. I'm not an optimizer expert, so I'm sure there are other ways statistics can aid the optimizer.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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