I want to know what is the maximum rows size of my table to figure how much of my data has passed the page size. I tried 3 different queries and ended up with 2 different results.
I'd like to know why sys.dm_db_index_physical_stats -> max_record_size_in_byte is different than what I get from sum(datalength(records))
To be clear, sys.dm_db_index_physical_stats -> max_record_size_in_byte tells me that the max size is 8,061 while summing the datalengths gives me 72,151 which is almost 10 times bigger
declare @table nvarchar(128)
declare @idcol nvarchar(128)
declare @sql nvarchar(max)
--initialize those two values
set @table = 'assets'
set @idcol = 'tenantid,id'
set @sql = 'select ' + @idcol +' , (0'
select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'
from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc '
-- This generates select tenantid,id , (0 + isnull(datalength([TenantId]), 1) + isnull(datalength([Id]), 1) + isnull(datalength([CreatedBy]), 1) + isnull(datalength([UpdatedBy]), 1) + isnull(datalength([Created]), 1) + ... as rowsize from assets order by rowsize desc
dbcc showcontig (@table) with tableresults
SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'[testDB]'), OBJECT_ID(N'[dbo].[Assets]'), NULL, NULL , 'DETAILED')