“So Balls”, you say, “What changed?”
Excellent question Dear Reader 2008, 2008 R2, and 2012 are all consistent with one another, but if one does a search on the Null Bitmap and pulls back any of the blogs on them they didn’t match up with what I was finding. Maybe this changed in a service pack, or it was something simple and under the covers, but there is definitely a difference Dear Reader. That difference is in the Hex pair that make up the Null Bitmap, but I’ll cover that in another blog that I’ll link back to here.
First up let’s talk about what we came here for the Null Bitmap.
First let’s update our chart so we know what part of the Data Record we are tackling. Once again these images come by way of Paul Randal (@PaulRandal | Blog) and the MCM Video series on Data Structures and the good people from Microsoft.
The Null Bitmap portion of the record actually has two parts. The first is a two byte value that tells us the number of columns in the record, the second is a one byte value called the Null Bitmap. And while the name sounds very fancy it is simply what it sounds like, it is a map of the Null and Non Null values in a record stored in bit format. When we look at the Null Bitmap 0=a column that is in use, and 1=a column that is NULL.
In the Record it is in Hexidecimal format. All of the records that we look at use two Hex pairs, and each block is 4 bytes in length, so we have 8 Hex characters making up a 4byte block. A regular data record will always have a Null bitmap, but Records that are formatted using Sparse do not. Mark S. Rasmussen (@improvedk|Blog) is a very smart fellow that I had the pleasure of doing a lightning talk with at the PASS Summit 2011. He has written this blog explaining the Null Bitmap is not always present in data records.
So now that we’ve got some extra detail out of the way let’s to 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')
DROP TABLE dataRecord2
CREATE TABLE dataRecord2
INSERT INTO dataRecord2(myID, myfixedData, myVarData1, myVarData2, myVarData3)
VALUES (7, 'XXXX', null, null, null)
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)
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 PAGE('demoInternals', 1, 288, 1)
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 15
Memory Dump @0x00000000136EA060
0000000000000000: 10000c00 07000000 58585858 05001c ........XXXX...
Here in Red, Green, Bold, and Underlined (sorry to anyone who is color blind hence the bold & underlined…), 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. More to follow up on this, but first I want to finish up on our Null Bitmap. So now Let’s change one of our Variable length columns and watch the Null bitmap change. We’ won’t go in order because it’ll be a little more interesting to go out of order. So let’s update column myVarData2 to contain some data and do a DBCC PAGE to get our output.
DBCC PAGE('demoInternals', 1, 288, 1)
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x000000001276A060
0000000000000000: 30000c00 07000000 58585858 05001402 0015001b 0.......XXXX........
0000000000000014: 00575757 575757 .WWWWWW
So as you can see from our output we’ve got some changes in our Null Bitmap. We still have 5 columns as noted in our Hex value 0x0005. Our bitmap now reads 0X14, let’s translate that to binary and we now get 00010100. We flip that value to 00101000 and it is ready to be read.
Our first two columns remain 0’s because they are in use, column 3 is still null, column 4 is now a 0 because this is in use, column 5 is 1 because it is still null, and we have 00 for our unused bits.
Now if we update our two variable length columns to be not null and run the whole thing again we should get 0’s across the board. But I’ll leave that to you Dear Reader to play around with.
WHAT IN THE NAME OF TWINKIES
So I hear you Dear Reader, What in the name of Twinkies does this have to do with how I manage SQL Server? There are changes big and small made with every release, sometimes we take for granted that some things work the same way. The Null Bitmap was added in SQL 2005 as an optimization, see Paul Randal’s blog on that subject here. When statements are made about how SQL Server is more efficient from one version to the next, this is one of those little reasons.
The Null Bitmap allows the process that reads the data record to know what it needs to read and what it doesn’t. In our example above, it needs columns 1, 2, and 4. If it read over 3 and 5 that would be a waste of time. Yet without that Null Bitmap that is what would happen, it’s not that cut and dry but the Null Bitmap is used in the internal code that reads our data records to determine just that.
Thanks again for stopping by Dear Reader see you next time.