sp_updatestats and the default sampling rate

  • Using SQL Server 2008R2

    As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue with sp_updatestats (after re-indexing part of maint step) using the default sampling rate (which potentially can skew the performance) bothers me.

    It has been a discussion earlier at that link: http://www.sqlservercentral.com/Forums/Topic1310877-146-2.aspx,

    but I still confused about using sp_updatestats

    Currently I am performing the following steps during weekend maintenance:

    1. ALTER Database MyDB

    SET RECOVERY Simple,

    AUTO_CREATE_STATISTICS OFF,

    AUTO_UPDATE_STATISTICS OFF

    2. My index maintenance routine based on the following criteria:

    Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 500.

    Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes.

    So at the end of the Reindex Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.

    3. Currently I am running the Update Statistics on a whole database after previous reindex step:

    sp_updatestats

    Since sp_updatestats updates statistics by using the default sampling rate, it possibly can deteriorate all my indexes after running reindex routine.

    3A. So I was thinking about “… update statistics for all tables which are defragged and don't run update statistics for all tables which are REBUILD”

    http://sqlserverpedia.com/wiki/Updating_Statistics

    So here is my logic for performing routine in 3A

    Select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were either not updated for the last, say, few weeks and run the following:

    UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE against indexes selected above.

    By running above I will be able to update statistics without running sp_updatestats

    4. ALTER Database MyDB

    SET RECOVERY Full,

    AUTO_CREATE_STATISTICS ON,

    AUTO_UPDATE_STATISTICS ON

    Please let me know if you have any comments, suggestions, recommendations on step 3A.

    It has been a suggestion earlier to run:

    USE dbname;

    EXEC sys.sp_MSforeachtable

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

    But unfortunately it takes way to long on my Production Database, given the time frame I have for the weekend maintenance.

  • Hi,

    sp_updatestats makes the statistics update on sampling max 20% of the data. For big tables AUTO_UPDATE_STATISTICS *does not* function well. In that case you'll need to enable trace flag 2371, but it will withdraw more recompilation of queries (you should experiment with this trace flag, the public thinking is positive on it). Additionally your database is big, and this flag could probably be of asset for your database.

    Rebuild automatically means update of the stats with FULLSCAN, so you don't need any kind of update stats for the rebuilt indexes.

    After REORGANIZE, it is better (if you have time,resources) to perform UPDATE with FULLSCAN. But if you do REORGANIZE on big indexes and then update their stats, then you'd better check the time for only REBUILD them regardless they have #frag<30%. You could make your algorithm here more flexible.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • Thank you,

    Yes - I am planning to select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were not updated for the last 2 weeks and run the following:

    UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE instead of running sp_updatestats on entire Database...

  • I would consider running sp_updatestats right before index rebuilds or custom stats update jobs.

    It is fast, catches bits that rebuilds don't catch and any full-scan stats updates will overwrite the sampled stats from sp_updatestats.

    Best of both worlds (kinda):-D

    Cheers,

    JohnA

    MCM: SQL2008

  • Thanks for your reply.

    I am running reindex (reorg and rebuild) in parallel threads (in multiple jobs) simultaneously, so I cannot run sp_updatestats before reindex step

  • Gurus, Any more suggestions?

  • Anyone else?

  • inHouseDBA (3/18/2013)


    Anyone else?

    Yes. You didn't mention what you're doing to reestablish the log chain after you break it by setting the DB to SIMPLE recovery mode. I'm just trying to make sure that you're ok there.

    Shifting gears back to the original problem, which edition of SQL Server are you running? If you're running the Enterprise edition, you might be able to avoid rebuilding or even defragging the indexes on certain large parts of the table by using table partitioning. That will give you more time to do the full scans on the parts of the large tables that have actually changed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • inHouseDBA (3/12/2013)


    Gurus, Any more suggestions?

    1) My suggestion is that you get a professional on board for a few days to completely review and improve your entire maintenance process. Managing a TB sized database properly is almost certainly not optimal if you don't have some serious training and experience doing it already. Get a mentor to a) get things done right and b) teach you how/why it is so. Win-Win.

    2) if you aren't using it, I HIGHLY recommend ola.hallengren.com's AWESOME, FREE and FULLY DOCUMENTED maintenance suite

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks Jeff,

    I have over 20 steps in the Sunday Maint Plan.

    Among them:

    Backup before Maint, put DB in Simple Recovery, Disable auto update statistics, dbcc checkdb, Reindex, Update Staisticsts, put DB in a Full Recovery, Post maint backup, etc.

    Unfortunately we have SQL Server 2008R2 Standard, so we cannot use partitioning as of now. So it is maybe another year before we will start looking into partitioning.

    My Reindex routine is pretty robust, and I need to enhance my Statistics step now.

    Can you please comment on the following part:

    After Reindexing Step is complete, I am selecting indexes which were only "REORGANIZE"d (no statistics update) during Reindex Maintenance along with other indexes, where statistics were not updated for the last 2 weeks and I am running the following:

    UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE on the individual Indexes instead of running sp_updatestats on the entire Database.

    Does it make sense?

    Once Statistics step is completed I turn on automatic statistics update on the entire Database (SET AUTO_UPDATE_STATISTICS ON)

    Thank you

  • One more thing to keep in mind, you may have statistics on columns that are not in indexes. Make sure those are being updated. Your plan could potentially skip these statistics.

    I agree with Igor's post. Here's some info on the trace flag: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx

  • Thanks Guys for your input,

    Igor,

    can you please elaborate on ...if you do REORGANIZE on big indexes and then update their stats, then you'd better check the time for only REBUILD them regardless they have #frag<30%

    Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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