SQL Server Performance

Update Statistics with full scan

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by WingSzeto, Jul 31, 2009.

  1. WingSzeto Member

    I have a daily scheduled job that uses the update statistics with full scan command on a few big tables. This job runs at early morning hour. It has been adequate and everything was fine. Lately I encountered a few queries that use these big tables starts running super slow. Based on the execution plan, instead of using a specific index for the search (index seek), it chose the clustered index scan instead. Upon I run the scheduled job again, then the execution plan shows it chose the specific index correctly again. For one of these big tables which has 13 million records, daily inserts are about 30,000 to 50,000 record. Daily update would be simliar or less.
    My question is that I understand optimizier has its own logic to determine when to update statistics, but I consider our % change to that big table is small (I could be wrong), is there something else wrong that I should look into instead of running the update statistics with full scan every time that query runs slow? By the way, does update statistics with full scan cause any locking problem besides creating high IO activity?
    We are using SQL 2008 std 64 bit on Windows 2008 enterprise 64 bit
    w
  2. imSQrLy New Member

    Try to force a seek. See link in BOL.
    http://msdn.microsoft.com/en-us/library/bb510478.aspx
  3. Luis Martin Moderator

    I use to run full update statistics once a week.
    Also I use to check null statistics.
    "By the way, does update statistics with full scan cause any locking problem besides creating high IO activity?"
    Yes. Run the job in windows time.
  4. WingSzeto Member

    Auto update statistics is a database level option as well as index level. We have the database level is set to auto update statistics but we also have a few tables which its indexes are set to 'NOT' recompute statistics. If I check the box, it will drop the index and then recreate it. Some tables are big and primary key is one of the indexes needed the changes as well. I can't afford to lock the table due to the drop and recreate of index especially the primary clustered index key. Is there a back door way to just enable the 'automaticaly recompute statistics' check box in the index properties using a sql statement without causing a drop and recreate an index?

Share This Page