How to get the size of records in a table in SQL Server 2014? What kind of request you need to write?

    1 answer 1

    You can, for example, request statistics in sys.dm_db_index_physical_stats ( link ).

    A query for the case when partitioning is not used on the table:

     select ips.alloc_unit_type_desc, ips.record_count, min_record_size = ips.min_record_size_in_bytes, max_record_size = ips.max_record_size_in_bytes, avg_record_size = ips.avg_record_size_in_bytes from sys.indexes ix cross apply sys.dm_db_index_physical_stats( db_id(), ix.object_id, ix.index_id, NULL, 'SAMPLED') ips where ix.object_id = object_id('dbo.TableName') and ix.type_desc in ('HEAP', 'CLUSTERED');

    If partitioning is used, then the data for different sections need to be aggregated (the average is better to take weighted by the number of records in the section):

     select ips.alloc_unit_type_desc, record_count = sum(ips.record_count), min_record_size = min(ips.min_record_size_in_bytes), max_record_size = max(ips.max_record_size_in_bytes), avg_record_size = sum(ips.avg_record_size_in_bytes * ips.record_count) / sum(ips.record_count) from sys.indexes ix cross apply sys.dm_db_index_physical_stats( db_id(), ix.object_id, ix.index_id, NULL, 'SAMPLED') ips where ix.object_id = object_id('dbo.TableName') and ix.type_desc in ('HEAP', 'CLUSTERED') group by ips.alloc_unit_type_desc;

    Pay attention, however. If the corresponding query except the line with alloc_unit_type_desc equal to IN_ROW_DATA also returns LOB_DATA or ROW_OVERFLOW_DATA with non-zero values ​​(i.e., if the table contains LOB or variable-length columns with data, due to which the record size may exceed 8060 bytes), then by data returning sys.dm_db_index_physical_stats reliably determine the size of the table entries is not possible.

    The fact is that a single table entry can physically consist of several pieces (one in-row and several lob and row-overflow), but sys.dm_db_index_physical_stats does not add data for them, but counts them as separate "records". This can be seen in the following example.

    If you insert one record into a table with one LOB column:

     create table lob_test (data varbinary(max)); insert into lob_test values (cast(replicate(cast(0x11 as varbinary(max)), 32000) as varbinary(max)));

    and see the result that returns the first query for this table, then we will see

     alloc_unit_type_desc record_count min_record_size max_record_size avg_record_size --------------------- ------------- ---------------- ---------------- ---------------- IN_ROW_DATA 1 71 71 71 LOB_DATA 4 7894 8054 8014

    those. the only record we insert consists of 1 + 4 = 5 pieces. In LOB_DATA not one piece with max_record_size ~ 32 thousand bytes, but four pieces of ~ 8 thousand bytes, which does not allow to correctly estimate the size of the record.

    Therefore, in cases where table entries have data in LOB or row-overflow pages, it may be more expedient to try to estimate the size of records using the datalength function:

     select min_record_size = min(record.Size), max_record_size = max(record.Size), avg_record_size = avg(record.Size) from dbo.TableName t cross apply ( select datalength(Column1) + datalength(Column2) + ... datalength(ColumnN)) record(Size);

    With datalength , however, there are pitfalls .