September 21, 2015 at 12:58 am
Hi,
I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
Why is this happening ?
September 21, 2015 at 2:37 am
Rebuild the clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 3:41 am
GilaMonster (9/21/2015)
Rebuild the clustered index.
What happens in this instance if there is no clustered index (i.e. a Heap)?
We always have clustered indexes, so its only curiosity to enquire about how space is reclaimed after a delete.
September 21, 2015 at 3:46 am
Kristen-173977 (9/21/2015)
GilaMonster (9/21/2015)
Rebuild the clustered index.What happens in this instance if there is no clustered index (i.e. a Heap)?
Then you need to rebuild the heap.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 4:20 am
Ah .. .so ALTER TABLE ... REBUILD rather than ALTER INDEX ... REBUILD ?
I had a look at BoL and it appears that ALTER TABLE ... REBUILD will rebuild clustered index if one exists, otherwise heap, so perhaps that might be useful if I didn't know (couldn't be bothered to check!) if the table had a clustered index.
Rebuilding heap is going to have to update all the non-clustered indexes (although if the purpose of doing it is to reclaim massive amounts of space following a bulk deleted I suppose that's fair enough), and the non clustered index are going to need rebuilding, after the heap, both because of the bulk delete and?? because of the heap rebuild perhaps?
September 21, 2015 at 4:43 am
Kristen-173977 (9/21/2015)
Rebuilding heap is going to have to update all the non-clustered indexes (although if the purpose of doing it is to reclaim massive amounts of space following a bulk deleted I suppose that's fair enough), and the non clustered index are going to need rebuilding, after the heap, both because of the bulk delete and?? because of the heap rebuild perhaps?
Rebuilding a heap will automatically rebuild all nonclustered indexes. It won't need to be done separately. Rebuilding a clustered index does not rebuild nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 4:56 am
GilaMonster (9/21/2015)
Rebuilding a heap will automatically rebuild all nonclustered indexes.
Wow, that's handy. Thanks for telling me because I would have assumed that was not the case and then done them all manually!!
I had assumed the Heap Rebuild was just going to update all the non-clustered indexes with the revised row-identifier and nothing else.
September 21, 2015 at 5:36 am
Kristen-173977 (9/21/2015)
I had assumed the Heap Rebuild was just going to update all the non-clustered indexes with the revised row-identifier and nothing else.
That would be extremely inefficient, to update every nonclustered index N times where N = number of rows in the table, instead of just recreating the entire thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 6:05 am
GilaMonster (9/21/2015)
That would be extremely inefficient, to update every nonclustered index N times where N = number of rows in the table, instead of just recreating the entire thing.
Never occurred to me, but makes sense now I hear you say it 🙂 Thanks, useful info as always.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply