Reclaiming Space After Column Data Type Change
-
Comments posted to this topic are about the item Reclaiming Space After Column Data Type Change
-
We do a weekly rebuild of our index set.
-
Awesome article, thanks!
For the free sql server version limited to 10 GB, Alter becomes a problem. I've gotten into the routine of always copying a table to an empty database, dropping the original table, and then creating the new and pulling in the data from the holding database. I've got scripts which automate the whole process. This keeps me from getting aborts due to the 10 GB limit.
Actually I've always suspected "Alter" was smoke and mirrors due to the space usage and have avoided it from the start.
-
Great article, thanks.
qh
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font] -
That was an awesome article!
-
Just ran across this article... I agree! Very nicely done, especially with the page-level proofs!
As a bit of a sidebar, the reason why such a small change caused the Clustered Index to initially double in size is because it caused every row to grow a little. Since the pages were as full as they could get, it didn't take much to cause every page in the index to split. Here's what that all looks like... notice the fragmentation percent and the page density... (and I used the DBO schema in a "scratch" database for the test).
Here's what sp_IndexDNA™ looks like for that index (all the pages of the index are about half full).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply