What is the data page size on SQL Server 2016 Database?

To find out the data page size on SQL Server 2016 Database, we can run the SQL script, fyi_page_insert.sql, presented in the last tutorial with different @Size on SQL Server 2016 Database.

The following table shows the summary of the test results. The PARTITION field is removed to save space. The PAGE_SIZE field is calculated as SIZE * COUNT / PAGES.

@@version = Microsoft SQL Server 2016 - 13.0.5292.0 (X64) 

 SIZE  COUNT  DURATION  +ROWS   ALLOCATION  +PAGES  FILE_ID   PAGE_SIZE
  256    100       123    100  IN_ROW_DATA       4        1        6400
  512    100       143    100  IN_ROW_DATA       8        1        6400
 1024    100       110    100  IN_ROW_DATA      22        1        4655
 2048    100       110    100  IN_ROW_DATA      54        1        3793
 4096    100       133    100  IN_ROW_DATA     100        1        4096
 8192    100       130    100  IN_ROW_DATA     100        1        8192
16384    100        76    100  IN_ROW_DATA     100        1       16384
32768    100       140    100  IN_ROW_DATA     100        1       32768

As you can see:

• The SQL Server 2016 Database gives us a consistent latency of 1 ms to perform a single INSERT statement, no matter how small or big the record (row) size is.
• The page size is not fixed as the record (row) size changes.
• For smaller record sizes, the page size is in the range of 3,793 to 6,400 bytes.
• For larger record sizes, the pages size is the same as the record size.

Comparing to Azure SQL Database of (12.0.2000.8 May 2 2019), SQL Server 2016 is about 4 times faster.

⇒ Use fn_virtualfilestats() for I/O Tests

⇐ Page Size Varies on Azure SQL Database

⇑ I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials