SQL Server Performance

UPDATE STATISTICS

Discussion in 'Performance Tuning for DBAs' started by piripi13, Feb 14, 2006.

  1. piripi13 New Member

    hi,

    Does UPDATE STATISTICS block or lock tables ?

    Ive heard alot on this subject with different opinions RE SCH-M, SCH-S locks etc so I'm not sure what is correct, there doesnt seem to be anything stated from microsoft on it.

    I have a 300+ GB DB with only a 4 hour maintenance window, not much, this 4 hours has to be spent REINDEXing what tables I can in a monthly schedule and Defragging the big daddy Tables.

    From what Ive read REINDEX will update the stats, so its more the big tables that get defragged I'm worried about.

    The server hardware should handle any performance hit from the UPDATE STATISTICS command but its the access to the actual tables that I'm concerned about.

    Any help would be great.

    thanks

  2. chaloner New Member

    Hi

    UPDATE STATISICS does not lock the table for which it is executed.The table is available to use.It makes dirty reads to get its info.

    cheers
    Parik****
  3. FrankKalis Moderator

  4. piripi13 New Member

    thanks guys,

    Just one more thing, I cant find out much info on the WA_SYS indexes or statistics.


    As REINDEXing a tablename will drop and create all indexes, is this how the stats actually get updated or is there some other procedure that SQL performs to UPDATE STATS after a REINDEX ?

    Would these take a long time to reindex on large tables or should I not worry about them ?
  5. chaloner New Member

    Hi

    Time taken would depend on the size of ur tables.Also while using DBCC REINDEX your tables would not be available to users.It is an offline method.But as indexes are dropped and created the statistics are updated.

    You can use DBCC INDEXDEFRAG.This is an online method.
    more information on
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_30o9.asp

    But i am not sure if DBCC INDEXDEFRAG updates statistics automatically.



    cheers
    Parik****
  6. piripi13 New Member

    thanks

    I'm quite sure indexdefrag doesnt update stats, was just wanting to know which part of the reindex statement actually updates the stats

    Basically I will be doing REINDEX on all tables apart from those that will take over the maintenance window which will be defragged, I will then run update stats on those defragged tables.

    As update stats doesnt block I will also now try and sneak in sp_updatestats on a daily basis in the early hours of the morn after backups have completed. If i havnt got time for the whole DB, i'll just do a couple of the main tables

    Thanks for the swift responses
  7. abroadway New Member

    Indexdefrag does NOT update stats.
    You need to run:
    UPDATE STATISTICS TableName WITH FULLSCAN
    -- WITH FULLSCAN is an option

    Remember also:
    EXEC sp_recompile TableName
    You need to set the table that has updated stats for Recompile. Then ANY sproc that hits that table will recalc its execution plan with the new table Stats. THIS IS IMPORTANT as your sprocs might still work sloooowly. (And you will wonder why the same statement in Query Analyser is fast!)

    If you have a heap of tables that you would like to create Statements for Indexdefrag, Update Statistics and Recompile you could so something like this:
    select 'DBCC INDEXDEFRAG (DataBaseName, ' + NAME + ', PK_' + NAME + ')' from sysobjects where xtype = 'u'
    -- This assumes your Primary Key starts with PK_ and ends with the table name.

    select 'UPDATE STATISTICS ' + Name + ' WITH FULLSCAN' from sysobjects where xtype = 'u'

    select 'EXEC sp_recompile ' + Name from sysobjects where xtype = 'u'
    OH And remember too, if your DB is set to AUTOGROW and index pages become defragmented, why not GROW your DB to a very big size and then not have to put up with the pain of Defraging quite so often? (depending on your circumstances this can help a lot).
    Hope it helps.
    :)
  8. satya Moderator

    Appreciate your insight, but do you think any use of updating a 2 year old thread [:)].
  9. abroadway New Member

    Satya, I thought "why not!".
    You have some good SEO pointing to that topic and the extra info on updating stats and recompile might help someone out there searching on Google.
    Keep up the great work! This site is an excellent resource for DBA's.
    [:D]
  10. jake New Member

    Thanks abroadway that sp_recompile may just be the thing I was missing from my maintenance plans.
    Jake
  11. JohnStafford New Member

    Thank you all - the piece I was missing was running update stats before sp_recompile - so thanks to people who update posts even after two years!
    So the process I'm going to use is:
    1. Indexdefrag
    2. Update Staistics
    3. sp_recompile
    If only SQL Server would do this itself then I'd have more time for
  12. satya Moderator

    Well said abroadway.
    Not that as a weekly job, if you see there are few stored procedures that are accessed frequently and underlyting table has major insert/delete operations then it is better to run SP_RECOMPILE intermittenlty and also bear in mind [http://sqlserver-qa.net/blogs/perftune/archive/tags/too+many/default.aspx] too many recompile will hurt performance.
  13. robinpryor New Member

    Here's a kicker.... 8, yes 8 years after original post, here I am looking at this thread for information about updating stats.... don't judge. We're all in this together.
  14. Luis Martin Moderator

    Welcome to the forums:)

Share This Page