May 29, 2009 at 12:55 pm
GilaMonster (5/29/2009)
Jack Corbett (5/29/2009)
Hey you folks who are going to write the book on indexing. How about helping this guy out?Why? You're doing a fine job yourself.
Thanks, but I had just about exhausted my answers, and my patience with the last question he asked. After looking at the way your questions have been answered, especially with the select statement since he was asking about inserting and updating originally I'm not sure I could have lasted any longer.
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
May 29, 2009 at 12:59 pm
Jack Corbett (5/29/2009)
GilaMonster (5/29/2009)
Jack Corbett (5/29/2009)
Hey you folks who are going to write the book on indexing. How about helping this guy out?Why? You're doing a fine job yourself.
Thanks, but I had just about exhausted my answers, and my patience with the last question he asked. After looking at the way your questions have been answered, especially with the select statement since he was asking about inserting and updating originally I'm not sure I could have lasted any longer.
I'm not sure he knows what he wants or what he's asking either.
Gut feel is that those bad stats are going to bite him but he probably won't notice.
Edit: Oohhh. Spoon-feeding time. And I'm all outa spoons. :hehe:
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
May 29, 2009 at 1:01 pm
Gaby Abed (5/29/2009)
Steve Jones - Editor (5/29/2009)
I still think he was creating a new word. Like "upgradation"Nothing wrong with upgradation, it's a perfectly cromulent word.
😀 (one hint where that's from)
Such verbiosity embiggens us all 🙂
May 29, 2009 at 1:03 pm
Dave Smith (5/29/2009)
Gaby Abed (5/29/2009)
Steve Jones - Editor (5/29/2009)
I still think he was creating a new word. Like "upgradation"Nothing wrong with upgradation, it's a perfectly cromulent word.
😀 (one hint where that's from)
Such verbiosity embiggens us all 🙂
Thanks Jebediah! 🙂
Makes me wonder what would happen if Homer was a DBA.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 29, 2009 at 1:07 pm
Dave Smith (5/29/2009)
Such verbiosity embiggens us all 🙂
Not sure I would like to be "embiggened", but the night is still young! 😉
-- You can't be late until you show up.
May 29, 2009 at 1:16 pm
GilaMonster (5/29/2009)
I'm not sure he knows what he wants or what he's asking either.Gut feel is that those bad stats are going to bite him but he probably won't notice.
Edit: Oohhh. Spoon-feeding time. And I'm all outa spoons. :hehe:
I was just reading in BOL about creating an index and what they said about turning off the auto-computing of statistics. It seems to me that you would want the statistics to update in order to use the index properly. Is there a special case where you wouldn't want the statistics to update?
-- Kit
May 29, 2009 at 1:24 pm
Kit G (5/29/2009)
GilaMonster (5/29/2009)
I'm not sure he knows what he wants or what he's asking either.Gut feel is that those bad stats are going to bite him but he probably won't notice.
Edit: Oohhh. Spoon-feeding time. And I'm all outa spoons. :hehe:
I was just reading in BOL about creating an index and what they said about turning off the auto-computing of statistics. It seems to me that you would want the statistics to update in order to use the index properly. Is there a special case where you wouldn't want the statistics to update?
There are times when you might want to control when the statistics update. For that reason I might turn off auto-update and do my own manual update.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 29, 2009 at 1:25 pm
Kit G (5/29/2009)
GilaMonster (5/29/2009)
I'm not sure he knows what he wants or what he's asking either.Gut feel is that those bad stats are going to bite him but he probably won't notice.
Edit: Oohhh. Spoon-feeding time. And I'm all outa spoons. :hehe:
I was just reading in BOL about creating an index and what they said about turning off the auto-computing of statistics. It seems to me that you would want the statistics to update in order to use the index properly. Is there a special case where you wouldn't want the statistics to update?
If it's a big table with a lot of activity, auto updating the stats can have negative performance impacts. That's the only case I know of.
- 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
May 29, 2009 at 1:35 pm
Kit G (5/29/2009)
GilaMonster (5/29/2009)
I'm not sure he knows what he wants or what he's asking either.Gut feel is that those bad stats are going to bite him but he probably won't notice.
Edit: Oohhh. Spoon-feeding time. And I'm all outa spoons. :hehe:
I was just reading in BOL about creating an index and what they said about turning off the auto-computing of statistics. It seems to me that you would want the statistics to update in order to use the index properly. Is there a special case where you wouldn't want the statistics to update?
It's for times where you've tested, monitored and measured and have found that the auto-update causes problems, either because it happens too often, doesn't happen often enough or happens at the wrong time. Async stats updates don't help and you know that updating manually at a certain time or certain schedule doesn't cause more problems.
The one time I used it recently was on a table in a stock market trading system. The table was updated in a job around 3am only. Nothing else ever changed it. The overnight load did enough changes to invalidate the stats but, because it didn't read the table, didn't update the stats. The update happened the first time the table was queried by the traders. That first execution, because it paid the penalty of the stats update was often slow and the dealers would phone and complain. Usually around 6am.
Solution: Turn the auto stats off for that table and do a manual update as the last step of the job that loaded the table. Problem solved.
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
May 29, 2009 at 1:40 pm
Gail's example for turning off Auto Update stats is a good one. I've never worked on a system that was big or busy enough that you would want to turn it off.
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
May 29, 2009 at 1:47 pm
Okay. I guess if it was a really big table you could run a job that would do the update when the table wasn't in use or something. That makes sense.
So the statistics either on or off won't affect what rows get returned for a query, like it isn't going to miss any rows because statistics weren't updated, but it can cause the query optimizer to take a different path in figuring out the query and returning the rows? It could cause the optimizer to not use the index at all when it really needs to? And this can, of course, degrade your performance.
I'm asking mostly because I want to make sure I'm figuring out what BOL is trying to tell me. 🙂
-- Kit
May 29, 2009 at 2:07 pm
Kit G (5/29/2009)
So the statistics either on or off won't affect what rows get returned for a query, like it isn't going to miss any rows because statistics weren't updated
No, not at all.
The stats are used by the Query Optimiser to estimate how many rows will be affected by various query operators. It uses that estimate to estimate the cost which in turn allows it to pick what should be the most optimal query plan.
Poor stats can cause the optimiser to pick a plan that looks fantastic but in reality performs terribly. If you look at an execution plan and see a large difference between actual and estimated row counts, it can indicated inaccurate stats (I say can, because there are other things that can cause this)
Shameless plug: If you're thinking of attending PASS this year, I'm doing a full session just on statistics.
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
May 29, 2009 at 2:18 pm
Cool. Thanks Gail. I haven't dug into execution plans so now when I do I'll have a little more sense about what I'm looking at. 🙂
-- Kit
May 29, 2009 at 2:25 pm
More shameless plug: http://sqlinthewild.co.za/index.php/2007/08/20/reading-execution-plans/
Grant, is your book still available for download anywhere?
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
May 29, 2009 at 2:30 pm
Viewing 15 posts - 5,146 through 5,160 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply