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

• ashkan sirous

  SSCrazy

  Points: 2733

  More actions

  September 25, 2019 at 9:16 am

  #3683218

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

   

  

  • This topic was modified 4 years, 7 months ago by  ashkan sirous.

  Best Regards,
  Ashkan

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 25, 2019 at 11:23 am

  #3683248

  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.

  John