Questions on Index Rebuilding (not reorganize)

Question

Questions on Index Rebuilding (not reorganize)

chewychewy

Hall of Fame

Points: 3126
More actions
June 3, 2013 at 6:07 am

#278131

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!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

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 Hall of Fame Points: 3126 More actions · Answer 2

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 Hall of Fame Points: 3126 More actions · Answer 3

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSCertifiable Points: 5777 More actions · Answer 5

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 Hall of Fame Points: 3126 More actions · Answer 6

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 SSCertifiable Points: 5777 More actions · Answer 7

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 Hall of Fame Points: 3126 More actions · Answer 8

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 10

It's worth noting that ALTER INDEX........WITH (ONLINE = ON) while it's not a magic button, it is an excellent option though.

When rebuilding indexes online, short lived, shared locks are still taken at the beginning and end of the rebuild process.

Regards

HanShi SSC-Dedicated Points: 33507 More actions · Answer 11

chewychewy (6/4/2013)
Thanks. In the url it stated that when rebuilding offline, select statement still can be performed on the table.

As the url stated, this only applies to NON-clustered indexes. When rebuilding a CLUSTERED index offline, a select on the table will be blocked.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

dileep1911 Grasshopper Points: 15 More actions · Answer 12

Hi,

Could you please send me the scripts to how to find fragmentation and removing.

Regards

Dileep

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 13

We have lots of scripts to find fragmentation:

http://www.sqlservercentral.com/search/?q=fragmentation&t=s

chewychewy Hall of Fame Points: 3126 More actions · Answer 14

chewychewy

Hall of Fame

Points: 3126

June 9, 2013 at 6:01 pm

#1622293

thank u all for ur kind help!