Friday, August 16, 2013

24 Hours of PASS Q & A: Why are Bit’s Flipped?



http://bit.ly/16UyEPH
Hello Dear Reader.  Almost two weeks ago I delivered a presentation for the 24 Hours of PASS on SQL Server Data Internals.  I received some really great questions that have pushed me to dive deeper in my understanding of SQL and computers in general.  One of the questions was as follows:

Why are Bytes sometimes swapped and why do you have to flip bits in order to decode a bitmap?

At the time I took a WAG at the question, Wild @$$ Guess.  I said that they were flipped because of a need to protect proprietary data.  I couldn’t have been more wrong.  Fortunately for me my friend and co-worker Roger Wolter (@RWolter50 | Blog)  was watching and was quick to let me know that the source of the byte swapping was not proprietary.  It was because of Endians.


“So Balls,” you say, “It was because of Endi-a-Whats?”

Don’t worry Dear Reader, I was right there with you.  This was a new term to me, some of you clever former Computer Science majors are already seeing a light bulb form.  For my sake humor me and pretend you haven’t already figured this out.  Some of you former Literature Majors, or general readers of classic tales, are wondering what Gulliver Travels has to do with SQL Internals.

BIG AND LITTLE ENDIANS
Jack Black, Comedian, Singer, Dancer, ...Computer Scientist

In Jonathan Swift’s satirical novel Gulliver’s Travels, Gulliver ends up in a land called Lilliput.  Lilliput has hostilities with their neighbor Blefuscu.  You see Lilliput likes to eat their hard boiled eggs by cracking the little end.  Whereas Blefuscu likes to eat their eggs by cracking the big end.  There for the Lilliput’s are known as Little End-ians and the Blefuscu are Big End-ians.  Side stepping Swift’s satirical play on societal issues the term was later utilized in Computer Science over 200 years later.

So what does this mean for Computers?  It is how we read data out of memory. 

We will be covering memory at a very high level, even though we are pretty deep.  Memory is one big array waiting to hold our data. 

What does that array hold?  Bytes.  How do we find where we are storing the bytes in our array?  We give it an address and we look up that memory address.

An address is not an Index, but for the correlation of how to look up memory data it is comparable to the way we store data on a Clustered Index page and then look that data up by its unique key.  So to make this an easy comparison for my SQL Family, let’s just say that an Address is our Index and how we will look up data in our array/table.

When we read data into memory there is a memory address assigned to the byte or bytes depending on the chipset of the machine.  Big Endian Processors read the data from Left to right, also known as most significant byte to smallest address.  Motorola and quite a few others use Big Endian.  x86 and x64 processors use Little Endian.  Since SQL Server run’s on x86 and x64 hardware we will focus mainly on that.

For example take value XSWB.  If we translate each letter to two byte hex pairs that we would place into memory we would get X=58, S=53, W=57, B=42, or 58535742.  Each hex pair would be translated to binary which would then translate to ASCI characters which would become the regular letters we see.  How would we store that in memory?  The ASCII example below is for 8 bit access.

*We will disprove the flipping of ASCII bit’s here in a moment using a 64 bit access.  But what I want you to get from this is the concept.  More in a moment.  Also here’s a really nice graphic from theWikipedia Entry on Endianess, well worth the read.


BIG ENDIAN
Address
Value
1000
58=X
1001
53=S
1002
57=W
1003
42=B


LITTLE ENDIAN
Address
Value
1000
42=B
1001
57=W
1002
53=S
1003
58=X


This behavior is left over from when 8 bit processors had 16 bit memory registers and it was more efficient to load the lower byte first.  If it was only an 8 bit operation then the top byte could be skipped.  Thanks to Roger for all the technical explanations, more on that to come.

Since ASCI characters show up internally a little bit nicer than this, each letter is a two byte hex pair. No need for swapping to decode. When we get large numbers, we can really see this at work within SQL Server.  For example let’s use the following statement.

use master
go
if exists(select name from sys.databases where name='demoInternals')
begin
     drop database demoInternals
end
go
Create database demoInternals
go
use demoInternals
go
if exists(select name from sys.tables where name='brad1')
begin
     drop table brad1
end
go
create table brad1(mychar char(4) primary key clustered, myint int)
go
insert into brad1(mychar, myint)
values('XSWB', 12345678)
go

We’ll create the value we just looked at XSWB and an integer value of 12345678.  Now let’s do a DBCC IND, get our page number and look at the page dump.

dbcc ind('demointernals', 'brad1', 1)
go
dbcc page('demointernals', 1, 278, 3)


The ASCII doesn’t look byte swapped, but the integers obviously are. This lead to another question that I asked Roger.  Ridiculously smart man that he is, he told me that ASCII characters do not need to load the registers in the arithmetic processors.  For that reason we do not have to swap bytes.

Thanks Roger for all the great info.  This was a lot of fun to learn.  Thank You Dear Reader for stopping by.

Thanks,


Brad

