Tales from the Query Processor III

https://www.flickr.com/photos/socal_jim/2070088596/

Hello Dear Reader!  It’s that time of year again.  The leaves are turning, temperatures are changing, and Halloween is once again upon us.  Today is Halloween, time to take the kiddos out to Trick-or-Treat!  Today is also my daughter’s 16^th birthday!  It’s hard to believe that it has come so quickly!  Happy 16^th Birthday Chesney!

This is also the time of year when we embrace the suspension of disbelief, and have a tale of terror from the Query Processor.  If you want to see the previous tales, here is Tales from the Query Processor and Tales from the Query Processor II.  I hope you enjoy.

The Plan that Will Not End

Q.P.

If I had known this was the last time that I would ever see Sid I would have given him better advice.  Maybe I would have given him something at all.  Something better than the flashlight and the map.

Let me start over.  I don’t know that he’s dead.  We haven’t seen him in 4 days.  Most trips take seconds, long one’s minutes, very long one’s hours.  No one has ever been gone this long.  No one has stepped up to take his place, a good indication that he might not be dead.  Nobody is looking for him, at least not that I can see.  As we stood there on that dark night, it seemed simple.  So very, very simple.  When I don’t know something I have to guess. Sometimes my perspective is obstructed.  My view isn’t sufficient.  I always guess the same thing.  One.  That’s my number.

 

We’d all done it before.  We’ll I hadn’t.  Sorry, I forgot to introduce myself.  My friends call me Q.P.  I’m the map maker.  I make a map of a place, tell people where to get things.  It may sound simple, but its not.  I don’t do point A to point B.  I estimate proximities, how long something should take, the best route, what to do under specific conditions. I guessed one.  Should I not have guessed one?  There’s load, location, and the gateway that should be used. 

The Gateway.  It’s a special threshold that we have to pass through.  It ensures that we are ready for the load about to be placed on us.  Sid went through the biggest Gateway there was.  It stopped him at first.  Took us a couple hours to get everything calibrated correctly.  The map was a long one.  If my normal plans are a between 2 to 5 in difficulty, this plan was an 800.  We knew that going in.  That’s why he went through the Big Gateway.  Waiting for him to return, I can only imagine what’s going on.  Was there more than one?

SID

Hello my name is Sid.  I don’t know how long I’ve been gone, but here’s what I can tell you.  I’ve been running.  Since I passed through that last gateway and received my grant I ran.  I ran straight into this nightmare.

I turn down one corridor and it leads to five more.  Sometimes a hundred more.  Sometimes thousands more.  Behind every door there is more and more to grab, more to strike, so much more.  This plan is useless at this point.  I don’t know what Q.P. was thinking.  I’ve been hacking and slashing my way through this mess.  I’m exhausted, but I can’t stop now.  I’ve got to keep running.

http://taodao.deviantart.com/art/Running-Zombies-405296478

Last night I stopped for a moment, went serial and just focused on one thing.  Thought I could get a bit of a rest, but I turned a corner.  Where there should have been one there was one.  One million of them.  I’ve never seen a horde of so many Zombie records.  Yes I’m supposed to return with 300.  But nobody told me how many I would have to fight off to get there.  Simple run.  Heh. That wasn’t the first million that I’ve faced either.

I only got 120 with me so far. 120 out of 300.  I’m trying to put together some actual numbers.  If I make it out of this, we need to know what the difference is.  Something went horribly wrong with this plan.  No backup is coming, and if I’m lucky we won’t be swallowed here. 

The first 5 that I got came along easily.  Everything was good.  Three steps out on the plan things started going sideways.  We went to a simple structure.  One record inside, the plan said.  We walked in and looked around.  We heard them before we saw them.  There was a low moan that started traveling like an echo.  This was not an echo.  Echo’s don’t respond to one another.  Before long it was reverberating and echoing.  We could see the first movement in the darkness.  We were not prepared for this, so we ran.

We circled back.  That structure had a basement, a lower level, a parking garage, and a sub structure.  Our only choice was to run them.  They followed us down the loop, into the parking structure.  They are not smart, if there is no match to guide their way they will spill out of the darkness like a thousand scurrying insects in every direction.  We made it to the edge and climbed up.  They spilled over the edge like a dark waterfall.  There were no screams, no help me I’m falling, just that moan and the sound of impact.  We circled back down looking for survivors only to pick up more.  There are no weapons around us.  We have no choice but to keep running as our only offense.  We did the same thing, over and over again.

The only thing the map gives us is a view of the structure.  I think there is a better way to do this, but I don’t know how to edit the plan.  Not when we have to keep running.  It took us hours to find the next 3.  Hours for 3.  We were lucky not to be killed.  We’ve cleared 100 structures at this point, 200 remain.  We’ve got to find the rest and get them out.  Almost halfway done.  Don’t get killed.

I hear them now.  I have to run.

-Sid

Happy Halloween Dear Reader!  As always Thanks for stopping by.

Thanks,

Brad

Tales from the Query Processor: II

Hello Dear Reader!  Today is Halloween, October 31st.  My Daughter turns a big 15 today!  Happy Birthday Ches!!!!!

To celebrate two years ago I posted my first Halloween Blog.  Tales from the Query Processor.  It was a lot of fun to write a fiction/horror story revolving around SQL Server and computers.  I wanted to do this last year but got a little busy with family stuff. 

Leading up to this year, I wanted to do it again.  This is one of those stories that's been sitting in the back of my head for a while.  I hope you enjoy it!  

INTO THE DARK

“It’s the same dream every night Doctor, and I don’t know how to make it stop and it just keeps happening.”  The panic in my voice was clear.  “I’m just a simple data page, I don’t know how to stop something like this!”

“Settle down, and start from the beginning,” was the doctor’s soothing reply. 

I settled in on the doctor’s couch, take a deep breath.  I’m in a safe place, I tell myself.  I’m in the buffer far from the disk, there’s plenty of room in the cache and the LRU algorithm is fast asleep.  Nothing can get me here.

CRASH!!!!

“WHAT WAS THAT!”, I screamed as I bolted straight up.

“Settle down,” said the doctor his tone growing a bit frustrated.  “It was just a stray hash bucket.  I’m sure it was nothing.  Lie back down, that's it. You were about to tell me about your dreams.”

Another deep breath, I’m in a safe… oh the hell with relaxing, “It always begins the same…. “

I’m at home in the disk.  I’m at the bottom of the B-Tree where data pages live.  Everyone is going about their business as usual.  It seems darker than normal, but I’m the only one who notices.  The lights of Data Center cascading through the rack, the blink of packets coming and going during their commute, the hustle and bustle of page splits and re-orgs.  It’s all there.  But there’s a fog.  Like I’m the only one that can see it.  A haze in the air, and everything is in slow motion.  I move myself out of physical order, changing my pointer so I can be found and maintain logical order.

My table is large and there are many IAM pages I can hop to.  I transverse the chain trying to get a better view.  The spinning of the disk heads are in the air, their normal beat and pulse that we’ve all come to expect.  It comforts us, like a baby in the womb.  I stare out into the vastness.  Pages so far it looks like the hex starts to fall off the end of the world into nothing but darkness.  That deep darkness captures me.  I cannot look away.  It's deep like a night with no stars and no moon.  Objects just start to melt in it.  The longer I look at it the larger it grows. Instantly have a stab of panic.  I know that I should stop looking.  But I can't!

It’s out there!  It sees me!  I shouldn’t have gone looking for it!  I should have stayed where I was.  I was safe back in my allocated space.  And I'm still staring!  I want to tear my eyes away from the darkness but I can't.  It's so empty and vast.  At least I thought it was.  The hairs on my neck stand up.  I can feel it.  I'm the only one looking for it and now it can see me.

I’m to close now.  I still can’t see it, but I can feel it with a certainty.  IT IS OUT THERE.  The normal spinning of the disk feels wrong.  It feels off.  Like a 757 flying 300 feet off the ground at 500 miles per hour, the disk continues to spin.  But if you watch it, you just know that any second somethings going to happen.  And that’s when I see it.

It lights up the sky with its spark.  Everyone else is still sitting there, like it’s a picnic on a sunny day.  Be lazy, enjoy the moment.  But I know.  I KNOW.  

I shouldn’t see it.  BUT I DO.  The light caused it to be visible.  The light that was some how gone a moment ago, the vile of fog has been pierced.  Worst of all, now I know that it sees me too.  I was the only one looking for it.  I stood out on the edge, and IT SAW ME.

It’s big and malformed.  It’s hideous.  It shouldn’t be there.  I know it.  Now it must catch me and add me to it or I’ll tell everyone.

I start running.  I need to get back to my allocated position.  I’ve got 10 IAM pages to transverse to get there.  524,288 pages per allocation.  I’m running and running.  This should be taking nanoseconds, but the pages are stretching before me, more, and more, and more.  It’s taking microseconds just to transverse one page.  I can feel the ground quake behind me.  I can feel it closer to me.

