April 9, 2009 at 5:02 am
Hi all,
I was reading the BOL "Disk Space Requirements for Index DDL Operations " to understand how much space i need to create a index. But have some doubts :
The BOL Says :
"Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits"
In example the table was a heap and alread have 2 nonclustered index and will add a clustered index. Ok...The RID of nonclustered index will be change by Clustered Key
Question 1
But if was another nonclustered index (my table still a heap).Do i need space for both structures yet ? (old and new)
Question 2
But if was another nonclustered index and the table already have a clustered index. Do in need space for both structures yet ? (old and new)
Another doubt : BOL SAYS
Disk Space Calculations for an Online Clustered Index Operation
When you create, drop, or rebuild a clustered index online, additional disk space is required to build and maintain a temporary mapping index. This temporary mapping index contains one record for each row in the table, and its contents are the union of the old and new bookmark columns.
To calculate the disk space needed for an online clustered index operation, follow the steps shown for an offline index operation and add those results to the results of the following step.
Determine space for the temporary mapping index.
In this example, the old bookmark is the row ID (RID) of the heap (8 bytes) and the new bookmark is the clustering key (24 bytes including a uniqueifier). There are no overlapping columns between the old and new bookmarks.
Temporary mapping index size = 1 million * (8 bytes + 24 bytes) / 80% ~ 40 MB.
OK..but if was a nonclustered index..my table stiil a heap . In my mind i think(in this example) it is s only i take off "+24 bytes"..
Its correct ?
Thanks a lot
April 9, 2009 at 8:26 am
April 9, 2009 at 8:55 am
HI, i try to delete this post and change to performance and tuning...does not work so i change this post to Performance and tuning
http://www.sqlservercentral.com/Forums/Topic694079-360-1.aspx
Please reply in this link
April 9, 2009 at 9:06 am
LOL, I just closed that one, saw it as the dup first. Apologies. People tend to scan all threads, not the forums directly, so it's not that your post hasn't been seen.
From my understanding.
If you have a heap, you have one structure. If you create a clustered index (CI), you need the
- heap
- space for the CI
If you have a heap, and a Nonclusterd index (NCI), and you create a CI, you need
- heap space (old data)
- old NCI (still exists)
- CI (new structure)
- new NCI (new index structure)
Anytime you need to actually build a new index, I believe that you need space to allow the changes to all indexes to occur without affecting the original structures in case there is a rollback.
April 9, 2009 at 9:22 am
No problems Steve. I apologize...i think may i post in the wrong place.
But Steve..The principal doubt is
"Whenever an index is created, rebuilt, or dropped, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits"
So i think if i have a heap and i create a CI ok..the old structure is all modified (RID to Clustered KEY)..so i need space from old and new structure..
But if a have a table with CI and i want to create a new NIC...the old structure change too ?
I both cases i need spaces from old and new structure ?...
April 9, 2009 at 9:27 am
There is no old structure for the CI.
Obviously you'll have the CI space already used, this isn't touched. You need space for the new NCI to be created.
If you alter an NCI, then you need space for the old and new NCIs, the CI or heap space is still there as well.
April 9, 2009 at 9:30 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply