Index rebuild online

Last Post 21 Apr 2009 08:36 PM by trans53. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
20 Apr 2009 04:19 PM
Hi guys,


i have this database with the following files:

dW_data_1.MDF 10 GB
dW_data_1.NDF - 13 GB
dW_data_2.NDF - 12 GB
dW_indexes_data_1.NDF - 54 GB
dW_indexes_data_2.NDF - 54 GB
dW_load_data_1.NDF - 155 GB
dW_load_data_2.NDF - 155 GB

One of the huge tables has a clustered index with 130 GB of data and it's split between dW_load_data_1.NDF and dW_load_data_2.NDF datafiles.

The total disk size is 500 GB and the database size is 410 GB.

The tempdb for this server is completly on the separate disks with 60GB available for data and 60GB available for tempdb transaction log.


We have this maint. job which performs index rebuild online or reorg based on the fragmentation level.
The question is that i am not able to do online index rebuild for this clustered index with 130GB because there is not enough disk space i guess.

We also can't add more space to this disk array but we can add another array (i did not calculate yet what the size it should be) and i think we can split data files between these two arrays.

2 questions:

What can you recommend to move to the second array?

And if we do index rebuild online for 130 GB clustered index (it's based on one datetime column) do you think 60GB tempdb is sufficient?
Does it use tempdb at all when the index rebuild online?

Thanks
trans53
New Member
New Member

--
20 Apr 2009 10:44 PM
Thanks Andrew, the only concern i have right now is that 60GB for tempdb may not be sufficient for 130GB clustered index when sort_in_tempdb is set to on. This is the part that i don't undertand completly how to plan for compacity for this kind of big index.
trans53
New Member
New Member

--
21 Apr 2009 02:39 PM
Sounds good, thanks again.
Another question. This table also has 2 non-clustered indexes 80 gb in size combined.
So if the clustered index rebuild online should i consider adding 80gb to 130gb when i will calculate for the space needed?
trans53
New Member
New Member

--
21 Apr 2009 08:36 PM
Each non clustered index has one column defined on it,it's just this table has more than 1 billion rows.
trans53
New Member
New Member

--
22 Apr 2009 10:38 AM
Fill factor for each index is 70 and fragmentation is 0.
i think it maybe makes sense to increase fillfactor.
You are not authorized to post a reply.

Acceptable Use Policy