SQL Server Data Pages on Linux

Published On: 2019-03-22By:

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.

Power BI Now Has Keyboard Accessible Visual Interactions

Published On: 2019-03-21By:

The March 2019 release of Power BI Desktop has brought us keyboard accessible visual interactions. One of Power BI’s natural strengths is that you can click on a data point within a visual and have it cross-highlight or cross-filter the other visuals on a page. But keyboard-only users weren’t able to use this feature until now. This greatly raises the accessibility of the Power BI report consumption experience.

Below is a demonstration of interacting with a visual using keyboard commands. Notice how I can select specific data points within the line chart, and the other charts on the page filter based upon the selection.

Keyboard commands can now access visual interactions

If you are a person that prefers to use a keyboard over a mouse, this might also be something you want to try. Relevant keyboard commands include:

  • Ctrl + right arrow: Move focus into the chart area of the visual
  • Tab or Arrow keys: navigate between data points (or legend items in a chart that contains a legend)
  • Enter or Space: select a data point within a visual
  • Ctrl + Enter or Ctrl + Space: select multiple data points within a visual
  • Ctrl + shift + c: clear all selections

I think this was the last big missing piece of keyboard accessibility. I’m excited to see its impact on users. If you try keyboard accessible visual interactions, or are taking advantage of keyboard accessibility in Power BI in general, please let me know how you are liking it. Tweet me at @mmarie or send me a note via my blog contact form.

Women in Tech Talk Tech

Published On: 2019-03-20By:

March is Women’s History Month, to celebrate the PASS Women in Technology Virtual Group (@PASS_WIT) held a webcast featuring prominent woman in the Data Platform community. I had the pleasure of being one of five panelists alongside these amazing women Lori Edwards (B|T), Malathi Mahadevan (B|T), Kellyn Pot’Vin-Gorman (B|T), and Catherine Wilhemsen (B|T).

Discussion ranged from direct technical topics to what it’s like being a woman in tech. Women took turns providing advice around having a degree and/or certification to succeed in tech. We discussed how to keep up with advances in the field and skills that technologists should have. Specific to being a woman in tech, we talked about having impostor syndrome, having a mentor, being a mentor and how that helped our career. We talked about what question we’re tired of being asked on these panels and what we need to do so we’re never asked that again. We wrapped up with a great question that I’d love for you to answer, “If you could go back in time & speak to yourself before you entered IT, what would you say?”

If you missed the discussion, no worries, you can see it here.

https://www.youtube.com/watch?v=uoyoL5Gd-Z8&t=1s

The PASS Women in Tech Virtual Group, lead Rie Irish (@IrishSQL) and Kathi Kellenberger (@auntkathi), highlights all the great work women are doing in the data platform speaking, writing, educating and building great products.  They encourage, empower & energize women to succeed in the tech field.  Their mission is to lift as we climb.   You can join the group https:/wit.pass.org.  Join the mailing list to receive the monthly newsletter that highlights the great work women are doing in tech along with topics important to women in their daily lives.

I thought my days of Linux were over

Published On: 2019-03-18By:

Oh, how wrong I was. Back in the day, all I worked on was Microsoft SQL Server. These days I’m doing some Microsoft SQL Server and a decent amount of Microsoft Azure and Amazon AWS cloud work. With all three of those, there’s a lot of Linux in play. Microsoft SQL Server has supported Linux since the release of SQL Server 2017 at Ignite 2017.  Microsoft Azure and Amazon AWS have both supported Linux since (I believe) they first supported VMs in their cloud platforms (forever is the world of computers).

Back when I had just a few years expense with SQL Server (and IT in general) I also owned and managed a large (at the time) Oracle database which ran on Unix. Once that was no longer my baby to manage, I assumed by *nix carrier was over. And it was, for a while, but now Linux is back and this time in the SQL Server world.

Looking at the servers that DCAC has in our Azure environment, we have more Linux boxes than Windows. Our website runs off of PHP running on a pair of Linux servers. Our database is MySQL running on a couple of Linux server (eventually we’ll move all this over to Azure PaaS, but still running on Linux). The only production servers in Azure that we have running Windows, our the Active Directory domain controllers, one of which also syncs from Active Directory to Azure Active Directory to handle our sign in, Office 365, etc.  That’s it. Everything is Linux.

Our lab environment in our CoLo is also a mix of Windows and Linux.  We have a few tools that were built by Microsoft that we run that are running on Windows, but we’ve also got a decent amount of Linux in the data center as well.  By the time this is published (I’m writing this on the flight to the PASS Summit in November 2018) we’ll have a Docker cluster up and running as well (unless I get lazy and I don’t get up to the CoLo to rack the servers for it). This Docker cluster is Linux based as well and will let us run a bunch more Linux servers as well.

Your point is?

The point that I’m trying to get to in all of this is that if you are a database administrator that thought they were going to stay in the Windows world forever, think again. You have to be an expert in Linux to manage these systems, but you’ll need to understand the difference between Windows and Linux. SQL Server has a few differences between the platforms, and these differences are significant to the platforms.  As a Windows DBA you’ll want to be able to navigate the Linux Operating System, and tell your system teams where SQL Server is storing the database files (they are in /var/opt/mssql/data if anyone asks) so that they know which mount points need to be made bigger.

You don’t need to know everything, but the basics of Linux are doing to take you a long way.

Denny

The post I thought my days of Linux were over appeared first on SQL Server with Mr. Denny.

1 2 3 411

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.