3

I would like to see how many 8K pages are taken by a specific record.

If you run this query on AdventureWorks2019:

SELECT ProductID
    ,Name
    ,DATALENGTH(Name) AS SizeInBytes
    ,LEN(Name) AS NumberOfCharacters
FROM Production.Product
ORDER BY 1

You cna have a list of bites taken by each record:

ProductID Name SizeInBytes NumberOfCharacters
1 Adjustable Race 30 15
2 Bearing Ball 24 12
3 BB Ball Bearing 30 15
4 Headset Ball Bearings 42 21
316 Blade 10 5

How can I add a column with the number of pages taken by that record?

Of course I can do DATALENGTH(Name) / 8 AS PagesTaken but I wonder if there is some command that allows me to find the number of pages through a SELECT.

0

1 Answer 1

12

I think you have something backwards here. A page is 8 KB (8,192 bytes). The rows in that table have a maximum size of the sum of the data type sizes of all of the columns:

Screenshot of the create table script for Production.Product in the AdventureWorks2019 database

I'm not actually going to add all of those up, but let's imagine the sum is 500 bytes.

You can look up the size of each data type by going through this page in the docs, and clicking through to each type you want to find size information on: Data types (Transact-SQL)

Because of a fixed size header on each page, and other per-row overhead, the amount of space available for rows on an 8KB page is 8,096 (see here for details: What Other Information Is Stored in The Page Header).

That means that, in this table, you could have up to 8096 / 500 = 16 rows per page. It might be possible to fit slightly more rows than that, depending on the size of the variable length (nvarchar) columns.

You can see how many pages are in a specific index by using a query like this: SQL Server: Number of 8K Pages Used by a Table and/or Database

From there you could derive an average of how many rows per page you're getting (total rows in the table / total pages in the table).

You can also use DBCC PAGE (as described in the "page header" link above) to view detailed information about each page, including how many rows there are on the page.


Note that all of the above applies to "in row data." If the table has LOB data types, then those will be stored off-row, potentially on multiple pages, if their size causes the record to exceed the maximum record size of 8,060 bytes.

2
  • Thank you, so that means that the record Adjustable Race which has size 30 bits is taking 0,0037055335968379 pages? (I'm doing 30 / 8,096 = 0,0037055335968379). And there is no way to find this through a SELCT? Feb 8, 2022 at 14:34
  • 1
    @FrancescoMantovani "Adjustable Race" is not a record. It's the value of only one field (Name) in that record. And no, there's not a way to find this information that I know of without doing the math yourself, or using the techniques described in my answer. Feb 8, 2022 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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