Reorganze index and Statistics

  • Looking for some clearer answers here...

    we have quite large databases 1TB+ and we are doing nightly Reorganize of our index. There is no job for statistics update. When should statistics update job should be scheduled for ? before or after the Reorganize of indexes? auto create and update statistics option are enabled for the database.

  • It doesn't matter. Before or after will have the same effect, the stats will be updated. Reorganise doesn't touch the stats.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Like I told you on the other thread, if you are concerned that auto-update stats is not doing a good enough job and you want to guarantee stats are updated along with your index maintenance then time an INDEX REORG plus an UPDATE STATS WITH FULLSCAN, and compare that with the time it takes to do a REBUILD to see which is faster. Are you having trouble with bad execution plans because of stale stats or are you just trying to cover your bases when it comes to stats?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks! what i am trying to accomplish is to update the stats since they are not being kept up. I have thought about rebuilding the indexes since some are pretty fragmented >50%. but one concern is that our tables are huge like over 10Million rows of data so transaction log growth can grow pretty crazy. Is it rebuilding an indexes recommended based on how fragmented it is? Our trans log backups are done every 15 mins.

  • At 50% I would rebuild. If you're having trouble with log size look into partitioning your table and indexes so you can rebuild smaller subsets of your index, ie only the partitions that need it.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/27/2013)


    At 50% I would rebuild. If you're having trouble with log size look into partitioning your table and indexes so you can rebuild smaller subsets of your index, ie only the partitions that need it.

    Or switch to bulk-logged recovery model if possible.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (3/27/2013)


    opc.three (3/27/2013)


    At 50% I would rebuild. If you're having trouble with log size look into partitioning your table and indexes so you can rebuild smaller subsets of your index, ie only the partitions that need it.

    Or switch to bulk-logged recovery model if possible.

    True. That will help keep log size down but could increase tran log backup sizes.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Transaction log backup sizes should be about the same size whether you're in FULL or BULK LOGGED recovery...

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model/

  • Erin Stellato (3/28/2013)


    Transaction log backup sizes should be about the same size whether you're in FULL or BULK LOGGED recovery...

    http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model/

    The article is more geared towards debunking the myth that BULK LOGGED recovery will reduce the size of the backups. The comment "roughly the same size" is ancillary to the point.

    Because there is more information being written to the log backup when BULK LOGGED operations are present than there would be when the logged activity was all carried out while in FULL recovery mode, namely the extent allocation log entries plus the data itself, the log backups will be larger in BULK LOGGED than in FULL for the same set of operations.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Log backup size in full and bulk logged recovery model will be roughly the same size.

    Full recovery the entire size of the data inserted goes into the log records and hence gets backed up, so it's data + log headers + some overhead. Bulk logged the modifed extents are included in the log backup whereas the amount logged is small, so the log backup has data + log records + some overhead. The sizes will vary, usually by small amounts. The log backup in bulk logged recovery is not going to be twice the size of the one in full or anything close to that.

    The tests Tony and I did writing the tran log book had the DB in full recovery model having a 1GB log and a 1GB log backup, the database in bulk logged having a 24MB log and a 1 GB log backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • opc.three (3/27/2013)


    GilaMonster (3/27/2013)


    opc.three (3/27/2013)


    At 50% I would rebuild. If you're having trouble with log size look into partitioning your table and indexes so you can rebuild smaller subsets of your index, ie only the partitions that need it.

    Or switch to bulk-logged recovery model if possible.

    True. That will help keep log size down but could increase tran log backup sizes.

    OK, maybe negligibly larger. I have not paid too much attention to the actual differences but know they are larger. With a ton of index rebuild activity I figured it would be noticeable but maybe not as much I figured.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks but bulk logged wont work for us. Also, we are use log shipping to another datacenter.

  • EL_oh_EL (3/28/2013)


    Thanks but bulk logged wont work for us. Also, we are use log shipping to another datacenter.

    Mind if we ask why it wouldn't work?

  • EL_oh_EL (3/27/2013)


    Thanks! what i am trying to accomplish is to update the stats since they are not being kept up. I have thought about rebuilding the indexes since some are pretty fragmented >50%. but one concern is that our tables are huge like over 10Million rows of data so transaction log growth can grow pretty crazy. Is it rebuilding an indexes recommended based on how fragmented it is? Our trans log backups are done every 15 mins.

    1) It is funny what people think is "huge" these days. I have a 131M row table on my laptop I use for data warehousing and column store index demos. 😎

    2) You cannot be successful managing a 1TB+ database on any RDBMS system without doing quite a few things right. Index mx and statistics updates are two of those things. The proper solution on HOW to manage those things best for YOUR SYSTEM cannot be determined without a lot more knowledge about your apps, data access/processing, mx window(s), SLAs, etc, etc. I could tell you things that would be general best practices that could totally fubar things for your system. I recommend you get a professional to help you determine what your needs really are and get things set up and mentor you on how to monitor/react to your systems needs.

    3) Large tlogs are part and parcel of managing a large database. Plan for it or suffer the consequences.

    4) Given log shipping to another datacenter you mentioned, are you compressing before shipping (assuming you have bandwidth/latency issues that usually come with that scenario)?

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

Viewing 14 posts - 1 through 13 (of 13 total)

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