One of the new phrases coming out of Microsoft is that “SQL is just SQL” regardless of what operating system it resides on. This was echoed during the keynote at SQL Bits 2019 by the Microsoft team, which you can watch here. Later that weekend, I gave a session about database internals. My presentation is about how data is structured within a row and why that matters. Understanding the internals of table structures, even in today’s age of technology, include SQL Server 2019 (which will be released in Q3/Q4 of 2019) is important. During my session, a question came up about how a data page is structured if SQL Server is sitting on top of a Linux server, such as Ubuntu. Does the data page have the same size and shape in Linux as it does in Windows?
Honestly, I wasn’t quite sure. Thankfully, my colleague, Joey D’Antoni (B|T) was present in the room and he was able to confirm that it’s just SQL and a data page has the same size and shape in Linux as it does in Windows. Once the session was over, I wanted to set out in verifying that for my own learning!
SQL Server on Linux
First things first, I needed a SQL Server instance that was sitting on top of the Linux eco-system. Naturally I turned to Azure and quickly stood up a brand-new SQL Server 2019 CTP 2.2 on Ubuntu. You can see below that the SQL Server is up and running, ready to service requests.
Now that I have the instance up and running, I can start to do some testing! First I need to create a database and then a table. Once that is complete, I will put a row into the table and then check the pages. All of this can be done via SSMS or Azure Data Studio. After all, SQL is just SQL so I can use the tools I’m familiar with to accomplish the work. Anyway, let’s do this!
Let’s create the database and table then insert a single record with a first name of “John” and an ID of “1”.
IF DB_ID('PagesOnLinux') IS NULL
BEGIN
CREATE DATABASE PagesOnLinux
END
GO
USE [PagesOnLinux]
GO
CREATE TABLE dbo.table1 (id INT, fname VARCHAR(20))
GO
INSERT dbo.table1 (id, fname) SELECT 1,'John'
GO
Once we have a row inserted, we can use DBCC IN and DBCC PAGE to locate and examine the necessary pages. You an also use the DMV sys.dm_db_database_page_allocations to locate the data page that has the row on it. Since there is only a single row (and it’s much smaller than 8060) there is only a single data page.
We can see that page 336 is the one that we want to examine. Even on Linux, you still need to enable trace flag 3604. This trace flag will pipe the output of the DBCC PAGE command to the results pane of SSMS instead of the SQL Server log file.
DBCC TRACEON (3604)
DBCC PAGE('PagesOnLinux',1,336,3)
DBCC TRACEOFF (3604)
GO
The page header is where we will find the information we need. On the page header, there are two elements I want to point out:
- m_freeCnt – We know from Paul Randal’s blog that this indicates how many bytes are free on the page. In this case, you can see I have 8075 bytes free. This indicates very quickly that the page size is most likely 8192, or 8Kb as we suspected. However, let’s verify it just to make sure.
- Slot 0 & Length – SQL Server is zero based so slot zero indicates the first row on the page. Since I only inserted a single row, this would represent that inserted row. The length (in bytes) reflects how many bytes that row (or slot) is consuming. In this case, we can see that the row is 19 bytes.
Let’s do the math…..
8075 bytes free on the page. 19 bytes for the row.
8075 + 19 = 8094
8094 bytes in total. We also know again from Paul’s blog that a page is 8192 bytes in size and the header of a page is 96 bytes. That leaves 8192 – 96 = 8096 bytes so we are two bytes short (8096-8094 = 2) however we have not yet taken the slot array into account. The slot array is 2 bytes for every row so now we have 8075 + 19 + 2 + 96 = 8192 bytes.
This validates that a data page in SQL Server on Linux is indeed 8Kb!
Summary
In this aspect, it’s true what they say in that SQL is just SQL regardless of the platform it might be running on. We’ve proven from our experiment here that a data page, whether it’s on Windows or Linux, is still 8Kb in size. How your tables are constructed can really make a different in how your application behaves. Make sure to do your due diligence when designing tables and others will thank you for it!
SQL is just SQL!!
Enjoy!
Note: The featured image at the top of the post provided by https://bytelix.com/aplicaciones/ and is licensed under CC BY-NC 3.0.
© 2019, John Morehouse. All rights reserved.