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

Questions on Index Rebuilding (not reorganize) Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 6:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:28 PM
Points: 93, Visits: 406
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!
Post #1459212
Posted Monday, June 3, 2013 6:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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
Post #1459233
Posted Monday, June 3, 2013 8:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:28 PM
Points: 93, Visits: 406
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
Post #1459317
Posted Monday, June 3, 2013 8:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:28 PM
Points: 93, Visits: 406
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!
Post #1459320
Posted Monday, June 3, 2013 8:39 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 @ 6:24 AM
Points: 42,422, Visits: 35,483
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 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 #1459324
Posted Monday, June 3, 2013 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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.
Post #1459375
Posted Monday, June 3, 2013 6:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:28 PM
Points: 93, Visits: 406
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?
Post #1459506
Posted Tuesday, June 4, 2013 12:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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
Post #1459559
Posted Tuesday, June 4, 2013 7:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:28 PM
Points: 93, Visits: 406
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?
Post #1460004
Posted Tuesday, June 4, 2013 11:34 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 @ 6:24 AM
Points: 42,422, Visits: 35,483
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 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 #1460031
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse