Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reorganze index and Statistics Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 10:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:23 PM
Points: 21, Visits: 182
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.

Post #1435763
Posted Wednesday, March 27, 2013 3:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
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 2008, MVP
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

Post #1435804
Posted Wednesday, March 27, 2013 9:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1435995
Posted Wednesday, March 27, 2013 10:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:23 PM
Points: 21, Visits: 182
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.
Post #1436034
Posted Wednesday, March 27, 2013 12:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1436079
Posted Wednesday, March 27, 2013 12:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
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 2008, MVP
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

Post #1436101
Posted Wednesday, March 27, 2013 1:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1436130
Posted Thursday, March 28, 2013 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:27 PM
Points: 30, Visits: 279
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/
Post #1436454
Posted Thursday, March 28, 2013 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1436462
Posted Thursday, March 28, 2013 8:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 41,524, Visits: 34,440
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 2008, MVP
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

Post #1436484
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse