November 2, 2011 at 12:08 pm
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
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
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
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 queriesThanks 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