March 26, 2013 at 10:39 pm
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.
March 27, 2013 at 3:20 am
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
March 27, 2013 at 9:43 am
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
March 27, 2013 at 10:43 am
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.
March 27, 2013 at 12:05 pm
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
March 27, 2013 at 12:37 pm
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
March 27, 2013 at 1:32 pm
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
March 28, 2013 at 7:19 am
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/
March 28, 2013 at 7:31 am
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
March 28, 2013 at 8:09 am
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
March 28, 2013 at 8:53 am
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
March 28, 2013 at 10:34 am
Thanks but bulk logged wont work for us. Also, we are use log shipping to another datacenter.
March 28, 2013 at 10:54 am
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?
March 29, 2013 at 7:37 am
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