SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


creating clustered index, space utilization


creating clustered index, space utilization

Author
Message
NJ-DBA
NJ-DBA
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5284 Visits: 1576
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?
crazy4sql
crazy4sql
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7087 Visits: 4514
[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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53996 Visits: 16962
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
NJ-DBA
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5284 Visits: 1576
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
NJ-DBA
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5284 Visits: 1576
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
John Mitchell-245523
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53996 Visits: 16962
This page looks as if it has all the information you need:

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

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search