creating clustered index, space utilization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » creating clustered index, space utilization

creating clustered index, space utilization

Rate Topic

Display Mode

Topic Options

Author

Message

NJ-DBA

Posted Thursday, August 26, 2010 6:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 858, Visits: 1,329

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

crazy4sql

Posted Thursday, August 26, 2010 6:40 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731

[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

John Mitchell-245523

Posted Thursday, August 26, 2010 6:54 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 4,425, Visits: 7,187

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

NJ-DBA

Posted Thursday, August 26, 2010 7:08 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 858, Visits: 1,329

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

NJ-DBA

Posted Thursday, August 26, 2010 8:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:49 AM
Points: 858, Visits: 1,329

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

John Mitchell-245523

Posted Thursday, August 26, 2010 8:30 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:51 AM
Points: 4,425, Visits: 7,187

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 »

Permissions