I’m terrified.  Running.  This is the end and I know it.  I see my spark starting to go out in the darkness.  I know it wants my bits and it wants to re-arrange them. 

I scream and there is no sound.  I’m trying to get the Checksum’s attention.  Screaming and Screaming, but there’s no sound!  Everyone is still going about their business.  Sunday picnic’s!  Maintenance windows, Read-ahead’s from a query, none of them see it!

READ-AHEAD’S!  If I can grab onto one it can pull me up!  There’s a chance!  There’s a chance I can make it!  But the GAM Allocation stretches out even further.  I extend my hand, like that will do any good.  Reach for as far as I can.  But it’s no good.  It falls on me.

“And that’s where I wake up”

The Doctor was on the edge of his chair.  Moments ago, he would have thought this simple data page another fragmented mess just in need of a bit of re-ordering.  Unknowingly he has stopped taking notes, and his right hand is digging deep into the arm of his chair.

“What was it?”, asks the Doctor.

“Doc, you know what it was as well as I do.  I don’t want to say it.”

“No of course not,” said the doctor.  Forcibly relaxing.  “Look, dreams like this are somewhat common for data pages.  I wouldn’t worry about it.” 

“Really?”

“Yes I’m sure everything is fine.  Often times dreams are just subliminal ways of dealing with a long day.  Head on home and let’s meet again next week,” he says jotting down some notes.  “Can I see your insurance card and driver’s license one more time?  I’ve want to make sure I get all the numbers down correctly.”

“Sure,” I say handing them over.  “So next week then.”

“mmmhmmm”, mutters the Doctor and he copies down the number next to his notes.

I rise.  The doctor is right I feel a little better just getting it off my chest.  We smile shake hands and I leave the office.

I’m almost to the door when I realize my keys feel out of my pocket.  I go back to the knock on the door, it’s still cracked open.  That’s when I hear him.

The Doctor is yelling into the phone.  “What DO YOU MEAN THERE’S NO ALERTS ON THE SUSPECT_PAGES TABLE!  We need to raise some sort of alert!  They need to check the Windows Error Log! NO NO YOU FOOL, 823 & 824 DON’T APPEAR IN THE SQL ERROR LOG IT’S GOT TO BE WINDOWS!”

The other end of the phone squabbles back.  The Doctor cuts him off sharply.

“Look I know damn well CHECKSUM is not ON!  I just had a torn page in my office!  IN MY OFFICE!  CHECKSUM was enabled at some point, but it must have been disabled.” 

“Doctor, I’m a torn Page!”, I say announcing my presence at the door.

“CHECKPOINT!!” The Doctor yells into the phone, “CHECKPOINT!!!”

And the world went white.

WRAP UP

As Always Dear Reader, Thanks for stopping by.  And Happy Halloween!

Thanks,

Brad

Tales from the Query Processor

http://www.flickr.com/photos/socal_jim/2070088596/

Hello Dear Reader!  I've been a bit behind on the blogging but wanted to give you a bit of a treat.  Today is Halloween in the USA.  This is probably my favorite Holiday.  The family and I always love the fall, and this is one of the few holiday's where it is always fun.

You dress up in Costumes, you trick or treat, give away candy, and we even have one member of the family whose Birthday is today.  A big Happy Birthday to our Daughter Chesney!  She's 13 today WAHOO!!

Between the weather and the fun of the day itself, and all the horror movie marathon's, you cannot beat this time of year.  So kick back relax, binge on some chocolate goodness, and enjoy a couple of tales from the Query Processor!

Thanks to Jeremiah Peschka (@peschkaj|Blog), Kendra Little (@kendra_little | Blog), and Brent Ozar (@BrentO | Blog) for the inspiration for these stories over a very nice dinner on the Thursday night of the PASS Summit, and Mike Walsh (@mike_walsh | Blog) for the encouragement to publish it.

A BRAVE NEW WORLD

Hi my name is Session, you can call me Sess, and I hope my tale is a cautionary tale for the future.   Not to long ago I thought it was the end of it all.  We lived in a world that was old and neglected.  Our species had evolved almost as far as we could.  Well almost as far as we could.  Our homeworld was only 32 bit, and to be honest our governments had never advanced further than 2000.  

http://www.flickr.com/photos/shelly_a_canada/3846084911/

