SQL Server Performance

Update Stats with Full Scan

Discussion in 'Performance Tuning for DBAs' started by mishrsk, Oct 2, 2008.

  1. mishrsk New Member

    Hi,
    I am on SQL 2000 SP4 with latest SQL patches. I am running the UPDATE STATISTICS WITH FULL SCAN on all tables twice a week. The time taken on each run is different every time. On 23rd Sep it took 14.5 hrs, 28th Sep it tool 15 hrs, on 1st Oct it took 11 hrs. Checked the Locks/Blocks, CPU counters, Memory Counter and Waits at table levels but could not see a pattern for the behaviour. Have not done any re-org or re-indexing since 23rd Sep, however doing a Index Defrag everyday.
    Please could anyone let me know how to figure out what might be the reason.
    Thanks in advance..
  2. satya Moderator

    Welcome to the forums.
    When was the last time you have performed the REINDEX on all the tables in this database?
    I suspect there might be physical file fragmentation whereby the DEFRAG is unable to address it and you must run REINDEX to correct it, atleast once in a week during the maintenance window for the required tables only.
    How to Detect Table Fragmentation in SQL Server 2000 and 2005
  3. Luis Martin Moderator

    Other option is to update with full scan only in those heavy tables once a week, and all tables on weekends.

Share This Page