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


Questions on Index Rebuilding (not reorganize)


Questions on Index Rebuilding (not reorganize)

Author
Message
chewychewy
chewychewy
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 491
Hi Guys,

Need some information on Index Rebuilding. (sort_in_tempdb = off)
When performing index rebulding (for indexes > 30% fragmented), will we expect to see an increase in the user transaction log size (LDF)? Or we will expect to see an increase in the database size instead (MDF)?

Anyway to calculate how much size needed for rebuilding indexes?

Let's say if i need to rebuild 3 indexes, each about 5 GB.
After rebuilding the first index, will the temporary space used for rebuilding the first index be reuse for the 2nd index? Or we first need to perform a transaction log backup before the space can be reused?

thanks!
e4d4
e4d4
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2397
Db file: can grow to accomodate new index before dropping old one, so it need a free space of size the largest index that is rebuild.
DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less
chewychewy
chewychewy
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 491
thanks.

Db file: can grow to accomodate new index before dropping old one, so it need a free space of size the largest index that is rebuild.
DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less


I supposed the above is when rebuilding online?
With the offline option, is it the same as above?

thanks
chewychewy
chewychewy
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 491
anyone can advise why it need to much space for transaction log? i thought the additional spaces required in the mdf file to store the additional index during time of rebuild is all that it need.

DB log: this depend on recovery mode, in FULL all changes (so 3x5GB+a little more) made during rebuild will stay in log until backup. In Simple/"Bulk logged" space used in log will be much less


thanks!
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: 47187 Visits: 44356
chewychewy (6/3/2013)
anyone can advise why it need to much space for transaction log? i thought the additional spaces required in the mdf file to store the additional index during time of rebuild is all that it need.


It's a fully logged operation, hence every single aspect of the change has to be logged in full recovery model, that means all the index pages get logged as they are allocated.


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


e4d4
e4d4
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2397
chewychewy (6/3/2013)...
I supposed the above is when rebuilding online?
With the offline option, is it the same as above?

thanks


No, in both case space for a new copy of index is required.
ONLINE = you can perform DML on the underlying table. OFFLINE = you can't perform DML on the underlying table.
For lowering log space usage (log backup size will be almost the same) you can consider the Bulk-Logged recovery mode, but first read about bulk-logged recovery mode.
chewychewy
chewychewy
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 491
Thanks all for the help.
One last question, in this case with rebuild offline, i guess select
statement wont get impacted since the old index is there when creating the new index? Btw any gd query to find a listing of indexes size?
e4d4
e4d4
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2397
No, OFFLINE = offline.

"Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation."
http://msdn.microsoft.com/en-us/library/ms188388%28v=sql.90%29.aspx
chewychewy
chewychewy
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 491
Thanks. In the url it stated that when rebuilding offline, select statement
still can be performed on the table. Will it be slower? As in will the old index still
usable while rebuilding going on for select statement?
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: 47187 Visits: 44356
When you're rebuilding offline, the index will be unavailable, the rebuild process takes a schema modification lock, which blocks all access to the table.


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