Friday, August 9, 2013

The Man Who Thinks He Can

Hello Dear Reader!  I got a lot of great questions during the 24 Hours of PASS last week and I'm working on several blogs to line those up.  The silence this week, was more due to getting busy with work and some personal things cropping up.

Life is full of ups and downs.  In my down moments I will often look for inspiration.  One of my favorite places to draw from is the poem "The Man Who Thinks He Can" by Napoleon Hill.

My Grandma Schroeder was a wonderful lady.  I loved her very much.  She used to clip out coupons from the newspaper and send them to me at college.  She would send me checks.  And then she would give me hell, because I was an 'ornery little shit'.  You see I didn't use them.  Or cash the checks (unless I was really super COLLEGE BROKE).

I'm kind of sentimental like that.  I still have movie stub's from when I was a teenager, and most of the movie stubs from any of the shows that the kids and I have sat threw.

I loved it that she cared for me so much that she would clip a coupon and send it off to me in the mail.  I could see her sitting at the kitchen table, paper spread wide, Sudoku and crossword filled out, and smell the coffee that she had brewed.  I could see the light streaming in the sliding glass door behind her in the morning.  I could see her spot a deal and think "Bradley could use this", and clip a coupon.

She would get out an envelope and a stamp from the same drawer that held the playing cards, the dice, and the Wrigley's DoubleMint gum in the green case.  I could smell the mint as she opened it.  She would, look up my address from her address book.  Fill out the envelope, and later in the day she would walk it down to the mail box.

As a child I played there, and it would always surprise me how small everything seemed when I returned.  Looking down at the table instead of up at her.

When I close my eyes now I'm still looking up, and I see her in the bright sunlight.  I see the front yard that Grandpa kept so neat.  I remember looking down at the back yard and Grandpa's rose bushes from a big red porch on the other side of the sliding glass door.

I would hold those moments whenever the road seemed long, whenever I felt very far from home, or anything that resembled victory seemed equally out of reach. In those moments. I would close my eyes and be back in Illinois, looking at Grandma at the table.

Those coupons were worth far more to me than if I had ever used them.  I think I still have a couple.  The prized possession arrived one day in the mail.  It had been a very hard year.  She had clipped out of the Peoria Journal Star the poem "The Man Who Thinks He Can".

I re-read it today and thought that it would be worth sharing.



If you think you are beaten, you are;

If you think you dare not, you don't.
If you'd like to win, but think you can't
It's almost a cinch you won't.

If you think you'll lose, you've lost.

For out in the world we find
Success begins with a fellow's will:
It's all in his state of mind.

If you think you're outclassed, you are:

You've got to think high to rise,
You've got to be sure of yourself before
You'll ever win that prize.

Life's battles don't always go

To the stronger or the faster man,
But sooner or later the man who wins
Is the one who thinks he can.


I hope you enjoyed it.  I know I do.  Thanks Grandma.


Thanks,

Brad

Wednesday, July 31, 2013

24 Hours of PASS Deck and Demo's Live!

Hello Dear Reader!  Another very quick blog.  Thank you to all of the people that tuned in to see me present on SQL Data Internals for the 24 Hours of PASS tonight.  I truly appreciate you spending your hard earned time with me.

My Deck and demo's are now live on the resources page.  I've added a list of all the presentation's that I used as references.  Any Scripts not in the deck you can find at the following links.

Click Here for the Slide Deck and Click Here for the Demos.  Now the links to all the other material.

Paul Randal MCM Video Series Data Structures http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

Bradley Ball SQL Internals Reading Data Records Part 1: Tag Bytes http://bidn.com/blogs/SQLBalls/ssas/2776/sql-internals-reading-data-records-part-1-tag-bytes

Bradley Ball SQL Internals Reading Data Records Part 2: Null Bitmap Offset http://bidn.com/blogs/SQLBalls/ssas/2781/sql-internals-reading-data-records-part-2-null-bitmap-offset

Bradley Ball SQL Internals Reading Data Records Part 3: Fixed Length Columns http://bidn.com/blogs/SQLBalls/ssas/2785/sql-internals-reading-data-records-part-3-fixed-length-columns

Bradley Ball SQL Internals Reading Data Records Part 4: Null Bitmap http://bidn.com/blogs/SQLBalls/ssas/2789/sql-internals-reading-data-records-part-4-null-bitmap

Bradley Ball SQL Internals Reading Data Records Part 5: Variable Offset Array http://bidn.com/blogs/SQLBalls/ssas/2791/sql-internals-reading-data-records-part-5-variable-offset-array

Bradley Ball Differences in the Null Bitmap between SQL 2005 and SQL 2012 http://www.sqlballs.com/2012/07/differences-in-null-bitmap-between-sql.html


Bradley Ball SQL Internals Reading Data Records Part 6: Variable Length Data http://www.sqlballs.com/2012/07/sql-internals-reading-data-records-part.html



As always Thanks for stopping by!

Thanks,

Brad