Post reply

Ruuning Update Stats

  • June 11, 2012 at 6:30 pm

    #1499566

    Part of out main application has been build by 3rd-party vendor and only recently (less then a year ago) they became SS2K8 certified.

  • February 6, 2013 at 4:41 pm

    #1585162

    Hello again,

    As my Production database approaching 400GB, issue with sp_updatestats using the default sampling rate (which potentially can skew the performance) bothers me.

    Running below takes way too long:

    USE dbname;

    EXEC sys.sp_MSforeachtable

    @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';

    So how about performing the following during weekend maintenance:

    1. ALTER Database MyDB

    SET RECOVERY Simple,

    AUTO_CREATE_STATISTICS OFF,

    AUTO_UPDATE_STATISTICS OFF

    2. My index maintenance routine.

    3A. Selecting indexes which were "REORGANIZE"d (no statistics update) during maintenance along with other indexes, where statistics were either not updated for the last few weeks or where statistics don't exist.

    3B. Run UPDATE STATISTICS Table_Name WITH FULLSCAN, NORECOMPUTE against indexes selected above in 3A.

    4. ALTER Database MyDB

    SET RECOVERY Full,

    AUTO_CREATE_STATISTICS ON,

    AUTO_UPDATE_STATISTICS ON

    By running 3A and 3B I will be able to update statistics without running sp_updatestats.

    Does it make sense?

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply