SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reorganze index and Statistics


Reorganze index and Statistics

Author
Message
EL_oh_EL
EL_oh_EL
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 190
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215264 Visits: 46270
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


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37742 Visits: 14411
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
EL_oh_EL
EL_oh_EL
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 190
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37742 Visits: 14411
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215264 Visits: 46270
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


Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37742 Visits: 14411
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
Erin Stellato
Erin Stellato
Old Hand
Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)Old Hand (368 reputation)

Group: General Forum Members
Points: 368 Visits: 411
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/
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37742 Visits: 14411
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215264 Visits: 46270
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search