SQL Server Performance

SQL Server 2005 performance suffers during Auto Update Statistics process

Discussion in 'SQL Server 2008 General DBA Questions' started by swhaley, Sep 10, 2009.

  1. swhaley New Member

    I have a production SQL Server 2005 SP2 instance hosting over 150 databases. Every day or so the server performance slows considerably for all SQL services. Upon inspection I see a dozen or so threads of ‘UPDATE STATISTICS’ running with many in a wait state. If I kill the sessions(s) running the statistics the normal performance returns. I know that statistics play an important role in determining execution plans, but should I disable auto update statistics for all databases and run the update statistics manually during off-peak times? Will this prevent the update from running automatically? Is there a way to limit the number of threads the update statistics uses? Thanks for any advice on this subject.
  2. moh_hassan20 New Member

    Welcome to the forum.
    One of the most advices in sql server 2005 /2008
    Auto statistics for datbase should be ON (or if you turned them off - turn them back on [;)] )
    if you are sure that auto statistics may be a performance problem (for large tables ), turn off auto update at the index /table level NOT at the database level, and schedule updating them during off-peak using sp_autostats
    In case of bulk insert data or bcp or delete /update huge data , you should update statistics manually after the operation
    You can use AUTO_UPDATE_STATISTICS_ASYNC ON, under certain conditions, for details:
    review: http://www.sql-server-performance.com/articles/per/asynchronous_statistics_p1.aspx

Share This Page