SQL Server Performance

does rebuilding indexes update statistics?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Aug 9, 2011.

  1. Luis Martin Moderator

    "Since auto update statistics could happen even during peak hours, I prefer to have an off peak time job to update stats."

    I agree. Full in weeks ends and full for some tables in between.
  2. FrankKalis Moderator

    Aah, now I understand. Sorry, sometimes it takes me a little bit longer... :)
  3. Trev256b Member

    hi frank - handy links - thanks. ok - i'm refining everything so hopefully will get the perfect setup in 20 years! the more i delve the more i become addicted to the details!!!

    my setup is this: databases set to auto-update-stats; performing index rebuilds on indexes that are fragmented over 5% daily, however, i spotted that some procedures were cpu intensive - so it looks like the stats were not up-to-date. not sure why - maybe as some indexes may not be fragmented and the index rebuilding should update stats and recompile the procedure in procedure cache - perhaps the stats slowly get out of date without index being fragmented...? 1) any ideas? could there be some objects in the database that don't have stats updated via index rebuilds? Preethi mentioned there are stats not linked to indexes which don't get updated via index rebuilds - 2) how can i determine what these are and if they exist?

    3) Therefore as stats may be out-of-date should i run a weekly update stats as well as intelligent index rebuilds? Especially as indexes are only rebuilt if fragmented. My feeling is yes and test if stats are good and procedures are in cache and being re-used, and importantly the cpu is not used to the max!

    How does this sound? A good approach? I'd prefer to have an intelligent way to update the stats but don't know of one yet... 4) any ideas? i'm thinking of finding highest cpu queries and running sp_updatestats with full scan on the related tables - but can't find elegant way to do this automatically.
  4. FrankKalis Moderator

    Not sure about blindly rebuilding indexes that are fragmented > 5% daily, but if you can afford this without significantly affecting performance, then why not?
    At the end of the day it all comes down to the simple "It depends" answer anyway. What works great in one environment, might be a complete overkill in another or turn out pretty useless. You have to find out yourself, what is best in your case. And this will vary from database to database anyway. Generally I'm not sure if I would prematurely optimize things before I have to and someone complains about poor performance. :)
  5. Trev256b Member

    hi frank - all the dbas i've ever met say that you should regulalrly rebuild or reorg indexes.
    1) what are u suggesting? are u saying some sort of preformance testing before and after should be done?
    2) if so what exactly? perfmon, sql profiler, showplan, or checking cpu intensive queries?

    i do have one system that has poor peformance - normally i make sure the usual maintenance is in place and then see where the exact perfomance problem lies and resolve it.

    i am interested in what u might suggest. i rebuild indexes where fragmentation is >5% as it is essential to maintain performance - we do this overnight during non-user window so does not affect performance when running it.
    3) are u saying rebuilding indexes may reduce performance after they are rebuilt? if so how?

    4) also i have an issue with the update stats - i have been advised to run this to ensure all stats are up-to-date weekly. however i am concerned a few databases have too many indexes and/or stats. do you have a handy link of points to check on how to optimise in dexes and stats? i haven't done this in ages so any tips and dmvs would be very useful.
  6. FrankKalis Moderator

    Well, essentially I guess I am suggesting not to rely on other suggestions, but rather make his own experience and draw the right conclusions from them. There is nothing absolute here. For some systems a fragmentation of 5% might have an effect on performance. Other systems might work well with fragmentations of 30% or above. I am beginning to think that you should rather sit back, relax, and think about what you are really trying to achieve. Maybe something like Qure from http://www.dbsophic.com/ could be a good start to get an idea on overall performance of a system. Once you get the big picture, you can drill down into the fine tuning of certain aspects of the system.
  7. preethi Member

    We do rebuild or reorg the indexes periodically. but they are not based frequency, but on fragmentation and size.
    1. If the fragmentation level is smaller (We have our own threshold which I do not want to push to you) we go with Reorg.
    2. Small indexes could be rebuilt during off peak time of weekdays.
    3. Indexes on large tables (Or wide indexes) which may take longer time to rebuild/reorg we do it during week ends
    Recently I saw an article from Paul Randal, where he says reorg cannot be rolled back. He suggests that we can use that to reorganize the table on chuck by chuck basis. Good article to try out.
  8. preethi Member

  9. satya Moderator

    Trev
    I believe the answers here are really pointing out to the solution but your question is not clear enough to clarify further, you need to comeup with better explanation of hardware, databases (number), tempdb settings and SQL Server version (service pack levels) to get further information on the matter.
  10. mmarovic Active Member

    Just one more thing. In general, you should care about index fragmentation and statistics of large tables only. Rebuild indexes on smaller tables when they outgrow the status and become large :)
  11. MichaelB Member

    I know this is an old thread.. sorry.. been away and busy lately.
    always remember... too many stats will confuse the optimizer. if you have autocreate stats on a heavily used OLTP system, then I would recommend deleteing all the WA_ stats, then running with autocreate on for one or two weeks to capture standard workload, then turning it off. Maintain those indexes with some FULLSCAN and enjoy good performance. I have had systems that had many many WA_ autocreated stats and when we deleted them , performance dramatically increased. actually had one where performance was suddenly terrible with a table, removed those old stats then BAM! great perfomace returned. ahh the wonder of SQL Server..!
  12. Luis Martin Moderator

    Interest. Really never cross my mind. Will try in some clients.
  13. MichaelB Member

    Yup. done it more than once to fix an issue:)
  14. Trev256b Member

    Hi guys - quick update:

    Satya - I found out the high cpu queries was not related to stats not being updated - so no worries there - just too many processes running for the cpu!

    Michael B - What does WA_stats stand for? can't find this.

    Frank - I'm keeping the fragmentation down as it is necessary to keep it low and the tables fragment a lot.
  15. Luis Martin Moderator

    _WA_ are statistics created by SQL to improve performance.
    Using SSMS, databases, yourdatabase, anytable, statistics you should find it.
  16. satya Moderator

    As Luis referred these are created/managed automatically by the optimizer when AUTO UPDATE STATISTICS part is processed.
    What is see here is you are jumping in to too many conclusions, in order to get clear idea explain what kind of performancwe slow down you are suffering on this system. Lets take one problem at a time.

Share This Page