SQL Server Performance

Stats out of date

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jinugeorge05, Oct 25, 2007.

  1. jinugeorge05 New Member

    I have a pretty huge job which run normally for 1hr max on a SS2000. Lately, after bringing over the database to SS2005,the job goes running over 1day!!This job performs quite a lot of updates on a particular table.We updated the stats and the next day it completed in 45 mins!!
    The next 2 days after that, the job hung again. The autoupdate stats for the indexes in that table is turned on.And I can see a few stats getting updated(EXEC sp_autostats) and some others aren't (even though autostats are turned on for the table and the database).To fix it I had to run the update stats(UPDATE STATISTICS <table> WITH FULLSCAN,ALL) and run the job.
    I can't be doing a manual stats update everyday. Has any one come over this issue where SS2005 does not update stats on their server? I am on SS2005 enterprise with SP2.
  2. techbabu303 New Member

    How is fragementation leve of indexes used in tables ?
    How big are these table which gets updated by the job ?
    To check fragementation use :
    Uses DBCC SHOWCONTIG <table_name> , check to see
    Check for logical scans <20-30% applies larger tables.
    Regards
    Sat
  3. jinugeorge05 New Member

    There are 5 indexes in this table..One Server which is a replica of the problematic server works fine. That has the same frag levels and the table is pretty big consisting of 8547842 rows. The day I manually update the stats, the job works fine.The next day it gets messed up again and Sql Server doesn't bother to do an updatestats, despite turning them on.
  4. techbabu303 New Member

    Are client request timing out when this job runs ?
    If yes You should consider setting the AUTO_UPDATE_STATISTICS_ASYNC option ON , but first I would rebuild indexes before trying this option.
    Reference :
    http://msdn2.microsoft.com/en-us/library/ms190397.aspx
    Regards
    Sat
  5. satya Moderator

Share This Page