Sql-server – Actual Row Size vs My Estimated Row Size

sql serversql-server-2016

I have a table of 500 million rows on SQL Server 2016. I calculate that each row should take up 12 bytes, total size should be in the order of 6 GiB. However the actual size is over 10 GiB.

After running dbcc showcontig ('[Table]') with tableresults I see the the MinimumRecordSize and MaximumRecordSize is 19 bytes and the AverageFreeBytes is 11.023 bytes.

The table is defined as the following.

CREATE TABLE [dbo].[Table](
    [Key] [binary](7) NOT NULL,
    [Value] [binary](5) NOT NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-Key] ON [dbo].[Table]
(
    [Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My question is where is the extra 7 bytes per row coming from? And is there anything I can do about it?

Also if there are any suggestions about space optimizations would also be appreciated, the table will be read-only (no updates or inserts).

Best Answer

Michael pointed you to the source (Paul Randal's "Anatomy of a Page" and "Anatomy of a Record") in an above comment, but to summarise:

  • SQL Server data is stored in 8KB pages, each of which contains a 96-byte header, and some room at the end of the record for the slot array (which tells the engine the logical order of rows in the page).

  • Because you use a clustered index, the b-tree structure must also be taken into account, per Dan's comment.

All this considered, we can make the following observation:

All things being equal, at 12 bytes per row, we would be able to fit 674 rows in a single 8KB data page. That works out to be 741,840 pages in total, for 500 million rows. No doubt, this is where your 6GB figure comes from, or more specifically, 6,077,153,280 bytes.

However, there's an overhead per row, as you discovered. Summarising from Paul's Anatomy of a Record:

  • Two bytes of record metadata (record type) = 2
  • Two bytes pointing forward in the record to the null bitmap = 2
  • Fixed length portion for the data itself = 12
  • Null Bitmap: Two bytes for count of columns in the record = 2
  • Null Bitmap: Variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not = 2 bits

That's 2 + 2 + 12 + 2 + 1. Yes, it works out to 7 bytes as you calculated, but it could easily be more if there are more columns in the table.

Assuming then a length of 19 bytes per row, you can fit 426 rows in a page. That works out to 1,173,709 pages, or 9,615,024,128 bytes.

If we have a b-tree structure with 500,000,000 rows, your 1,173,709 pages will be at the leaf level. You'll need roughly 2,500 pages for the intermediate level (I don't know the exact length of the intermediate index record off the top of my head, so I guessed 16 bytes), which is an additional 200MB, give or take, and you may have two intermediate levels between root and leaf level.

With this in mind, based on my terrible estimates for the b-tree structure, that would take you to around the 10GB range you see for your data.

(All credit to Dan Guzman and Michael Green for their comments, and of course Paul Randal for his blog post.)

Related Question