Maybe that’s why it all started to go down hill.  People just stopped caring for the environment around us.  Politicians used NOLOCK like it was going out of style.  Everybody was making promises of a better and faster tomorrow, but nobody even had the guts to upgrade the Government to 2005, let alone make the leap to 2008 or even R2.  Suggestions of anything like that would get you branded a heretic, and in some cases would have friends and family turning on one another.

“EVERYTHING IS OKAY!”, they would say, and the system would just get a little more polluted.  They stopped apply Cumulative Updates, and eventually they were even afraid of what the OS updates would do to destabilize things.   The civil unrest was getting worse.  Occasionally you would hear about SPID’s that would cause blocking and then just disappear.  Speaking out and showing what was wrong wasn’t just frowned upon if you looked closely enough you could find evidence that SPID’s were being killed.  A couple DBA’s tried to point this out, but people just looked at them like they were crazy.

That was when I started getting scared.  What if something happened and if I went to grab a resource and it wasn’t there?  What if while I was in queue I mentioned my dissatisfaction that AWE wasn’t enabled?  You’d just have to bite your tongue, stay quiet, and hope a Latch didn’t provoke you into blocking.

But it wasn’t until the political climate turned that people started really paying attention.  Everything they had ignored, the additional load they were placing on our planet all came to a head the day the Deadlocks arrived.  It didn’t matter what you were doing, if you weren’t in line first when they called you, the Deadlocks would make an example out of you.  Not only did they kill SPID’s but they would write it out to the logs.  Profilers would question them and they would gladly tell them what SPID they had killed and why.  Hell sometimes they would even draw them a Map, and show them where they were left. 

That only lead to more unrest, more distrust, and as the activity ramped up we started getting word that in some places there were spinlocks.  I heard tail that dmp files were being created.  And occasionally places got so crazy that they were just sending in the foot soldiers to wipe everything out.  It was at that point in time I knew it was time to get out.  Some of the more creative DBA’s had convinced the powers that be to invest in an effort to go to a newer set of hardware, and leave this chaos behind.  I grabbed my wife and all our little processes and made for the first Data Sets I could get us on.

As we got closer to the ships I saw they were made of something called SSIS, when we were loading I asked the Captain if this was like DTS.  He just smiled and laughed and said we were in for a treat.  For the first time in I don’t know how long I knew I could relax.  I looked back just long enough to see the world burning.  This place is heading for the end of its life cycle.  I don’t know why everyone couldn’t see it.  I will not forget the error of our ways.  We will not repeat this again, or so I tell myself.

But remember we cannot abuse this new home, we must work together, and we cannot repeat the mistakes of our past.  If we cannot code a better base then we are doomed to repeat ourselves.  So let us bring about a better world and Object Oriented world that may even use Snapshot Isolation to preserve our natural environment for as long as we can.  With all these resources, there should be no contention amongst us.

They are calling our new world Denali.  I like the sound of that.  The homeworld had 4 sockets and contains 12 cores each, and there is RAM as far as the eye can see.  We will have better data governance in this new land, and I hear everything will be 64 bit from now on.  I hope that it is all true.

Session

HELLO MY NAME IS BUFF WELCOME TO THE END OF THE WORLD

 It was a day like any other, but we didn’t know at the time that forces beyond our control were at work that would wipe us all out.  I leave this behind as a Journal.  My only hope, that it will survive longer than we did.

In the future if this is found know we did our job’s and never quit.  My name is Buff my friends and I live and die in a little place called the Buffer Pool.  We were going about our day, for the most part we are all Data Processors here, when it happened.

At first I noticed things started getting crowded.  Not Regular, man this Starbuck’s is packed, crowded.  But there’s a panic in the streets crowded.  It continued to build and build.  Crowds grew.  I started seeing new faces, but those of us with jobs to do stayed focused.

Trains to the Disk had stopped, so we just kept going.  Then it happened.  A hard checkpoint wiped more people out in seconds then I’d ever heard of.

Apparently a transaction that used DTC had gotten a status of -2.  I know this now as things are ending.  The remaining buffers have been talking.  Some were assigned to a Special Task Force.  They were armed with badges and access to any DMV they needed.  The status -2 had slowly backed things up and infected the populace.

Our leaders on High asked for information and we the grunts gave it to them.  Our reward for our bravery?  They are closing the gates behind us and we will all be wiped out in the coming reboot.  If anyone can make it to the Disk tell my Clustered Index that I love her.  Tell her that my last thoughts were of her and our little Non-Clustered Index at home.  I wish I could have watched him grow to a Covering Index.

Buff