1

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

  • Also consider page and row overhead plus the b-tree index. Not much you can do about it except use page/row compression (Enterprise feature). – Dan Guzman Sep 5 '16 at 2:37
1

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

0

After further reading, I believe there is always a 7 byte overhead per row in MS SQL Server.

  • 2 bytes for record header
  • 2 bytes to store the number of columns in the record
  • 2 bytes for number of columns in the null bit map
  • 1 bytes for null bitmap (because there are two columns)
0

A Data Page is 8192 bytes. Of those bytes, 96 are reserved as the Page Header, leaving you with 8096 "usable" bytes for row data.

Per each row, you have 6 bytes of fixed overhead, plus one byte for the NULL Bitmap (but with more columns this number might increase, as per CEILING( num_columns / 8.0) ), and finally 2 bytes of fixed, associated (but disconnected) overhead for the slot array. Meaning, you really have 9 bytes per row of overhead (in this particular table). (Side note: if the Database has ALLOW_SNAPSHOT_ISOLATION enabled, there will be an extra fixed 14 bytes of overhead per row!).

Given that you have 12 bytes of fixed-length data, we can combine that with the above info to get the following equation. There is no need to worry about fractional amounts as the INT types for the numbers will truncate the fractional portion of the result (i.e. a "partial" row).

SELECT (8192 - 96) / (12 + 6 + 1 + 2) AS [NumberOfRowsPerDataPage];
-- 385

Now we know that we can fit a maximum of 385 rows onto a data page. I say maximum because you can have pages that are less than 100% filled due to page splits, deletes, FILLFACTOR set to < 100, or naturally being the last data page and not yet being filled entirely. However, assuming that you have done a full REBUILD on the Clustered Index (and that FILLFACTOR is set to 100), all but the possibly the last page should be filled with 385 rows.

We can use this number of rows to determine both how many bytes, if any, are left over upon being filled, and how many data pages are being used (and hence how many bytes):

DECLARE @NumRows BIGINT = 500000000;

SELECT 8192 - ( 96 + ((12 + 7 + 2) * 385) ) AS [FreeBytesPerDataPage], 
       (@NumRows / 385) AS [NumberOfDataPages],
       (@NumRows / 385) * 8192 AS [NumberOfBytesForDataPages],
       ((@NumRows / 385) * 8192) / 1024.0 AS [NumberOfKBytes],
       ((@NumRows / 385) * 8192) / 1024.0 / 1024.0 AS [NumberOfMBytes],
       ((@NumRows / 385) * 8192) / 1024.0 / 1024.0 / 1024.0 AS [NumberOfGBytes];

Returns:

FreeBytes    NumberOf   NumberOfBytes  NumberOfKBytes  NumberOfMBytes    NumberOfGBytes
PerDataPage  DataPages  ForDataPages
11           1298701    10638958592    10389608.00000  10146.1015625000  9.9083023071289062

There is your 11 "free" bytes and your 10-ish GB of data (not including index space). If you are using sp_spaceused, it reports sizes in Kb, so I assume you saw the 10389608 Kb and simplified it to be 10 Gb, whereas technically that value needs to be divided by 1024 twice to get "true" Gigabytes. Or perhaps you were speaking in terms of total / "reserved" space. Either way :-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.