Are the posted questions getting worse?

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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 🙂

  • 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

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

  • 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

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



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Is it the Dissecting SQL Server execution plans e-Book? If so then yes. I have it with me. And have started reading it.

    I got it from SSC itself[/url]. It was part of an eBook Selection Zip file

    If you buy the hard copy, Grant gets money I guess.. I cheated on that and downloaded it...:hehe:

    -Roy

Viewing 15 posts - 5,146 through 5,160 (of 66,738 total)

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