creating clustered index, space utilization

Question

creating clustered index, space utilization

NJ-DBA

SSChampion

Points: 13834
More actions
August 26, 2010 at 6:21 am

#226223

I have a db with a very large table:
rowsreserved_KBdata_KBindex_size_KBunused_KB
121575689130194824 KB12815296 KB116897216 KB482312 KB
There is no clustered index on the table and I need to create one, will fill factor 0.
How will this impact space utilization on disk? What about white space in the datafile?

Viewing 6 posts - 1 through 5 (of 5 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

There is no clustered index on the table and I need to create one, will fill factor 0.
How will this impact space utilization on disk? What about white space in the datafile?

1)

clustered index is physically sorting of data and sql server need extra space (1.2 times the space required for the entire table) to physically sort the data and create index.

you can use the option sort_in_temdb but better first understand about pros/cons of this option in BOL.

2)

not able to understand what do you mean by What about white space in the datafile?

----------
Ashish

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Creating a clustered index with fill factor 0 won't use any more space on disk (apart from any extra space that is consumed while the index is being built). If your table is heavily fragmented, you may even find that your space used goes down. Another factor to consider is non-clustered indexes - if you have any on your table. If you choose a wide clustering key then the size of your non-clustered indexes will grow since each row in the non-clustered index points to the clustered index using the clustering key.

John

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

Thanks for the input.

The table is extremely fragmented- so what I am hearing is what I suspected- even though I will gain space in the end, I still need 1.2Xs the size of the existing structures of free space in order to create the index. that makes sense.

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

So to clarify- the "Reserved space" for the table and index as show above is almost 130GB (most of that is "index space"... non-clustered). I should have at least 1.2 Xs 130GB free in order to safely create a clustered index?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

This page looks as if it has all the information you need:

http://technet.microsoft.com/en-us/library/ms191183.aspx

John