Page Size Varies on Azure SQL Database

Q

What is the data page size on Azure SQL Database?

✍: FYIcenter.com

A

To find out the data page size on Azure SQL Database, we can run the SQL script, fyi_page_insert.sql, presented in the last tutorial with different @Size on Azure SQL 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 Azure (RTM) - 12.0.2000.8 May 2 2019

 SIZE  COUNT  DURATION  +ROWS   ALLOCATION  +PAGES  FILE_ID   PAGE_SIZE
  256    100       424    100  IN_ROW_DATA       4        1        6400
  512    100       386    100  IN_ROW_DATA       8        1        6400
 1024    100       387    100  IN_ROW_DATA      22        1        4655
 2048    100       420    100  IN_ROW_DATA      54        1        3793
 4096    100       494    100  IN_ROW_DATA     100        1        4096
 8192    100       403    100  IN_ROW_DATA     100        1        8192
16384    100       436    100  IN_ROW_DATA     100        1       16384
32768    100       470    100  IN_ROW_DATA     100        1       32768

As you can see:

  • The Azure SQL Database gives us a consistent latency of 4 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.

 

Page Size Varies on SQL Server 2016 Database

Use allocation_units for I/O Tests

I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials

2019-05-12, 2253🔥, 0💬