Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VLDB complete index rebuilds - a little validation needed


VLDB complete index rebuilds - a little validation needed

Author
Message
JarJar
JarJar
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 1021
ok, first of all, VLDB is relative. it's a 1.4TB database so that's VLDB for me. Wink

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 Smile
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!



Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 1396
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
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
The transaction log will need to be at least as large as your largest table.
arnipetursson
arnipetursson
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 1019
How much space are you leaving in the datafiles?
You also have to leave room in the data files for the index rebuilds.
JarJar
JarJar
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 1021
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. Smile

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.



JarJar
JarJar
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 1021
arnipetursson (12/9/2013)
The transaction log will need to be at least as large as your largest table.



thanks, that was my assumption.



JarJar
JarJar
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 1021
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!



GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47351 Visits: 44392
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, 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


JarJar
JarJar
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 1021
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 Smile
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.



Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 4849
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
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