January 12, 2012 at 3:55 pm
Side note. Make sure they run a DBCC CLEANTABLE on the table in question after the removal to deallocate the extents associated to BLOBs. It needs to be run before you drop the table if you decide to go the route of the table rebuild. It can be run anytime after if you do the BatchToNULL-BackupLog and then DROP. Just do it before the shrink for space recovery occurs.
Some further information on that in these discussions:
http://qa.sqlservercentral.com/Forums/Topic1011086-146-1.aspx#bm1011126
http://qa.sqlservercentral.com/Forums/Topic993883-146-2.aspx#bm1008735
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 4:44 pm
Thanks Craig, always the bearer of good news. 🙂
I knew I was going to have to do some more work to actually get the space back, but nice to know it could be a right royal pain!
---------------------------------------------------------------------
January 12, 2012 at 4:55 pm
george sibbald (1/12/2012)
Thanks Craig, always the bearer of good news. 🙂
There's a reason I'm Evil! 😉
I knew I was going to have to do some more work to actually get the space back, but nice to know it could be a right royal pain!
BLOB space recovery is incredibly nasty. I refuse to do inline BLOBs anymore, and always force at least a secondary ndf for them. It's improved since 2k when it made me cry in public, but it's still painful.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2012 at 2:41 am
just to finish off this thread and put some info out there.
Went the drop column route in the end. DB in simple recovery mode
17GB table, 9.4 million rows, drop column of image data type.
drop column, - instant, no log of any size produced. No change in table space usage
dbcc cleantable in batches of 100,000 - max 600mb of log usage. took 50 mins on a 4GB, dual core VM. Table space usage dropped to 4GB.
Added an identity column to the table, 13 mins, 2GB log
clustered on identity column, 4 mins duration, no appreciable log usage. another 400MB space recovered.
---------------------------------------------------------------------
February 14, 2012 at 2:54 am
Thank you very much for the great feedback, George!
I'm very surprised to see that the column was dropped instantaneously. I'm still wondering why.
-- Gianluca Sartori
February 14, 2012 at 2:56 am
I wish I'd seen this earlier...
Dropping a column is a meta-data only operation. SQL just changes the metadata to say that the column isn't there any longer and bumps the version of the table, the pages themselves don't get touched.
Adding a nullable column is the same.
Lots of fun, it's possible for a single page to have rows which have different numbers of columns depending when they were last written vs when the metadata changes were done.
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
February 14, 2012 at 3:15 am
GilaMonster (2/14/2012)
I wish I'd seen this earlier...Dropping a column is a meta-data only operation. SQL just changes the metadata to say that the column isn't there any longer and bumps the version of the table, the pages themselves don't get touched.
Adding a nullable column is the same.
Lots of fun, it's possible for a single page to have rows which have different numbers of columns depending when they were last written vs when the metadata changes were done.
Thanks for the info, Gail. I feel a bit dumb now. :blush:
I thought that removing a columns was like adding a non nullable column. Thinking of it a bit more, it makes sense: the pages will be overwritten with the new column set only when needed by subsequent DML statements.
This confirms that the most dangerous things are not the ones you don't know, but the ones you don't know you don't know. 🙂
-- Gianluca Sartori
February 14, 2012 at 3:23 am
I think this calls for another blog post added to the to-write queue. When I start blogging again I'm going to have no shortage of topics.
Thinking of it a bit more, it makes sense: the pages will be overwritten with the new column set only when needed by subsequent DML statements.
And selects can check the row's version against the metadata version and ignore the column that shouldn't be returned but is still there.
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
February 14, 2012 at 3:30 am
GilaMonster (2/14/2012)
I think this calls for another blog post added to the to-write queue. When I start blogging again I'm going to have no shortage of topics.
See? My ignorance is not totally useless, then. 😉
-- Gianluca Sartori
February 15, 2012 at 2:05 am
GilaMonster (2/14/2012)
Adding a nullable column is the same.
Is it possible it would have to expand the size of the NULL bitmap to accommodate the extra column, or is that always a fixed size?
February 15, 2012 at 2:42 am
paul.knibbs (2/15/2012)
GilaMonster (2/14/2012)
Adding a nullable column is the same.
Is it possible it would have to expand the size of the NULL bitmap to accommodate the extra column, or is that always a fixed size?
According to MSDN, the NULL bitmap size is calculated as:
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
This suggests it could change. I've never seen this happening though.
-- Gianluca Sartori
February 15, 2012 at 3:04 am
Gianluca Sartori (2/15/2012)
According to MSDN, the NULL bitmap size is calculated as:Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
This suggests it could change. I've never seen this happening though.
Dunno where they got the 2+ from, the null bitmap is 1 bit per column, rounded up to the nearest byte, so 5 column = 1 byte, 10 columns = 2 bytes, 15 columns = 2 bytes.
Edit: Forgot the column count that the null bitmap starts with. That's where the 2+ comes from.
Yes, if the number of columns increases the null bitmap might have to grow (table with 16 columns, add one more and the null bitmap needs to grow an entire byte. That would happen the next time the row is changed, not at the point that the nullable column is added, assuming the column was added without default values
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
February 15, 2012 at 3:11 am
GilaMonster (2/15/2012)
Gianluca Sartori (2/15/2012)
According to MSDN, the NULL bitmap size is calculated as:Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
This suggests it could change. I've never seen this happening though.
Dunno where they got the 2+ from, the null bitmap is 1 bit per column, rounded up to the nearest byte, so 5 column = 1 byte, 10 columns = 2 bytes, 15 columns = 2 bytes.
Maybe that 2 is for the offset to the null bitmap (tells where in the row the null bitmap starts)
Yes, if the number of columns increases the null bitmap might have to grow (table with 16 columns, add one more and the null bitmap needs to grow an entire byte. That would happen the next time the row is changed, not at the point that the nullable column is added, assuming the column was added without default values
This is the part I was missing.
A nice read on the subject: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(630)-three-null-bitmap-myths.aspx
-- Gianluca Sartori
February 15, 2012 at 4:18 am
Interesting stuff...so even if the NULL bitmap should theoretically grow to accommodate a new column, it won't until some data is actually put into the specified record? Presumably the storage engine must assume any additional columns in the table that aren't in the bitmap are NULL by default. Clever bit of optimisation there!
February 15, 2012 at 4:35 am
paul.knibbs (2/15/2012)
Interesting stuff...so even if the NULL bitmap should theoretically grow to accommodate a new column, it won't until some data is actually put into the specified record?
Until the row is touched by a data modification. As far as I know, it doesn't have to be that particular column (the newly added null one) that gets updated.
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
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply