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

creating clustered index, space utilization Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 6:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:59 AM
Points: 885, Visits: 1,427
I have a db with a very large table:

rows reserved_KB data_KB index_size_KB unused_KB
121575689 130194824 KB 12815296 KB 116897216 KB 482312 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?
Post #975564
Posted Thursday, August 26, 2010 6:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:43 AM
Points: 882, Visits: 4,144
[b]

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
Post #975573
Posted Thursday, August 26, 2010 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 5,488, Visits: 10,339
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
Post #975587
Posted Thursday, August 26, 2010 7:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:59 AM
Points: 885, Visits: 1,427
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.
Post #975599
Posted Thursday, August 26, 2010 8:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:59 AM
Points: 885, Visits: 1,427
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?
Post #975684
Posted Thursday, August 26, 2010 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 AM
Points: 5,488, Visits: 10,339
This page looks as if it has all the information you need:

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

John
Post #975692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse