none
Index Maintenance

    Question

  • Hi,

    Yesterday when ran Statistics update on 2 TB data (Windows Server 2008 R2), it's took almost 6 hrs.

    But some time it is not acceptable ton PROD environment, in this case what is the proper way to update statistics and index rebuild/reorganize on such a big DB's.

    Saturday, March 29, 2014 3:55 PM

Answers

  • Hello,

    The proper way would be to only update statistics and reorg/rebuild the indexes that need it. Depending on the databases, different levels of statistics skew and index fragmentation are acceptable. You'll need to watch your database and decide what those levels are and then act upon that data. If you're using maintenance plans, stop. They will rebuild/update/etc every item in the database which is a sledgehammer approach... you can write your own or use Ola's scripts, but either way one size does no fit all... you'll need to watch your DB and make decisions based on your environment.


    Sean Gallardy | Blog | Twitter

    Saturday, March 29, 2014 4:11 PM

All replies

  • Hello,

    I guess with such a big table time required will be high.I also assume you are updating stats during maintenance window or when load is relatively less on database.If not you can consider this ,this can reduce further time.Just to try you can use Ola's script to see if it makes difference

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, March 29, 2014 4:08 PM
  • Hello,

    The proper way would be to only update statistics and reorg/rebuild the indexes that need it. Depending on the databases, different levels of statistics skew and index fragmentation are acceptable. You'll need to watch your database and decide what those levels are and then act upon that data. If you're using maintenance plans, stop. They will rebuild/update/etc every item in the database which is a sledgehammer approach... you can write your own or use Ola's scripts, but either way one size does no fit all... you'll need to watch your DB and make decisions based on your environment.


    Sean Gallardy | Blog | Twitter

    Saturday, March 29, 2014 4:11 PM