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 123»»»

VLDB complete index rebuilds - a little validation needed Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 9:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:58 AM
Points: 324, Visits: 677
ok, first of all, VLDB is relative. it's a 1.4TB database so that's VLDB for me. ;)

so what i'm tasked to do it shrink a large database and part of the process is to rebuild all indexes afterwards in order to defragment the database caused by the shrink process. i know, i know, you should never shrink but in this case we must. the database was oversized in the first place (about 2x larger than needed) and then we compressed the database during the 2008 -> 2008 R2 upgrade so now after compression and the oversizing, we are running with about 40% space used in the db, even after several years of growth. the db is running on a SAN and they (managers) want to reclaim this (expensive) SAN space for other uses. (plus multiply this database by about 6 between TEST, DEV, QA, and other system copies). i've already run the shrink process on one of the smaller DEV environments but not the reindex part (missed that point at the time). the shrinks alone took 16 hours so we are looking at an all weekend job.

SOOOO...i'm getting ready a SQL jobs broken down like this:

0 - backup the database :)
1 - set the db in simple recovery mode
2 - turn off disallow page locks on certain tables
3 - do the shrinks, one datafile at a time (with emails in between each to chart our progress over the weekend)
4 - reindex all
5 - turn on disallow page locks on certain tables
6 - set the database in full recovery mode

firstly, anyone see anything wrong with this process? just to doublecheck my thought process.

secondly, a question about the ALTER INDEX commands. there are 78,000+ tables. i ran a script to generate all the index rebuild commands like this:

ALTER INDEX ALL ON dbo.TABLE1 REBUILD
ALTER INDEX ALL ON dbo.TABLE2 REBUILD
ALTER INDEX ALL ON dbo.TABLE3 REBUILD
...
ALTER INDEX ALL ON dbo.TABLE783863 REBUILD

i'm setting my database in SIMPLE mode because AFAIK index rebuilds are logged transactions so i don't want to blow up my system for lack of space in the dblog file. when SQL rebuilds a large index (say 70GB, my largest table) are the entire index transactions written to the dblog? or is it broken up by checkpoints? in other words, do i need to be concern with the dblog space during the index rebuild process?

thanks for any advice!





Post #1521175
Posted Monday, December 9, 2013 2:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 11:09 AM
Points: 989, Visits: 1,329
What are you doing to manage the transaction log? If your database is in Full recovery mode you should have a job that performs a transaction log backup.

Also would you consider just rebuilding the indexes on those fragmented indexes > than some percent of fragmentation?

Just some random thoughts here.

Good luck.
Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1521305
Posted Monday, December 9, 2013 3:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:30 AM
Points: 322, Visits: 983
The transaction log will need to be at least as large as your largest table.


Post #1521330
Posted Monday, December 9, 2013 3:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:30 AM
Points: 322, Visits: 983
How much space are you leaving in the datafiles?
You also have to leave room in the data files for the index rebuilds.
Post #1521332
Posted Tuesday, December 10, 2013 2:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:58 AM
Points: 324, Visits: 677
Kurt W. Zimmerman (12/9/2013)
What are you doing to manage the transaction log? If your database is in Full recovery mode you should have a job that performs a transaction log backup.

Also would you consider just rebuilding the indexes on those fragmented indexes > than some percent of fragmentation?

Just some random thoughts here.

Good luck.
Kurt

thanks for the random thoughts. :)

first step is to put the database in SIMPLE mode and do a full backup.

i normally use the scripts from ola.hallengren.com which checks the level of fragmentation but in this case, this is an SAP database and we do everything according to their best practices, and a reindex of all tables is recommended. secondly (if i understand ola's scripts correctly) the frag check step requires to read the entire table to determine the fragmentation, which adds extra time and i/o. we have a narrow maintenance window and are unsure about the timing.



Post #1521424
Posted Tuesday, December 10, 2013 2:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:58 AM
Points: 324, Visits: 677
arnipetursson (12/9/2013)
The transaction log will need to be at least as large as your largest table.



thanks, that was my assumption.



Post #1521425
Posted Tuesday, December 10, 2013 2:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:58 AM
Points: 324, Visits: 677
arnipetursson (12/9/2013)
How much space are you leaving in the datafiles?
You also have to leave room in the data files for the index rebuilds.

planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!



Post #1521426
Posted Tuesday, December 10, 2013 3:12 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
OLDCHAPPY (12/10/2013)
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!


Free space in the data file should be about the size of the largest index. In simple or bulk-logged recovery, the log space needed is less than the size of the index, much less.

You're breaking the log chain to do this, is that a good idea?



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 #1521432
Posted Tuesday, December 10, 2013 3:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:58 AM
Points: 324, Visits: 677
GilaMonster (12/10/2013)
OLDCHAPPY (12/10/2013)
planning to leave about 10% free space so will check that it covers the space for index rebuilds. thanks to note that!


Free space in the data file should be about the size of the largest index. In simple or bulk-logged recovery, the log space needed is less than the size of the index, much less.

You're breaking the log chain to do this, is that a good idea?


do you mean this?


0 - backup the database :)
1 - set the db in simple recovery mode


need to ensure we can recover to the point prior to the "shrink/rebuild process" and restart backups cleanly in full mode after. system will be stopped before the first backup, so no online operations.

i guess i'm a little confused on the backups and switching between full mode and simple mode and back again.






Post #1521437
Posted Tuesday, December 10, 2013 3:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,115, Visits: 3,239
OLDCHAPPY (12/9/2013)


ALTER INDEX ALL ON dbo.TABLE1 REBUILD
ALTER INDEX ALL ON dbo.TABLE2 REBUILD
ALTER INDEX ALL ON dbo.TABLE3 REBUILD
...
ALTER INDEX ALL ON dbo.TABLE783863 REBUILD



Here I think it's good to remind you on the options for the INDEX REBUILD

 ... (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
COMPRESSION = PAGE)
ON [Index_FG]

because you said you run a script to generate the indexes definitions. Many scripts do not script indexes well, especially regarding these options. You said you'd run compression, and etc.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1521439
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse