Why the row size is different when reading from different places?

  • ashkan sirous


    Points: 2726

    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 '

    PRINT @sql

    exec (@sql)

    -- 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')


    [3ee08837b67c60c87c873d226dfa28b5]_Image 2019-09-25 at 10.53.20 AM

    • This topic was modified 1 year, 1 month ago by  ashkan sirous.

    Best Regards,

  • John Mitchell-245523

    SSC Guru

    Points: 148777

    What is the definition of the assets table?  My guess is that it contains off-row data.  Have a read of this.  I quote:

    For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.


Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply