Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«September 2015»
MonTueWedThuFriSatSun
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

SQL Internals Reading Data Records Part 1: Tag Bytes

  • 26 June 2012
  • Author: BradleyBall
  • Number of views: 4250
  • 0 Comments

Hello Dear Reader while learning about SQL Server the more you learn the more you start looking at SQL Internals and how data is stored on Disk.  As you go deeper you will eventually use commands like DBCC IND to find Page numbers or DBCC PAGE to look at the contents of a data page to look at actual data records.  When you do you will also see a numeric output along the left hand portion of your screen, it is the data as it is stored on the page, but it isn’t in a format that we would normally read it.  I’ve been in presentations and read books written by some very smart people and I’ve heard them say that reading a data record takes a little practice.  This isn’t so much something that you will use in your job as it is having some fun exploring the tool we use every day, so come along Dear Reader and let’s practice together. 

 

We’ll start by looking at a graphical representation of a Data Record.  This is the best I’ve ever seen and it comes directly from Paul Randal (@PaulRandal | Blog) and the MCM Video series on Data Structures.

 

When we look at a Data Record the first part of it is the Tag Bytes.  Tag Bytes are two bytes and are made up of two parts Status Bits A and Status Bits B.  Status Bits A are detailed in Kalen Delaney’s excellent book, SQL Server 2008 Internals

Bit 0 contains versioning Information.  Bits 1 to 3 are used as a 3 bit value to cover a host of things, (we’ll get to in a moment).  Bit 4 indicates that a NULL Bitmap exists, there will always be a NULL Bitmap.  Bit 5 is meant to show if a variable length field exists.  Bit 6 will tell us if there is any versioning information in the record, and bit 7 is not used.  Status B Bits are only used to indicate a ghost forwarded record.  All of that information is stored in two bytes per data record, one byte for Status A and one byte for Status B.

 

So that was pretty technical, how do we relate this to English?  First off let’s talk about how a record is displayed.  On a page it will be displayed in Hexidecimal code, in order to read it we have to translate that to binary and then flip the binary.  It’s clear as mud but let’s walk through it real quick. 

First we’ll create a database and a table for this and insert a record.

USE master;
Go
IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoInternals')
       BEGIN
              DROP Database demoInternals
       END
CREATE DATABASE demoInternals
GO
USE demoInternals
GO

/*

Let's create a Clustered Index

*/

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
BEGIN
       DROP TABLE dbo.myTable1
END
CREATE TABLE myTable1(
       myID INT IDENTITY(1,1)
       ,productName char(500) DEFAULT 'some product'
       ,productDescription CHAR(1000) DEFAULT 'Product Description'
       ,PRIMARY KEY CLUSTERED(myID)     
) ;   

/*

Insert one data record

*/

INSERT INTO dbo.myTable1
DEFAULT VALUES
GO

Now that we have our table and our data record, let’s take a closer look.  I’ll be using DBCC IND and DBCC PAGE over and over again, the only thing that will change in future examples is the structure of the table we are creating.

DBCC IND(demoInternals, 'myTable1', 1)
GO

 

 

Now we have our pages in our table, PageType 10 is an allocation unit our data page is a PageType 1, we can get our page number and plug it into DBCC PAGE.  Don’t forget to turn on Trace Flag 3604 so we can get the output of DBCC PAGE to our SSMS window.

 

DBCC TRACEON(3604)
GO
DBCC PAGE('demoInternals', 1, 276, 3)
GO

 

I’m only going to post the part that is relevant to us, so this is extremely paired down, and I’m only grabbing our first bit of data

0000000000000000:   10 00e405 01000000 736f6d65 2070726f 64756374  ..ä.....some product

 

In red you will find our Status A Tag Byte and in blue our Status B. 

 

I hear what your saying Dear Reader, how is 10 all of that information I talked about up top, it doesn’t look like much.  But this is actually 0x10 in hexadecimal, you can use the converter tool at http://easycalculation.com/hex-converter.php (plug in value 10) and watch as it is converted to binary.  In binary it is translated to 00010000, we are not quite finished remember we need to flip this value to get what we are looking for.   So how do we flip it, first we read it backwards one section at a time. 

 

Now one important thing to note is that our 3 bits even though they say 0-7 just like everything else they are binary values as well 0=000, 1=001, 2=010, 3=011, 4=100, 5=101, 6=110, and 7=111.

3-bit binary value

Decimal value

000

0

001

1

010

2

011

3

100

4

101

5

110

6

111

7

 

 

So these tag tell us that this is a Primary Data  Record, it contains no Versioning Information, no variable length fields, and no Row Versioning information.

 

So let’s take this same row in our table and delete it to make it a ghost record and see how our Tag bytes change.

BEGIN TRANSACTION

DELETE FROM
dbo.myTable1

DBCC PAGE('demoInternals', 1, 276, 3)

 

We don’t want to commit or rollback the transaction just yet, we want the record marked as ghosted.  Now let’s look at our column.

0000000000000000:   1c 00e405 01000000 736f6d65 2070726f 64756374  ..ä.....some product

 

Remember to convert your 1c value from Hexadecimal to binary, and flip your binary value.       

 

 

Remember our chart 110=6, so this is indeed a Ghost Data Record.  So now let’s drop our table and add a variable length column to it.  Then we’ll insert a record.

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
BEGIN
       DROP TABLE dbo.myTable1
END

CREATE TABLE myTable1(
       myID INT IDENTITY(1,1)
       ,productName char(500) DEFAULT 'some product'
       ,productDescription CHAR(1000) DEFAULT 'Product Description'
       ,productExtendedDescription varchar(2000) DEFAULT 'Extended Product Description'
       ,PRIMARY KEY CLUSTERED(myID)     
) ;   

INSERT INTO dbo.myTable1
DEFAULT VALUES
GO

Remember to use DBCC IND and DBCC PAGE to get our internal information, then let’s look at our record.  And translate our Tag A bytes.

0000000000000000:   30 00e405 01000000 736f6d65 2070726f 64756374  0.ä.....some product

 

Here are our results.

 

 

And once again we see translated we have our Primary Data record with a null bitmap and variable length columns.  One interesting note, if you alter your original table to have a variable length column you have to rebuild the table in order for the Tag bytes to change from 0x10 to 0x30.

 

Hope you enjoyed the read, I had fun writing this up.

 

Thanks,

 

Brad

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

BradleyBallBradleyBall

Other posts by BradleyBall

Please login or register to post comments.