SQL Server Performance

Sample statistics

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by MichaelB, Jul 20, 2007.

  1. MichaelB Member

    All-
    I have a question... of course...
    I run update statistics with 50 sample each week.
    Each early monring I run sp_updatestats.
    Today, I had a view that became very slow. when I ran sp_updatestats or a plain update statistics on one of the tables involved the query didnt change. when I ran update statistics with 50 sample on that table, it flew. The question is how/should/can I increase the default sample size when sp_updatestats are run on this table? I thought it should take from the last update stats. Is this set when we run create statistics?
    I want to keep this from occuring on this table that does recieve a lot of updates, but also dont have much of a weekday window for maintenace.

    Ideas?

    Mike

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  2. MichaelB Member

    I appolgize.. it does not recieve a lot of inserts/updates.. that makes it even more questionable.. and opens up other issues?

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  3. satya Moderator

    Are there any computed columns or indexed views on this database?
    Try with WITH FULLSCAN, NORECOMPUTE periodically, when automatic statistics with the default sampling rate are not sufficient, you can control statistics sampling rates, and creation and update time, explicitly.

    http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx is a good reference in this case.

    (I'm going blog this one, its interesting)

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. MichaelB Member

    Satya,<br /><br />There is no computed columns or indexed views on this table. There is one indexed view on the db though. The curious thing is that there are so few updates to this table. The row count is close to 800K but the number of updates were 68 rows (obtained from the sysindexes table). so even if the sample was bigger I am unsure how running a 50 percent sample would make for better performance. There has to be something else that this is just lightly fixing. dont you agree?<br />I say this since I totally rebuild all indexes on the weekend and update those stats<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Mike<br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  5. MichaelB Member

    also, the estimated row count for the query was almost the same as actual when comparing the actual plan against the estimated plan.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  6. MohammedU New Member

    From BOL...
    In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.


    I believe you can use UPDATE STATISTICS command to use the sample instead of sp_updatestats...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. satya Moderator

    Have you reviewed the reference I gave above, as it speaks all about update stats and sampling in this case.

    quote:Originally posted by MikeEBS

    also, the estimated row count for the query was almost the same as actual when comparing the actual plan against the estimated plan.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. MichaelB Member

    Satya,<br /><br />Yah. blew my mind<img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br />So much info. <br />But I am still unsure. I keep thinking that it is not stats but something else since it is so little of changes.<br /><br />MohammedU. Yah I can run sample and do during weekend. I dont think I should have to run during week. thoughts?<br /><br />Mike<br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  9. satya Moderator

    How often you run with FULLSCAN, NORECOMPUTE option?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. MichaelB Member

    I run only sample 50 weekly. which should be OK. but I do need to recompute. I agree. I will put that into place for next week's window.
    The thing is this is not a table that grows a lot compared to a lot of others we have.


    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  11. MichaelB Member

    I mean. actually that I need to resample. I think the recompute is good.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  12. MichaelB Member

    aww heck.. may as well do fullscan

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  13. MichaelB Member

    also: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    Michael
    MCDBA

    "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  14. satya Moderator

    Thats what I have mentioned on my first reply to you to test [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br />You need a holiday [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  15. MichaelB Member

    Yah.. I know. I just resist the fullscan due to time needed.<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  16. MohammedU New Member

    Instead full scan you can reindex tables with ONLINE = ON option if you are using EE...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  17. satya Moderator

    .. I bet still you need to perform UPDATE STATISTICS if SAMPLING is used.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  18. MichaelB Member

    Using SE<img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  19. satya Moderator

    I don't think EE and SE would matter in this case, as Online Index Operations are only limited to EE>


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  20. MichaelB Member

    But still the question remains, how can a table that gets very few updates need full every couple days to keep it fast? could it be bad indexing? I am considering that (I didnt do them - honest!)<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Michael <br />MCDBA<br /><br />"The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends -- if they're okay, then it's you!"
  21. satya Moderator

  22. MichaelB Member

    I defrag the indexes each morning. I am stupified:(

Share This Page