Monday, October 31, 2011

Tales from the Query Processor
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.


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.
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.



 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.



  1. It appears the apocalypse is near. Good luck to my dear SPIDS and Agents that may be left behind.

    Very nice Halloween Read!!!


  2. Awesome! Pure awesome, sir. Glad you decided to share it with everyone. I like the first story as well, that was the new one.