Hello Dear Reader, I’ve been working on a series for BIDN.com,
the Business Intelligence Developers Network, on how to read a Data
Record. I’ve always wanted to take the
time to go rummaging around in the internals of SQL Data Records and this was
my shot to do it. If you are interested
please feel free to read Part
1 Tag bytes, Part
2 The Null Bitmap Offset, Part
3 Fixed Data, and Part
4 The Null Bitmap.
While I was working on part 4 I found that the results that
I was getting for the Null Bitmap were a bit different than what I was finding
on the blogosphere and the Intrawebs. I
was reading the blogs of some very smart people, so that lead me to one
conclusion: I was doing it wrong. So I
started following their examples and comparing the results and well….there’s
only so may ways you can type DBCC PAGE before you start thinking maybe I’ve
found something. I was working in SQL
2012 so my first thought was that it changed there. So I stood up a VM with SQL 2008 R2 on it and
I got the same results. So then I stood
up another VM with 2005 and Viola! I was looking at results just like everyone
else. So then I stood up ANOTHER VM with
2008 RTM and once again I was back to what I was initially looking at.
“So Balls,” you say, “What was different?”
Sorry for the Ramble Dear Reader, what I found was that in
2005 when you translate the Null Bitmap if you had a number of columns that
doesn’t divide easily into 8, (The Null Bitmap is one byte which translates out
to a map of 8 individual bits, and you get 8 whether you need them or not),
instead of using 1’s for unallocated bits they use 0’s.
I understand not exactly a jaw dropping conclusion but if you’re
a geek like me that likes to dive into internals this should be a fun read.
LET’S ROCK
So now that we’ve got some extra detail out of the way let’s
dive into examples. First we’ll create
our table with two fixed length columns and three variable length. We’ll keep the variable length columns as
nulls at first so we can see how the Null Bitmap changes.
IF EXISTS(SELECT NAME FROM sys.tables WHERE NAME='dataRecord2')
BEGIN
DROP TABLE dataRecord2
END
GO
CREATE TABLE dataRecord2
(myID INT
,myfixedData CHAR(4)
,myVarData1 VARCHAR(6)
,myVarData2 VARCHAR(6)
,myVarData3 VARCHAR(6)
)
GO
INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)
VALUES (7, 'XXXX', null, null, null)
GO
Now that we’ve got our table and a record inserted let’s use
DBCC IND to find our page.
DBCC IND(demoInternals, 'dataRecord2', 1)
GO
Remember type 10 is an allocation page and we want a data
page or a PageType 1, we see that Page 288 is our candidate. Now let’s use DBCC Page to get page 288, don’t
forget to turn on Trace Flag 3604. I’m
only going to copy the relevant bits from our page dump and remember your page
numbers may differ. I’m using SQL 2012
for this output.
DBCC TRACEON(3604)
GO
DBCC PAGE('demoInternals', 1, 288, 1)
GO
Record Type = PRIMARY_RECORD Record Attributes =
NULL_BITMAP Record Size
= 15
Memory Dump @0x00000000136EA060
0000000000000000:
10000c00 07000000 58585858 05001c ........XXXX...
Here in Red, Green, and Bold (sorry to anyone who is color
blind hence the bold…), is our portion of the record slated as our Null
Bitmap. 0500 is the first two bytes of our record,
remember two numbers equal a Hex pair.
Our Pairs are 05 &
00, they are flipped so
you would read them as Hex 0x0005, which
translates out to 5 and we do have 5 columns. Don’t
forget to use our handy Hex to binary convertor, http://easycalculation.com/hex-converter.php.
The last portion of the record is the Null Bitmap itself 1c. In Hex
this works out as 0X1c and converts to
binary as 00011100. In order to read this binary value accurately
we flip it and it becomes 0011100.
We break this down per column, because we have two columns
that are not null three Variable length columns that are null and then the remaining
two 0’s are not in use. So why are they
are you may ask? Because 1 byte equals
8 bits, and these are left over bits.
Now this is one of the differences that I’ve noticed from
2005 to 2008 on up, in 2005 the left over bits where marked as 1’s. Let’s take a look real quick to back that
up. I set up a VM running Windows 2008
R2 and SQL 2005 Straight up RTM no Service Packs applied. We’ll run the exact same scripts as above and
look at the Null Bitmap output.
IF EXISTS(SELECT NAME FROM sys.tables WHERE NAME='dataRecord2')
BEGIN
DROP TABLE dataRecord2
END
GO
CREATE TABLE dataRecord2
(myID INT
,myfixedData CHAR(4)
,myVarData1 VARCHAR(6)
,myVarData2 VARCHAR(6)
,myVarData3 VARCHAR(6)
)
GO
INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)
VALUES (7, 'XXXX', null, null, null)
GO
Now that we’ve got our table and a record inserted let’s use
DBCC IND to find our page. Notice we use DBCC IND with a -1 if you attempt this
with the exact same script as above you’ll get an output error for dump style
1.
DBCC IND(demoInternals, 'dataRecord2', -1)
GO
Remember type 10 is an allocation page and we want a data
page or a PageType 1, we see that Page 153 is our candidate. Now let’s use DBCC Page to get page 153, don’t
forget to turn on Trace Flag 3604. I’m
only going to copy the relevant bits from our page dump and remember your page
numbers may differ.
Record Type = PRIMARY_RECORD
Record Attributes
= NULL_BITMAP
Memory Dump @0x0000000008BAC060
0000000000000000: 10000c00
07000000 58585858 0500fc††††........XXXX...
asdf
As you can see from above we get the output of fc in SQL 2005 instead of 1c
like we got in SQL 2012. If you take the
Hex for 0xfc and dump it to binary you get 11111100.
When when we flip works out to 00111111.
Just to reiterate, unless you want to validate this for
yourself, I stood up VM’s for 2005 RTM, 2008 RTM, 2008 R2 RTM, and SQL 2012
RTM. Only 2005 handles unused bit’s as 1’s
the rest treat them as 0’s. The Null
Bitmap was an optimization that was added in 2005, Read Paul Randal’s, Paul(@PaulRandal | Blog), blog
post on that here.
Since it was introduced in 2005 then there I’m sure there
was a good reason to change it in 2008 and up.
My guess is that instead of masking as columns that where just there of
offset the bitmap some logic is used based on the number of columns and the
bitmap to determine how much of the record remains to be read.
Whatever the case a lot of the documentation I came across
regarding this all pointed to the unused bit’s being labeled as 1’s. That through me for a loop at first and I
just wanted to toss this out there to anyone else searching on the subject.
Thanks for stopping by,
Brad
No comments:
Post a Comment