I've got an interesting table in one of my DBs that's confusing me.
The table in question has a a few LOB type columns (two nvarchar(max) and a text) and it looks like there's some strange space issues going on.
from this query:
SELECT type_desc, SUM(total_pages) *8 [Size in kb] FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE p.object_id = OBJECT_ID('asyncoperationbase') GROUP BY type_desc;
type_desc Size in kb IN_ROW_DATA 27936 LOB_DATA 1198144 ROW_OVERFLOW_DATA 0
(there's just under 8000 rows in the table, each row has a data length of ~10k - not counting the LOB data)
here's where it gets somewhat interesting:
SELECT ( SUM(DATALENGTH(aob.WorkflowState)) + SUM(DATALENGTH(aob.[Message]))+ SUM(DATALENGTH(aob.[Data])) ) / 1024
FROM AsyncOperationBase aob
As I'm reading it - it looks like the ~75mb of LOB data is using over a gig of space to be stored - I would expect some overhead but not quit that much.