SQL Server: Record size larger than expected

Question

My table consists of 3 columns

| Column Name | Data Type | Size
| Value       | real      | 4
| LogId       | int       | 4
| SigId       | smallint  | 2

One primary key is set for columns LogId, SigId.

The sum of all size's is 4+4+2=10, however using sys.dm_db_index_physical_statsI get, that the average (and min/max) record size in bytes is 25. Can someone explain? Am I comparing apples and oranges?

Answer 1

The physical record length includes row overhead in addition to the space needed for the actual column values. On my SQL Server instance, I get an average record length of 17 reported with the following table:

CREATE TABLE dbo.Example1(
      Value real NOT NULL
    , LogId int NOT NULL
    , SigId smallint NOT NULL
    , CONSTRAINT PK_Example1 PRIMARY KEY CLUSTERED(LogId, SigId)
);
GO
INSERT INTO dbo.Example1 (Value, LogId, SigId) VALUES(1, 2, 3);
GO
SELECT avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example1'),1,0,'DETAILED')
WHERE index_level = 0;
GO

The 17 byte record length reported by sys.dm_db_index_physical_stats includes 10 bytes for data, 4 bytes for the record header, 2 bytes for the column count, and 1 byte for the NULL bitmap. See Paul Randal's Anatomy of a record article for details of the record structure.

Below is a script to dump the first clustered index data page using DBCC_PAGE as determined by the undocumented (don't use it in production) sys.dm_db_database_page_allocations table-valued function:

DECLARE
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.Example1')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
--get first clustered index data page
SELECT
      @allocated_page_file_id = allocated_page_file_id
    , @allocated_page_page_id = allocated_page_page_id
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 1, 1, 'DETAILED')
WHERE
    page_type_desc = N'DATA_PAGE'
    AND previous_page_page_id IS NULL --first page of clustered index;
--dump record
DBCC TRACEON(3604);
DBCC PAGE(@database_id,@allocated_page_file_id,@allocated_page_page_id,1);
DBCC TRACEOFF(3604);
GO

Here is an excerpt from the results on my instance with the physical record structure fields called out:

DATA:


Slot 0, Offset 0x60, Length 17, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 17

Memory Dump @0x0000002262C7A060

0000000000000000:   10000e00 02000000 03000000 803f0300 00        .............?...
                    |        |        |   |        |    |null bitmap (1 byte)
                    |        |        |   |        |column count (2 bytes)
                    |        |        |   |Value column data (4-byte real)
                    |        |        |SigId column data (2-byte smallint)
                    |        |LogId column data (4-byte int)
                    |Record header (2-byte record type and 2 byte offset to null bitmap)

As to why your actual record length is 25 instead of 17 as in this example, the likely cause is schema changes were made after the table was initially created as Martin suggested in his comment. If the database has a row-versioning isolation level enabled, there will be additional overhead as mentioned in Paul's blog post but I doubt that is the reason here since that overhead would be more than 8 bytes.